Hey all
I'm looking for some clarity on the SQL Server log file. I have a largish database (2GB) which lately wasn't backed up for whatever reason. The log file for the database grew to around 11GB which from my understanding is all the transactions and statements that occurred in the database.
My questions:
What causes the database log file to be flushed? What does "flush" actually mean? What are the consequences of doing a file shrink or database shrink on a large log file?
A backup usually clears the transaction log. The transaction log keeps all changes since the last backup. Depending on how you backup the database you may not need to keep a full transaction log at all. If you are using MS SQL2000/MS SQL2005 setting the recovery mode to Simple does away with the transaction log.
Once you are sure you have a clean copy of the database (with no risk of loss) it is safe to remove the transaction log. There are some SQL commands to do it but I usually change the recovery mode to Simple then shrink the database then set the recovery mode back to Full if necessary.
If you need more info then include the version of SQL you are using and how you perform backups and I'll see if I can elaborate bit more on your specific set-up.
Actually, I don't believe backing up the database shrinks the logs, backing up the transaction logs themselves shrinks it. SQL Server makes a distinction between the two.
Typically, I will either make all databases "Simple" recovery, or make backing up the transactions logs a part of my regular maintenance plan (normally once a week).
Once you backup the transaction log, those transactions are truncated from the log but the space used by the operation is not automatically recovered. If you are doing regular transaction log backups, this can be a good thing. The assumption being that the space being used for transactions and will be needed again in the future. Continuously shrinking the transaction log can be bad for performance since when needed again the database will need to expand the log.
So to fix your issue, first do a full backup and a transaction log backup. You don't need to go to Simple mode or single-user. Then shrink the database and set up a transaction log backup every few hours. On my servers I do it every 10 minutes, but it entirely depends how often one needs for their environment. Monitor how the log size changes, be sure to leave enough room such that it does not have to expand regularly, plus a little extra for fun.
When you do shrink the database use DBCC SHRINKFILE rather than DBCC SHRINKDATABASE since the latter will shrink the entire database, not just the log file. Also you don't have as much control over how much space is being recovered.