SQL Server Performance Forum – Threads Archive

Transaction Log File Growing Quickly

Dear Friends, I am using SQL Server 2005 standard edition. My database size is 500MB but the Transaction Log file size is incresing day by day now it is 40GB.I changed the recovery mode from Full to Simple.Can u pls.tell me how to reduce the size of LDF safely. Thanks,
Regards,
Taher

Hi there If you are in SIMPLE mode, you can do: BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY This will reduce the filesize, but beware there will be a performance overhead in increasing the file sizes again every night. Alternatively, do ordinary, regular, backup of your DB. regs, Mads "Follow the join tree" – Dan Tow

Hi,
BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY The above statement will not reduce the Log file size. This will only truncate the inactive part of the TL. It will not release the space for the OS use. The TL file will still have the same size. To reduce the file size you have to shrink the file after truncate command
BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (logical log file name)
ref : http://support.microsoft.com/default.aspx?scid=kb;en-us;873235
Madhu

Changing the recovery model doesn’t restrict the transaction log growth, either you need to perform frequent transaction log backups or restrict the AUTOGROW on Tr.log file on that database. You can shrink the Transaction log using DBCC SHRINKFILE and make sure to schedule the transaction log backup in order to keepup the sizes. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Changing the recovery model doesn’t restrict the transaction log growth, either you need to perform frequent transaction log backups or restrict the AUTOGROW on Tr.log file on that database. You can shrink the Transaction log using DBCC SHRINKFILE and make sure to schedule the transaction log backup in order to keepup the sizes. <hr noshade size="1">Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing.</center> <hr height="1" noshade id="quote"></blockquote id="quote"> Good point. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Thanks, DilliGrg

You can change the recovery model to SIMPLE when there is no requirement for point in time recovery.
If you are changing the recovery model to simple, run the alter db command to change and then run DBCC SHRINKFILE command to shrink it and leave the file gowth to unlimited. If the recovery model is FULL/BULK LOGGED, you need to perform the tlog backup with every 30/60/90.. min schedule. If you want to continue with Full/Bulk recovery models, you have to run the log backup with TRUNCATE_ONLY option then shrink the file using dbcc shrinkfile command then Run the full backup immeditely and schedule tlog backup every 30/60 minutes… Read recovery model info in bol..
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
Mohammed U.

Hi,
i would suggest to refer below KBs and threads ,for cause of Transaction Log becoming full and how to reduce Locks and how to stop it : Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/?id=110139
http://www.support.microsoft.com/?id=317375 How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/?kbid=873235 and threads:
http://groups.google.com/group/micr…40ed8742075475d?sa=X&oi=groupsr&start=1&num=3 http://groups.google.com/group/comp…ac9ed81dd813cd9?sa=X&oi=groupsr&start=0&num=3 http://www.dbazine.com/sql/sql-articles/mullins-sqlserver
HTH
Regards Hemantgiri S. Goswami
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
————————
http://hemantgirisgoswami.blogspot.com

With regard to Transaction log growth, there is nothing much difference in between 2000 and 2005 and you must take care the log size with whatever suggested. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

quote:Originally posted by satya Changing the recovery model doesn’t restrict the transaction log growth, either you need to perform frequent transaction log backups or restrict the AUTOGROW on Tr.log file on that database. You can shrink the Transaction log using DBCC SHRINKFILE and make sure to schedule the transaction log backup in order to keepup the sizes. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Thats confused me! I thought the Checkpoint process reclaims transaction log space every so often and you can’t back up the transaction log when a database is in Simple recovery mode anyway.

Yes you cannot be assured that transaction log will not grow if you keep your database to SIMPLE recovery model, in fact if there is a larger transacton and in order to complete that process it has to outgrow the size to accomodate the data irrespective of CHECKPOINT process. You can test it yourselves, if you restrict the AUTOGROW then you would see ERROR 9002 in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Thanks to
DilliGrg, Satya, Hemantgiri, Madhu & all participated in discussion,for useful information. I scheduled the trn log backup every 60 mins. & now .LDF file size in under control. Regards,
Taher

You can even set it to 30 minutes even that takes care of virtual log boundary very weel. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

]]>