Why is My Log File so Big??

This is probably one of the most common posts that I see on the MSDN forums and hear from students.  The most common answer is that the database has been set to full or bulk logged recovery model and a transaction log backup has not been completed.  So let’s take a VERY simplistic look at log growth and what to do to control it.  To best demonstrate this let’s first create a database and look at the log and data file sizes as well as the log_reuse_wait_desc:
<style=”font-size: 12px;”>
USE MASTER;
GO

IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘LogGrowth’)
BEGIN
DROP DATABASE
LogGrowth
END

CREATE DATABASE LogGrowth;
GO

ALTER DATABASE LogGrowth SET RECOVERY SIMPLE;
GO

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO

SELECT name,
size
FROM LogGrowth.sys.database_files;
–Log size = 98 Data Size 392
So the database is set to simple recovery model which means that the inactive portion of the log file is truncated upon checkpoint.  This can be validated by simulating transactions:
<style=”font-size: 12px;”>–Transactions
USE LogGrowth;
GO

IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = ‘Transact’)
BEGIN
CREATE TABLE
Transact(
col1   CHAR(50)
);
END
GO

DECLARE @i INT = 50000
WHILE @i > 0
BEGIN
INSERT
Transact
SELECT ‘This is row number ‘ + CONVERT(CHAR(4), @i);

SET @i  = 1
END

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO

SELECT name,
size
FROM LogGrowth.sys.database_files;
–Log size 98 Data size 776

Kewl!!  So the data file has grown, as is to be expected, but the log file has remained the same.  So what happens if the database recovery model is changed to full?  Well just changing the recovery model will have no immediate effect on the transaction log growth until a database backup is made, refer to my post on pseudo-simple recovery.  Once a backup is taken, however, the game is on!!
<style=”font-size: 12px;”>ALTER DATABASE LogGrowth SET RECOVERY FULL;
GO

BACKUP DATABASE LogGrowth
TO DISK = ‘C:\Backup\LogGrowthFull.bak’;
GO

DECLARE @i INT = 50000
WHILE @i > 0
BEGIN
INSERT
Transact
SELECT ‘This is row number ‘ + CONVERT(CHAR(4), @i);

SET @i  = 1
END

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO
–NOW WE need a LOG_BACKUP!!!

SELECT name,
size
FROM LogGrowth.sys.database_files;
GO
–Log Size 25352 Data Size 1160

The log_reuse_wait_desc now shows as LOG_BACKUP and the log file has obviously grown, in fact it is substantially larger than the data file.  OK so now how can we get the log file to a more manageable size???  Good question!!  Lets go through some answers that I see posted.

 

1. Backup The Log

With the log_reuse_wait_desc showing LOG_BACKUP the obvious first action should obviously be to backup the transaction log.  Transaction log backups can prevent the log files from growing too large, but do not reclaim used space
<style=”font-size: 12px;”>
BACKUP LOG LogGrowth
TO DISK = ‘C:\Backup\LogGrowth.trn’;
GO

–So what is our log size now?
SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO
–Back to nothing

SELECT name,
size
FROM LogGrowth.sys.database_files;
GO
–Log Size 25352 Data Size 1160 NO CHANGE IN LOG?!?!?
So the first question that comes out is “Why is the log file the same size?  I backed it up?!?  Why didn’t it truncate?!?”  The answer is that logging works in a circular manner and is allocated with VLF‘s, virtual log files.  After the log backup was taken the log is not truncated, but the VLF’s that can be are marked as free and can then be used to record new transactions.  The log file growing means that new VLF’s are added upon truncation they are not deleted, but rather marked as free and then reused.

The above transaction log backup has marked the VLF’s as free, but it does not delete these, executing DBCC LOGINFO will show the VLF status.  So what was the point in backing up the log?  If you don’t the log file will grow until all disk space is consumed!!!

 

2. Change the Recovery Model to SIMPLE

Makes sense right??
<style=”font-size: 12px;”>ALTER DATABASE LogGrowth SET RECOVERY SIMPLE;
GO

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO

SELECT name,
size
FROM LogGrowth.sys.database_files;
GO
–Log Size 25352 Data Size 1160
–It’s simple but still LARGE!!

Again the file size is the same.  Changing the recovery model to SIMPLE will insure that the log file sill truncate inactive VLF’s upon checkpoint but it does not reclaim disk space.   I KNOW!!!  Truncate in this sense is a bit deceiving.

THIS AFFECTS YOUR BACKUP/RESTORE CHAIN!!!

PLEASE remember that changing the recovery model to SIMPLE is an acceptable means of managing log space during events such as bulk imports and initial database population, but it will affect your backup/recovery process.  Simplistic example is a database that is set to full recovery model and a full backup is done at midnight and transaction log backups done every hour.  At noon the database recovery model is changed to Simple in order to manage logging and truncation on checkpoint for a bulk load of data.  Once complete at 12:30 the recovery model is set back to full.  All is well in the world until 1:00 when the scheduled transaction log backup runs.  The backup fails because SQL tells you there is no full backup that has been done.  True enough that a full backup ran at midnight, but by setting the recovery model to simple at noon basically started the backup/recovery over.  Since transaction log truncation occurs upon checkpoint in simple recovery the backup chain ended at noon when the recovery model changed to simple.  The first thing that should have been done at 12:30 after setting the recovery model to full was a full backup.

 

3. Detach the Database, Delete the Log, Reattach the Database Creating a new Log File

STOP and think about this!!  Can it be done?  Yep.  Should it be done? Nope.  There is the distinct possibility that you will not be able to use the database again.  Has this been used successfully?  Yes, but is the risk of total loss worth rolling the dice.  This is a LAST resort and only in extreme and a full backup should be done before attempting.

 

4. DBCC SHRINKFILE

DBCC SHRINKFILE can be used to shrink a data file, not necessarily recommended, and log file.  There are obviously considerations to this operation some of which are outlined here.

More information can be found on MSDN here.

The sample T-SQL code displayed above can be downloaded here.

 

Leave a Reply


Hit Counter provided by Sign Holders