0

Got something strange (to me ) going on with my SQL Server 2005 database. The log file has very gradually grown huge.

Running

dbcc loginfo('dbname')

shows everything in the log is active (Status 2)

Running

dbcc opentran

shows 'no active open transactions'

so.... Not really sure what all the stuf in the transaction log is. The only glimmer of hope I've found is this post at sql server central but turning replication on and off seems a bit of a peculiar fix (sort of a variant of turn it off and on again I guess).

Any ideas what's up or what else to try/investigate?

thanks, Robin

2 Answers 2

1

The transaction log doesn't manage itself. You either need to schedule a regularly ocurring log backup and truncate or you need to set the database Recovery model to Simple.

My suggestion would be to perform a log backup and truncate to get the log file down to a more reasonable size and then set the database Recovery model to Simple to avoid this problem in the future.

3
  • Just double checked my 'hourly tlog backups' and discovered they were full backups, hence the tlog had never been backed up. Now just need to get a second disk in that machine with space for the tlog backup I'm going to have to make.
    – Robin
    Feb 3, 2011 at 13:52
  • 1
    To prevent this on future database creations, you can set the recovery model on the MODEL database to simple. Then when new database are create, no one has to remember to change the recovery model from FULL.
    – jl.
    Feb 3, 2011 at 14:45
  • @jl: Good point
    – joeqwerty
    Feb 3, 2011 at 15:39
0

you can check sys.databases for finding out what is holding the log backup from getting shrink and go from there use select name, log_reuse_wait_desc from sys.databases

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .