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.

I'm using sql server 2005 and the log is too big 23 gbs (if you compare with the data file only 8GB). I've already run the truncate, after that the log file is 24mb but after 2 days, it becomes 23gbs. My database is full backup mode. I have the maintain plan is 2 hours will have diff backup and 24hours will have full backup. Do you have any idea about this case?

share|improve this question

migrated from stackoverflow.com Mar 31 '10 at 9:57

This question came from our site for professional and enthusiast programmers.

2 Answers 2

You need to make sure that you are running transaction log backups. As your database is in Full Recovery Mode, these will mark the portion of the log that has been played into the data file as inactive and it can be overwritten. Differential and Full backups do not actually backup the transaction log.

TL;DR Run transaction log backups regularly. Diff and Full backups do not do it.

share|improve this answer
    
To add to Dan's answer - you need to be doing both the T-log backups and your diff/full backups, at the proper interval for your recovery plans. –  mfinni Mar 31 '10 at 12:59

Thanks Dan, i found my problem is the weekly maintenance plan have the SHRINK task, and it was not success like expected. After i stopped run it, the transaction log seems be ok.

share|improve this answer

Your Answer

 
discard

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