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.

What is the best way to shrink a log file without breaking the log chain? I realize you can do something like:

alter database yourDb
set recovery simple
go

dbcc shrinkfile(LogFileName, 0)
go

alter database yourDb
set recovery full
go

But I really don't want to break the log chain. Is there any way to accomplish this?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

According to the comment on this answer "Shrinking databases makes God kill kittens" :-). So I'd advise you not do it.

Anyway, I don't have another solution to your problem except to backup the log, then shrink it. Switching to simple recovery model will break the chain. There isn't a best way to shrink the log file. Except not use FULL RECOVERY if it's not necessary, so shrinking of log won't be needed.

Edit: Have you seen what's using your transaction log? Maybe there's a long running open transaction. Or the need for a backup..a checkpoint.. Check this article for some ideas why your log file might be so big.

share|improve this answer
    
Shrinking a data file and a log file are completely different things. –  ivanmp Feb 18 '12 at 0:42
    
@ivanmp: I was just joking about that comment. Anyway, shrinking (for whatever reason or file) is an action that can cause some hidden problems so it's OK to raise the awareness level. –  Marian Feb 20 '12 at 8:12
add comment

I vaguely recall you have to do something like this, since the SQL Server log file is written cyclically.

  1. Make a log backup. This marks the active log space for reuse.
  2. Shrink the log file. This will shrink it down to the end of the last virtual log file in use (which will probably leave it larger than what you wanted to shrink it to).
  3. Since the end of the log file will now be in use, given that's where it stopped shrinking due to a VLF being in use, SQL Server will wrap back to the beginning of the log file for further log writes.
  4. CHECKPOINT to make sure dirty pages for active transactions are flushed, then make a backup again. (The CHECKPOINT might not be necessary - you can try without first.)
  5. Shrink the log file again. You should be able to bring it down to where you want.

If that still doesn't work, check DBCC OPENTRAN, and the log_reuse_wait column in master.sys.databases.

share|improve this answer
add comment

Your Answer

 
discard

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