Avoid a Full Transaction Log at All Costs
As great as full transaction logs are, they'll work for you only if you maintain them properly. A full transaction log file grinds production to a halt. SQL Server will simply refuse to write changes, leaving your users unable to work. You'll probably suffer this error only once, because once is enough.
When it does happen, you must truncate the log by hand using BACKUP LOG as follows:
BACKUP LOG databasename WITH TRUNCATE_ONLY
Executing this statement forces SQL Server to dump the inactive area of the log, which gets your database running and lets your users get back to work. However, truncating the log has a downside (you just knew there would be): you lose the history necessary to restore the database if it crashes. To protect the data, run a BACKUP DATABASE statement on the entire database immediately following the BACKUP LOG statement. Your users can continue to work, and although the unexpected backup might be a bit of a pain, it's necessary.
The key is to avoid a quickly filling transaction log altogether. Fortunately, you can easily do that by heeding the following administrative guidelines:
- Avoid large transactions. A large transaction can be adding or editing several million records with one INSERT or UPDATE.
- Avoid long-running BEGIN TRANSACTION blocks. Everything between BEGIN TRANSACTION and COMMIT remains active.
- Avoid using the KILL statement or canceling a transaction from Management Studio. Either action will render a statement active--forever. (An occasional KILL is sometimes necessary and harmless; just don't make a habit of it.)
Shrink the Log
Truncating the log frees up space inside the log for new transactions, but it doesn't reclaim disk space. As a matter of habit, check the log's size. If it's truly large, shrink it after truncating it as follows:
DBCC SHRINKFILE (databasename_log, targetsize)
Targetsize represents, as an integer, the size that you want the file to be in megabytes. If you omit this value, SQL Server reduces it to the default file size. In addition, if the log is already larger than targetsize, SQL Server shrinks the file to the size needed to store the current records.
Although this command will free up some space, it'll also play havoc with file fragmentation at the disk level, so use it infrequently. Perform this action manually only when necessary. Or, if you're specific about conditions, you can execute this statement via an alert script.