Transaction Log growth out of control

Question

Transaction Log growth out of control

Welsh Corgi

SSC Guru

Points: 116520
More actions
July 18, 2013 at 7:19 am

#275425

Last weekend I re-indexed some Databases.
Before doing so I backed up the Databases and Set them to simple recovery mode.
After the rebuild of the Indexes was complete I Set the Database back to Full recovery and performed another backup.
Ever since the Transaction log growth and size on one of the Databases is huge.
The Database is 259 GB but the Log File has been quite large.
I'm backing up the log and shrinking it.
This morning the log file was 108 GB.
I tried backing up and shrinking several times but the size remained the same.
I checked for Open Transactions and there were none.
So I put the Database in Simple Recovery and then I shrank the log file.
I want the Database to be in Full Recovery mode.
I never experienced anything like this, and ideas?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

If you run in FULL recovery modus, you can query the [sys].[databases] table. In the column "log_reuse_wait_desc" is stated what holds the log file from being re-used (and thus is growing).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

You may have the active portion of the log at the end of the file, which prevents it from shrinking. One of the things you can do is run some transactions, then try to shrink again. If it doesn't work, run some more. Once the active VLF rolls to the beginning of the file, you can easily shrink it.

calvo SSChampion Points: 12930 More actions · Answer 3

You can use dbcc loginfo to find active VLFs and their status.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

How often are you backing up the log?

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Thanks for all of the replies.

I back up the transaction log hourly.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Steve Jones - SSC Editor (7/18/2013)
You may have the active portion of the log at the end of the file, which prevents it from shrinking. One of the things you can do is run some transactions, then try to shrink again. If it doesn't work, run some more. Once the active VLF rolls to the beginning of the file, you can easily shrink it.

Thanks for the tip.

I'm baffled as to why this just started happening?

The transaction log gets so big that I switched to Simple Recovery Model and increased the frequency of the Differential Backups. If the transaction log is that large then I can't refresh the Development Environment with the Backup because of a lack of Disk Space.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

I think it's just bad luck and timing. The active portion of the log was near the end of the file when the log filled and you tried to shrink it.

I posted a script on here years ago to automate the shrink: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30026/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

I'm still having trouble shrinking the Transaction Log.

The Transaction Log was at 22 GB.

I backed up the Transaction Log and shrank the Log and it reduced the size to 9 GB.

I ran the command DBCC OPENTRAN and there are no open transactions.

I run the following command:

SELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);

It returns the following results:

Current LSNOperation

000f4163:00000040:0001LOP_BEGIN_CKPT

000f4163:00000040:0002LOP_COUNT_DELTA

000f4163:00000040:0003LOP_COUNT_DELTA

000f4163:00000040:0004LOP_COUNT_DELTA

000f4163:00000040:0005LOP_COUNT_DELTA

000f4163:00000040:0006LOP_COUNT_DELTA

000f4163:00000040:0007LOP_COUNT_DELTA

000f4163:00000040:0008LOP_COUNT_DELTA

000f4163:00000040:0009LOP_COUNT_DELTA

000f4163:00000040:000aLOP_COUNT_DELTA

000f4163:00000040:000bLOP_COUNT_DELTA

000f4163:00000040:000cLOP_COUNT_DELTA

000f4163:00000040:000dLOP_COUNT_DELTA

000f4163:00000040:000eLOP_COUNT_DELTA

000f4163:00000040:000fLOP_COUNT_DELTA

000f4163:00000040:0010LOP_COUNT_DELTA

000f4163:00000040:0011LOP_COUNT_DELTA

000f4163:00000040:0012LOP_COUNT_DELTA

000f4163:00000040:0013LOP_COUNT_DELTA

000f4163:00000040:0014LOP_COUNT_DELTA

000f4163:00000040:0015LOP_COUNT_DELTA

000f4163:00000040:0016LOP_COUNT_DELTA

000f4163:00000040:0017LOP_COUNT_DELTA

000f4163:00000040:0018LOP_COUNT_DELTA

000f4163:00000040:0019LOP_COUNT_DELTA

000f4163:00000040:001aLOP_COUNT_DELTA

000f4163:00000040:001bLOP_COUNT_DELTA

000f4163:00000040:001cLOP_COUNT_DELTA

000f4163:00000040:001dLOP_COUNT_DELTA

000f4163:00000040:001eLOP_COUNT_DELTA

000f4163:00000040:001fLOP_COUNT_DELTA

000f4163:00000040:0020LOP_COUNT_DELTA

000f4163:00000040:0021LOP_COUNT_DELTA

000f4163:00000040:0022LOP_COUNT_DELTA

000f4163:0000004e:0001LOP_END_CKPT

Any ideas?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/