Transaction File Growth

I recently Took over as Database Administrator for a Small Mortgage Firm in the Charlotte Area. The Databases have been poorly maintained for the last three years, with issues such as 3Gig Datafiles and 100G Transaction Files. I have backed up all the databases and reset the Transaction Logs to Restricted growth. The Problem is that on the Main Production Database the Transaction Log grows at about 50Megs a Day,which is fine, but in the smaller less used Database the Tranaction Log is Growing by 500Megs a Day. I have run the profiler against the Database and the Main gets about 5K hits a day and the Smaller Gets about 50 Hits a day. Does Anyone have any idea why there would be such a growth difference

Brad

Answer this question

Transaction File Growth

cheung_iris
A constantly growing transaction log indicates that transactions are not properly commited or rolled back. You should have a code review of all parts accessing the databases to make sure that all transactions are finished either way.
Charlie Maitland
One might be inclined to think that the T-Log is indeed poorly maintained. But this has only partially to do with maybe more or less properly committed transaction. If your databases are not in "Simple" recovery mode, the T-LOG will grow until there is no disk space anymore. Then SQL Server will stop. If you want a nice, small and handy T-Log you have to do some work by yourself. See if these links will help:
http://support.microsoft.com/ id=317375
http://support.microsoft.com/default.aspx scid=kb;EN-US;256650
http://support.microsoft.com/default.aspx scid=kb;EN-US;110139
http://support.microsoft.com/default.aspx scid=kb;EN-US;272318
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstutze PASS Deutschland e.V. (http://www.sqlpass.de)
coolbiker
That was my thought also. I will look into the queries being ran against the database and find the problems. Thanks for you help.

Brad
Transaction File Growth

Answer this question