Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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