SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log growth out of control


Transaction Log growth out of control

Author
Message
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27468 Visits: 4895
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/
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8738 Visits: 3718
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
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148434 Visits: 19444
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
calvo
calvo
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3346 Visits: 4018
You can use
dbcc loginfo

to find active VLFs and their status.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4136 Visits: 3436
How often are you backing up the log?



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27468 Visits: 4895
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
Welsh Corgi
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27468 Visits: 4895
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
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148434 Visits: 19444
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/

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27468 Visits: 4895
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 LSN Operation
000f4163:00000040:0001 LOP_BEGIN_CKPT
000f4163:00000040:0002 LOP_COUNT_DELTA
000f4163:00000040:0003 LOP_COUNT_DELTA
000f4163:00000040:0004 LOP_COUNT_DELTA
000f4163:00000040:0005 LOP_COUNT_DELTA
000f4163:00000040:0006 LOP_COUNT_DELTA
000f4163:00000040:0007 LOP_COUNT_DELTA
000f4163:00000040:0008 LOP_COUNT_DELTA
000f4163:00000040:0009 LOP_COUNT_DELTA
000f4163:00000040:000a LOP_COUNT_DELTA
000f4163:00000040:000b LOP_COUNT_DELTA
000f4163:00000040:000c LOP_COUNT_DELTA
000f4163:00000040:000d LOP_COUNT_DELTA
000f4163:00000040:000e LOP_COUNT_DELTA
000f4163:00000040:000f LOP_COUNT_DELTA
000f4163:00000040:0010 LOP_COUNT_DELTA
000f4163:00000040:0011 LOP_COUNT_DELTA
000f4163:00000040:0012 LOP_COUNT_DELTA
000f4163:00000040:0013 LOP_COUNT_DELTA
000f4163:00000040:0014 LOP_COUNT_DELTA
000f4163:00000040:0015 LOP_COUNT_DELTA
000f4163:00000040:0016 LOP_COUNT_DELTA
000f4163:00000040:0017 LOP_COUNT_DELTA
000f4163:00000040:0018 LOP_COUNT_DELTA
000f4163:00000040:0019 LOP_COUNT_DELTA
000f4163:00000040:001a LOP_COUNT_DELTA
000f4163:00000040:001b LOP_COUNT_DELTA
000f4163:00000040:001c LOP_COUNT_DELTA
000f4163:00000040:001d LOP_COUNT_DELTA
000f4163:00000040:001e LOP_COUNT_DELTA
000f4163:00000040:001f LOP_COUNT_DELTA
000f4163:00000040:0020 LOP_COUNT_DELTA
000f4163:00000040:0021 LOP_COUNT_DELTA
000f4163:00000040:0022 LOP_COUNT_DELTA
000f4163:0000004e:0001 LOP_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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search