Transaction log Size Blowout

[tinmar]

Hi,

Recently with sql 2008 I went through some pain with transaction logs files exploding in size.

There's many blogs out there on this subject but what was affecting us was something new (Change data capture) and is not widely documented.

So first the basics:

You should generally have nightly full backups of your database and hourly backups of your transaction logs. This not only means that you can always restore to a recent time but will keep the transactions logs small in size.

(It is actually the transaction log backup that reduces the log file size not the database backup.)

But what if you are doing this and the log is getting ever larger?

Run

DBCC SQLPERF(logspace)

to show you what percentage of the log is actually used. If it's very low then you can truncate the log to reduce it's size.

DBCC SHRINKFILE(<TransactionLogName>, 1)

Doing a truncate will disrupt the log and if you're system was to crash then all you would have left to restore to is your last full database backup.

If you ran logspace command above and got high usages then the next command is useful.

DBCC OPENTRAN

This will tell you your oldest active transaction.

If this is recent then it could be that your log file is the correct size for your database but in our case our db was 130 Gig and the log file was 200 Gig and growing everyday, so this wasn't the case.

Traditionally this sparks in every dbas mind replication gone bad and if you have been using replication then this could be the case.

To resolve this use the below to publish the db and then clear out all the currently active replication records
(The correct term is mark them as published or some such.)

EXEC sp_dboption 'DatabaseName', 'Publish', 'true' 
GO 
EXEC sp_repldone NULL,NULL,0,0,1

This wasn't our issue so I don't know too much more about this.

What we had done was toy with change data capture in the past and had left CDC activated on the server but disabled the 2 sql agent jobs.

This appeared to cause the transaction logs to continually store the records and increase the log size with active records that can't be truncated.

To Check to see if CDC is enabled on your Server:

USE master 
GO 
SELECT [name], database_id, is_cdc_enabled  
FROM sys.databases       
GO

If there's a 1 in the last column then CDC is activated.

To deactivate it:

USE DBName 
GO 
EXEC sys.sp_cdc_disable_db 
GO

After I did this I ran the below code again and the usage had dropped dramatically.

DBCC SQLPERF(logspace)

A transaction log backup later and the log size was back to normal and the issue resolved.

References:
There's a very good article on CDC at:

http://www.simple-talk.com/sql/lear...change-data-capture-(cdc)-in-sql-server-2008/