Q
Problem solve Get help with specific problems with your technologies, process and projects.

Transaction log file exploded in size over the weekend

Following the weekend, I found the transaction log had exploded from 35 MB to 1.4 GB. I executed DBSHRINKDATABASE and DBSHRINKFILE as well, but the log only reduced in size to 986 MB. I discovered next that a junior administrator had performed a database detach, which I don't believe caused this problem. Also, one of the developers scheduled a DTS package executed by a stored procedure. The package executes every 15 minutes, executing table updates and exporting table data to text files for importing to another (remote) database. Replication cannot be used. I experimented by detaching the database and copying the .MDF & .LDF to a cdrom and reattached the same on another server (test/development) by only using the .MDF file. SQL Server 2000 generated a new 504 KB transaction log file. The database has all the updates performed on it and no errors have been generated. My question: Will I experience problems by using the server-generated (new) log file. Since the orignal log file would not reduce further, I had to assume the file was corrupted.

The only issue you will run into is that you now have broken the chain of your backups. That means that if for some reason the full backup that you have is no longer any good, you can not go to a full backup further back in time and roll the entire database forward. The process that you ran is something that should not be done with a production database. Relying on SQL Server to create a new log file by purposely blowing away the existing one is something that should only be attempted under VERY extreme circumstances since you are basically very lucky that the database even came back online.

The transaction log was NOT corrupt. Just because it will not shrink all the way does not mean you have a corrupt transaction log. If you had a corrupt transaction log, you would certainly know about it by your database going suspect. A shrink can only remove the unused portion of the transaction log. If there is data in there, it can't remove it. It will also only shrink to the last open transaction. So, if someone has left a transaction open, you can shrink the database over and over again and it will not remove any space beyond that. The correct process for doing this is to backup the transaction log and then shrink it. The backup removes any committed transactions from the log to your backup allowing the shrink to remove the maximum amount of space possible. It is still limited if there are open transactions. Detaching and reattaching a production database is something that is not recommended as a general practice.

 

For More Information

This was last published in January 2004

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close