July 18, 2013 at 7:19 am
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/
July 18, 2013 at 7:26 am
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).
July 18, 2013 at 9:25 am
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.
July 18, 2013 at 9:32 am
July 22, 2013 at 5:01 am
July 22, 2013 at 8:08 am
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/
July 22, 2013 at 10:32 am
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/
July 23, 2013 at 8:42 am
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/
July 26, 2013 at 8:48 am
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply