Sql-server – How does shrinking a SQL Server log file affect performance

sql serversql-server-2005sql-server-2008

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is no longer available with 2008 and later databases.

I do have a script that truncates the log file:

USE [MyDatabase]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC shrinkfile('MyDatabase_log', 1)
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT
GO

This truncates the log file completely, but my question is: Does this affect performance?

I perform two Full backups daily so the log should not really be necessary as far as data roll-forward is concerned.

Best Answer

I really recommend reading Importance of proper transaction log size management by Paul S. Randal.

The quintessence is that there are only two really good ways to do transaction log handling:

  1. Either go with regular LOG file backups and the LOG-file will reuse it's space after each LOG backup and won't grow indefinitely, or

  2. Use the SIMPLE recovery model and you don't have to care about your LOG-file size, as you do regular full backups.

What concerns LOG-file truncation and performance is that you will always get a performance hit when the LOG file is to be increased (a quote from the above-linked blog post):

If you shrink the log, then it's going to grow again - possibly causing VLF fragmentation, and definitely causing your workload to pause while the log grows, as the log can't use instant initialization [...]

Update: Don't mistake LOG file truncation for DATA file shrinking. DATA file shrinking is really bad. See Why you should not shrink your data files for details.

Related Question