Transaction log file growth and database mirroring

If you have a database mirroring setup it is important to do regular transaction log file backups, if you do not maintain the transaction logs they will grow indefinitely and eventually fill up your drive and I have seen this happen.

When this what you need to do is backup the transaction log then shrink the log file using DBCC SHRINKFILE. Here is an example script which will backup the transaction log on a Blog database and figure out how much free space is available, then shrink the file down to the space used plus 10%.

BACKUP LOG Blog TO DISK = 'E:\MSSQLSERVER\Backups\blog_log.bak'
 
CREATE TABLE #TempPerf(
      [Database Name] VARCHAR(255),
      [Log Size (MB)] DECIMAL(12, 2),
      [Log Space Used (%)] DECIMAL(12, 2),
      [Status] INT,
      FreeSpace as ([Log Size (MB)] - ([Log Size (MB)] * ([Log Space Used (%)] / 100.0)))
)
 
INSERT INTO #TempPerf EXEC('DBCC SQLPERF(logspace)')
 
DECLARE @shrinkToMB INT;
 
SET @shrinkToMB = (
      SELECT TOP 1 (([Log Size (MB)] - FLOOR(FreeSpace)) * 1.1) as TargetSize -- FreeSpace + 10%
      FROM #TempPerf
      WHERE [Database Name] = 'Blog'
)
 
DBCC SHRINKFILE(Blog_log, @shrinkToMB)
 
DROP TABLE #TempPerf

This approach will not work in all cases, after running this query on a clients system which had this problem the transaction did not shrink, and we get a message as follows:

(1 row(s) affected)
Cannot shrink log file 2 (_log) because the logical log file located at the end of the file is in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

After some googling this article came up:

http://blogs.technet.com/b/mdegre/archive/2011/09/04/unable-to-shrink-the-transaction-log.aspx

– Ran DBCC OPENTRAN which returned ‘No active open transactions’
– Value of ‘log_reuse_wait_desc’ is LOG_BACKUP

The ‘log_reuse_wait_desc’ column on the ‘sys.databases’ table indicates why the transaction log was not cleared or truncated, see this article http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/

Basically the current VLF is preventing the transaction log file from being truncated, one solution that is suggested here: http://dba.stackexchange.com/questions/64771/how-to-do-a-one-time-log-shrink-on-a-database-with-transactional-replication is switching the database to simple recovery (which will cause transactions to stop being replicated, probably need to re-initialize replication) then shrink the log and switch the database back to full recovery mode.

In actual fact you can’t switch the database to simple recovery model while mirroring is configured, SQL Server will not let you, you need to remove mirror then you can change the recovery model and shrink the log, then you will need to reconfigure mirroring.

References:

http://blogs.technet.com/b/mdegre/archive/2011/09/04/unable-to-shrink-the-transaction-log.aspx
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d1c533cd-aa7e-4774-9b85-b73ddf3b7873/sql-server-2008-r2-mirror-database-shrink-transcation-log

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s