How does shrinking a SQL Server log file affect performance?

Question

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.

Answer 1

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.

Answer 2

OK first yes the log is necessary even with the daily full backups if you want to recovoer in the event of a problem. We backup our transactionlog every 15 minutes. The problem is that you are not backing up your transaction log and that is why the log grows so outrageously. You should almost never need to shrink a transaction log if you are doing correct transactionlog backups.

You will need to back up the database before you truncate the log. I suggest doing it on off hours so no new data is inserted between the backup and the truncation. Then set up proper transaction log backups so that you never have this problem again.

As to affecting performance, well without knowing the details of your system hardware and usage, that would be hard to say.

Answer 3

How quick does the transaction log grow? If it is rather rapid, you will affect performance by shrinking it down to near nothing, as it has to spend time growing it back. This does not mean you should not shrink it from time to time, but you have to think about the size issue rather than just shrink down to minimum. Is the perf hit huge? Probably not, but it depends on load on the server (number of transactions, etc).

One thing I find problematic is "I perform 2 Full backups daily so the log should not really be necessary as far as data roll-forward is concerned." The log is extremely important for points between your full backups. Even twice a day does not eliminate the need for a log file for disaster recovery, unless this is a read only database (if it was, you would not see the huge increase in log file, however).