June 1, 2010 at 5:57 am
Hi,
My database is on Full recovery, with frequent log backups, and weekly full backups (it's a bit large for daily full backup) - but these multiple daily log backups do not truncate the transaction log! Once again I ended up with a full transaction log and for the life of me can't figure it out - since all articles I read say that on frequent log backups the tran log should not experience these problems.
I have monitored the database over several days and after a log backup, never see the transaction log size decrease, it just gets bigger and bigger.:pinch:
It;s probably a misunderstanding on my part, if someone could help me out I'd be most grateful!
Thanks!
Jinx.
June 1, 2010 at 7:43 am
If your transaction log continues to grow after a log backup, it sounds like your transactions are in an uncommitted state. You should see committed transactions cleared from the log after a log backup. It won't change the size of the log, but it will change the amount of space available in the log.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2010 at 8:31 am
Query sys.databases. What's the value for log_reuse_wait_desc for that DB?
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
June 1, 2010 at 8:59 am
There are no open transactions and the output from log_reuse_wait_desc is NOTHING.
It's baffling!
June 1, 2010 at 9:13 am
So after you run a log backup, there is no extra free space within the log, at all?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2010 at 9:22 am
What is the exact command that you're running to back the log up?
Monitor the log_reuse for a while, see what values it takes, especially if you can see it just before the log grows.
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
June 1, 2010 at 9:28 am
OH! Idiot me. The statement is in a stored procedure, and I made the mistake of assuming it was ok, but now see that there is a NO_TRUNCATE within the statement - which I presume is what is causing my problem.
This is the procedure:
CREATE PROCEDURE [dbo].[sp_log_backup]
@DBName varchar(100),
@BackupLocation varchar (4000)
AS
BACKUP LOG @DBName
TO DISK = @BackupLocation WITH NOFORMAT, NO_TRUNCATE, INIT,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Sorry to waste your valuable time with my idiocy! :blush:
June 1, 2010 at 9:33 am
That's exactly what I was expecting to see. 😉 The no_truncate said backup the log and don't truncate it. That's a option that's used for tail-log backups with a damaged database.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply