May 31, 2013 at 9:08 am
I recently upgraded to SQL Server 2012 from SQL Server 2005. The database is in full recovery mode and I take full backups nightly and transaction log backups hourly. However, the transaction log is not being shrunk after backup and continues to grow daily. Switching the database to simple recovery and running DBCC Shrinkfile will reduce the file size. However, the issue continues after the database is back in full recovery and log backups are taken. Has anyone experienced this issue?
May 31, 2013 at 9:11 am
Log backups don't shrink the log file, hence you shouldn't expect to see the log file get smaller. Log backups just mark portions of the log reusable.
If you see that the log is not being reused after a log backup and the log file keeps on growing, then please take a read through this: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2013 at 9:18 am
If the transaction log is still growing even if you are taking regularly scheduled transaction log backups (which truncates, marks as reusable the VLF's) then you may need to increase the frequency of your t-log backups. If the transaction log is growing, it needs the space and shrinking it is doing you no good. Also, switching to simple recovery model and back to full recovery model breaks the log chain.
For more on managing transaction logs, read the last article I have referenced in my signature block.
May 31, 2013 at 9:41 am
Thanks for the reply. The log file is not being reused after backup and there are no active transactions (DBCC OPENTRAN) causing the size. I did not have this issue prior to upgrading from 2005. My log now is 43GB on a 120GB database. I run DBCC shrinkfile just to get it back to a reasonable size and I make a full backup after. The logs are backed up hourly so I do not think the frequency is the issue. Any ideas?
May 31, 2013 at 10:06 am
GilaMonster (5/31/2013)
If you see that the log is not being reused after a log backup and the log file keeps on growing, then please take a read through this: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2013 at 11:29 am
If it is still growing after the t-log backups (full backups have no affect on the t-log) try running the following:
select
name,
log_reuse_wait_desc
from
sys.databases
where
name = 'your database name here'
May 31, 2013 at 12:28 pm
My log_reuse_wait_desc is 'LOG_BACKUP' (recovery_model_desc = FULL). Is this correct? I appreciate all the assistance.
May 31, 2013 at 12:45 pm
Run the following:
DBCC LOGINFO ('YourDatabaseName');
Post the results here.
May 31, 2013 at 12:51 pm
I attached the results. There are 935 rows.
May 31, 2013 at 12:56 pm
Do you see all those VLF's with a status of 2? They won't away until the status changes to 0.
You may also want to start reading this: http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
Then follow up with the other blog posts referenced.
At this point, I am getting out of my depth. I hope I have at least pointed you in the right direction.
May 31, 2013 at 12:58 pm
Thanks for the help.
May 31, 2013 at 1:03 pm
Oh, actually, they don't go away. They get flagged as inactive (status 0). Your log file won't shrink(when using SHRINK_FILE) until you start getting the VLFs at the front of the file to an inactive status.
May 31, 2013 at 1:33 pm
Could you post the exact log backup command that you're running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2013 at 1:37 pm
My script is below.
backup log mydatabase
to disk = '\\someserver\backups\logbackup_timestamp.bak'
with no_truncate, init
May 31, 2013 at 1:45 pm
And there's your problem right there...
Kingfish (5/31/2013)
backup log mydatabaseto disk = '\\someserver\backups\logbackup_timestamp.bak'
with no_truncate, init
You've explicitly told SQL to not truncate the transaction log on backup, not to mark portions of the log as reusable, hence SQL doesn't truncate the log, doesn't mark it as reusable and hence the log file continues to grow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply