snippetsqlModerate
How to reduce the log file size without shrinking it in SQL server
Viewed 0 times
withoutfilethelogsqlsizereducehowservershrinking
Problem
In SQL server, how do you reduce the log files size without (DBCC) shrinking it. I know shrinking the log file will free up some space, but will also cause fragmentation. Doing a checkpoint in simple mode or backing up the transaction log in full mode should do the trick, but it is not working in one of my scenarios. Any advice/links will help in preventing log file fragmentation.
Solution
Once the log claims disk space the only way to get it back is to shrink. Checkpoints and truncation will free up space within the log file (now available for future use by the log) but the actual footprint of the file on disk stays the same.
Read these articles for good information not only about how this works but the pro's and con's of messing with it. Most DBA's would agree that shrinking is not a good idea in most normal cases (as you eluded to in your question).
http://msdn.microsoft.com/en-us/library/ms365418.aspx
pros and cons
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/a9e92f1b-7a27-4999-b241-eadadbb0a057
Read these articles for good information not only about how this works but the pro's and con's of messing with it. Most DBA's would agree that shrinking is not a good idea in most normal cases (as you eluded to in your question).
http://msdn.microsoft.com/en-us/library/ms365418.aspx
pros and cons
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/a9e92f1b-7a27-4999-b241-eadadbb0a057
Context
StackExchange Database Administrators Q#2716, answer score: 10
Revisions (0)
No revisions yet.