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.
| Author |
Topic |
|
agfreesafety
Starting Member
7 Posts |
 Posted - 2009-02-26 : 18:14:31
|
| I know how to shrink the log files. Is it possible to have them shrink automatically, perhaps on a periodical basis? If so, how do I get that accomplished? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-02-26 : 19:37:02
|
| The general wisdom is to NOT shrink log or data files. You should first find out why they keep growing, and see if you can take steps to prevent that. If you can't, it's better to leave them at their current size, since they will grow to this size again anyway.What recovery model are you using on your database(s)? If it's Full, and you don't use transaction log backups, you could limit or prevent growth by backing up the log periodically. If you don't need to restore to a point in time, you could even switch to Simple recovery, which will truncate the log after every transaction. |
 |
|
|
heavymind
Posting Yak Master
115 Posts |
Posted - 2009-02-27 : 01:49:36
|
| You can shrink entire database as part of maintenance plan. Or you can always schedule a job which performs use yourdbgodbcc shrinkfile ('yourdb_log', 0)or smthng like thatThanks, VadymMCITP DBA 2005/2008Chief DBA at http://www.db-staff.com |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-27 : 04:27:52
|
| Don't shrink the database regularly.It causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.See - [url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]Similar thing with the log. If the log is getting to a certain size, it needs to be that size based on activity, log backup frequency, etc. If you shrink it, it will just grow again and everything has to stop while the log grows. Lots of small grows of the log lead to lots of virtual log files which slow down backups, restart recovery and a few other things.Work out how big the log needs to be based on activity and log backup frequency (if in full recovery), make it that big (with a little leeway) and then leave it alone.--Gail ShawSQL Server MVP |
|
|
|
agfreesafety
Starting Member
7 Posts |
Posted - 2009-02-27 : 11:32:48
|
| Gila,Will the shrinking of log files cause massive fragmentation as well, or is that only with the data files? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-27 : 14:41:09
|
| Shrinking the log won't cause index fragmentation. That's what you get shrinking the data file.Shrinking the log and then letting it grow in small increments will get you fragmentation within the log, of a different form - lots and lots of VLFs (virtual log files), which can result in slower backups, restores, database startup and other operations that affect the log.See - http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
How to Automatically Shrink Log Files?