shrinking log files

Question

shrinking log files

DBA-640728

SSChampion

Points: 12898
More actions
August 17, 2010 at 8:27 am

#157154

hi, i do have a transaction log backup but still my transaction log from time to time gets huge, after searching for various articles i found this script which is the one i use:
USE GLReporting
GO
DBCC SHRINKFILE(GLReporting_log, 1)
BACKUP LOG GLReporting WITH TRUNCATE_ONLY
DBCC SHRINKFILE(GLReporting_log, 1)
GO
I also read that this script could brake the log chain, so which one is the best way to shrink the log in case of emergency (no space left in the drive, log full, etc) ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

If you are properly maintaining your log by doing Transaction Log backups at regular intervals, the log file should not be growing out of control like that unless you have a large logged operation occuring inside of a single transaction. The first thing to do would be to identify why the log is growing like it is. Is the database in FULL recovery? If it is, how frequently do you do transaction log backups? Generally it would be every hour, two hours, or even as frequently as every 15 minutes, depending on your specific SLA/OLA's. If you do log backups, do you use Database Mirroring, Replication, Change Data Capture, etc in SQL? When the log grows out of control, is it in conjunction with a scheduled Agent Job that is doing data loading, deleteing, updating, or other heavy processing inside of a transaction? What is the log_reuse_wait column showing for the database when the log isn't truncating?

Using the above script might alleviate your problem momentarily, but it is not the solution to the problem. Some good further reading on this subject can be found on Aaron Bertrand's blog post:

Oh, the horror! Please stop telling people they should shrink their log files!

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

DBA-640728

SSChampion

Points: 12898

August 17, 2010 at 10:45 am

#1208037

great article, thank you!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please read through this - Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pankushmehta Ten Centuries Points: 1217 More actions · Answer 4

BACKUP LOG GLReporting WITH TRUNCATE_ONLY

This will delete all your transaction log entries. if you are maintaining Transaction log backups, the chain will no longer be useful, in case of a restore.

if you are performing index rebuilds, or some long running transactions, they can increase your log file.

you can backup the transaction log and if space contrained issue the shrinkfile command.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

common culprits for large tlog files:

1) FULL recovery mode but not doing ANY tlog backups

2) leaving long running tranactions open even in SIMPLE mode. then committed transactions cannot be flushed.

3) index maintenance activities

4) lots of snapshot-type activity

5) big trigger activity

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service