Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction Log growth out of control Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 7:19 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
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/

Post #1475029
Posted Thursday, July 18, 2013 7:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 2,302, Visits: 2,777
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’! **
Post #1475033
Posted Thursday, July 18, 2013 9:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,018, Visits: 15,456
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
Post #1475094
Posted Thursday, July 18, 2013 9:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:20 AM
Points: 1,259, Visits: 3,567
You can use
dbcc loginfo

to find active VLFs and their status.


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1475096
Posted Monday, July 22, 2013 5:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:57 AM
Points: 906, Visits: 2,868
How often are you backing up the log?



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1475939
Posted Monday, July 22, 2013 8:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
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/

Post #1476029
Posted Monday, July 22, 2013 10:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
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/

Post #1476130
Posted Tuesday, July 23, 2013 8:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,018, Visits: 15,456
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
Post #1476619
Posted Friday, July 26, 2013 8:48 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
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/

Post #1478053
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse