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


dbcc loginfo('dbname')

shows everything in the log is active (Status 2)


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


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.

  • 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 '11 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 '11 at 14:45
  • @jl: Good point – joeqwerty Feb 3 '11 at 15:39

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.