Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
do you want to reduce the log file size or truncate it? Elaborate the scenario in which log full mode backup is not working? –  Coder Hawk May 13 '11 at 18:31
add comment

3 Answers 3

up vote 7 down vote accepted

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

share|improve this answer
    
+1 I was getting ready to post the same :) –  Coder Hawk May 13 '11 at 19:11
1  
I think a few of us are really hoping this site becomes the home for dba's as opposed to stackoverflow. Obviously plenty of folks on this site willing to answer... we just need more questions!! –  RThomas May 13 '11 at 19:23
1  
indeed... its picking up steadily; way better than initial stages of public beta. see area 51 stats –  Coder Hawk May 13 '11 at 19:32
add comment

The only way you can change the physical size of the log files is using DBCC SHRINK. CHECKPOINT will do only log truncation but that never changes the physical log file size.\

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

share|improve this answer
add comment

I'm not sure the OP is referring to the physical footprint. He indicates a CHECKPOINT nor a log backup freed up space. This make me think he's referring to how much of the log is "active". My suggestion would be checking for open transactions which would prevent the active part of the log from clearing - DBCC OPENTRAN.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.