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 Microsoft SQL Server 2005, is it OK to shrink the log file of an online database, or will it cause an interruption of service?

share|improve this question
add comment

4 Answers 4

up vote 6 down vote accepted

Shrinkfile will not take your database offline.

share|improve this answer
add comment

DBCC SHRINKFILE itself does not make the database go offline. However, simply executing this command will not necessarily reduce the size on disk of the log, which depends on when the last full (for SIMPLE recovery types) or t-log (for FULL recovery type) backup was taken. If there's not been a recent backup of the logs then there'll be no log space to reclaim.

Finally, unless there has been some unforeseen, explosive growth due to an abnormally large write operation/batch, you may want to reconsider shrinking the file because the database is showing a need for a log file that large and the server needs to dedicate resources to expanding it back out. Perhaps adding storage would be a reasonable alternative.

share|improve this answer
3  
IMO this is the best answer here. –  Eric Higgins Aug 20 '12 at 16:39
add comment

Yes, it's fine. It doesn't affect any existing transactions, nor does it move any data around like database shrinking. Don't shrink it right back though, because growing a log file takes effort.

share|improve this answer
add comment

This question has been answered many times on dba.se before. The answer to your question, like Paul Randal often says, depends on your situation.

If your database is in Full recovery mode and you care about point in time recovery, you shouldn't truncate and shrink the log file. Do you have log backups in place? Is there a runaway transaction which is growing the log file? You might want to read this link

share|improve this answer
1  
Actually, my question has not been answered. I'm asking specifically if issuing dbcc shrinkfile causes the database to go offline or not. I am aware that regular shrinking is not a best practice. I am using the a shrink to fix an inflated log file after a long series of failed backups. –  longneck Aug 20 '12 at 13:54
2  
Shrinkfile will not take your database offline. –  yogirk Aug 20 '12 at 13:55
    
make your comment the answer and the rep is yours... –  longneck Aug 20 '12 at 14:21
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.