Is there a way to autoshrink SQL Logs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Is there a way to autoshrink SQL Logs

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-09-15 : 14:11:09

I thought SQL Logs are supposed to autoshrink upon the completion of a full backup. Is this not true?

I notice our log files are huge. Is there a SQL Server setting that will autoshrink the logs for each database?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-15 : 14:18:04

That is not true. No autoshrink happens after any backup.

Your log files are huge because you are using full recovery model and not backing up the transaction log on a regular basis.

Autoshrink should never be enabled.

To fix your problem, either change your recovery model to SIMPLE or start backing up your transaction logs on a regular basis. We backup our tlogs every 15 minutes.

Once you have done either of the above things, then do a one-time shrink using DBCC SHRINKFILE to get it down to a reasonable size. You should not have to do this ever again and shouldn't do it ever again as it impacts performance.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog