Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We can handle a performance hit.

This is also a one off as there was a new index created which caused a massive log file to be created. I need to shrink this file back down.

I just wanted to know if there were any risks running this command.

Sql Server 2005 database

share|improve this question
1  
Ya' know who needs to answer this? smile If I'm not mistaken, the guy who wrote DBCC SHRINKFILE is on here. serverfault.com/users/1992/paul-randal –  Evan Anderson Jun 16 '09 at 2:36
    
Yup, thought the very same thing. Figured he'd see this soon. –  squillman Jun 16 '09 at 3:02
add comment

5 Answers

up vote 1 down vote accepted

It's safe, but do it at a quiet time when there's low transactional activity. It's recommended to shrink the log to its minimal size and then grow it to its normal size (this will ensure the correct number of VLFs (internal virtual log files) are created, which improves performance for logged commands).

If the database's log doesn't shrink - if the database is in simple mode execute a checkpoint command first, if it's fully logged backup the log first.

If the log contains unprocessed mirrored or replication transactions then it may not shrink to a minimal size.

Remember to set an appropriate auto growth value.

See the following articles:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

share|improve this answer
add comment

Assuming you are talking about SQL Server 2000/2005, I do it on live databases with no issues.

share|improve this answer
    
Good. Not quite as authoritative as what I was hoping for though :) –  Spence Jun 16 '09 at 2:51
add comment

I also shrink the trans log on active db's. It does only affect the inactive chunk of the transaction log.

share|improve this answer
add comment

Paul Randall has a pretty in depth thread here. There is also an article on his blog:

http://serverfault.com/questions/20909/is-it-safe-to-have-sql-server-auto-shrink-turned-on/20910#20910

Its not your exact question but does shed additional light on what/how DBCC SHRINKFILE works.

share|improve this answer
add comment

Also some pointers if your log doesn't shrink:

  1. You can backup your transaction log.
  2. You can try to run checkpoint (http://msdn.microsoft.com/en-us/library/ms188748.aspx) first so all dirty pages are written to disk and afterwards run the dbcc shrinkfile.
  3. If you don't have a complex sql configuration (eg: database mirroring on that particular databse) you can switch the database to simple recovery and then switch it back to full recovery mode (this can be done while the database is being used) and will surelly shrink your database log file.
share|improve this answer
1  
Doing this will break the log chain, and will require that a full backup be taken. Changing the database from full to simple doesn't shrink the file, it simply marks the log entries and being over writable. You still have to manually shrink the file. (Insert all the normal shrink warnings here.) –  mrdenny Jun 28 '09 at 22:57
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.