Is there a risk to running a DBCC SHRINKFILE command against a log while db is being used

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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.