May 19, 2011 at 7:35 am
I'm having some difficulties with transaction logs filling.
Every hour, we have an SSIS package that takes transaction log backups, based on the following code: -
select 'BACKUP LOG ['+name+'] TO ['+name+'] WITH NOFORMAT, NOINIT,
NAME = N'''+name+'-Transaction Log Backup'+CONVERT(VARCHAR(12), GETDATE(), 102)+' '+CONVERT(VARCHAR(8), GETDATE(), 108)+''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
from sys.databases where name not in ( 'Adtec','Tempdb') and recovery_model_desc = 'FULL'
At the end of the day, the SSIS package does a backup of the databases, based on the following code: -
select 'BACKUP DATABASE ['+name+'] TO ['+name+'] WITH '+CASE WHEN Datepart(dw,getdate())<>2 then 'DIFFERENTIAL,' else '' end+' NOFORMAT, NOINIT,
NAME = N'''+name+'-'+CASE WHEN Datepart(dw,getdate())<>2 then 'Differential' else 'Full' end+' Database Backup'+CONVERT(varchar(12),GETDATE(),102)+''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
from master..sysdatabases where name not in ( 'Adtec','Tempdb',CASE WHEN Datepart(dw,getdate())<>2 then 'Master' else ''end )
Even with this in place, our transaction logs fill extremely quickly then auto-grow to the point of running out of disk space on the separate hdd that stores the transaction logs.
When I check for log_reuse_wait, I rarely get anything other than either "Nothing" or "Log_Backuo" as a response for each database.
I'm at a complete loss here. Any help would be greatly appreciated 🙂
May 19, 2011 at 7:37 am
Run the log backups every 10 minutes (maybe even less).
May 19, 2011 at 7:46 am
Ninja's_RGR'us (5/19/2011)
Run the log backups every 10 minutes (maybe even less).
I'm not sure that would solve the issue I'm having (I'm not saying it won't, just that I'm confused as to why it may be necessary 🙂 ).
The reason is that after 3pm, several of our databases are barely used. To qualify, I mean that barely any data is loaded/updated/deleted, but they are still selected from.
So, with my little knowledge of transaction logs, I'd have thought that after 3pm we'd have our transaction logs back at a high amount of free space. This isn't the case, which is why I'm confused.
May 19, 2011 at 7:49 am
When the backup is taken, the space is marked available for reuse. so the log doesn't need to grow.
You can schedule it more than once... maybe every 10 minutes from 9 to 5 then every hour from 5 to 9.
But the point is that it doesn't matter. The log backups will only containt 100K of so of log headers if they are empty so that won't fill up the drives either.
One more thing is that if you have reindexing jobs it can fill up the logs REALLLLLY fast. So I'd make sure that this is not the issue here.
May 19, 2011 at 7:53 am
Ninja's_RGR'us (5/19/2011)
When the backup is taken, the space is marked available for reuse. so the log doesn't need to grow.You can schedule it more than once... maybe every 10 minutes from 9 to 5 then every hour from 5 to 9.
But the point is that it doesn't matter. The log backups will only containt 100K of so of log headers if they are empty so that won't fill up the drives either.
One more thing is that if you have reindexing jobs it can fill up the logs REALLLLLY fast. So I'd make sure that this is not the issue here.
The reindexing thing was the previous problem (actually, we were reindexing and recompiling statistics), that was spotted about a month ago so we changed it 🙂
I'll look into changing the schedules for during busier periods and let you know. Thanks.
May 19, 2011 at 10:09 am
We've solved our issue, it was a D'OH moment 😉
Basically, the code I posted above is an older version of what had been deployed to the server (must've been changed when I wasn't looking :hehe:). The newer code was malfunctioning, meaning it wasn't taking a transaction log back-up which meant we were having to do it manually.
We've fixed the offending code and it all seems to be running smoothly now.
May 19, 2011 at 10:11 am
Time to track the guilty party! 😉
May 19, 2011 at 10:15 am
Ninja's_RGR'us (5/19/2011)
Time to track the guilty party! 😉
Not too difficult - there are two of us that work on the database servers and it wasn't me. . . 😀
May 19, 2011 at 10:18 am
skcadavre (5/19/2011)
Ninja's_RGR'us (5/19/2011)
Time to track the guilty party! 😉Not too difficult - there are two of us that work on the database servers and it wasn't me. . . 😀
Time to wack the guilty party (just behind the head is enough... no need for permanant dammage :-D)
May 19, 2011 at 11:54 am
Ninja's_RGR'us (5/19/2011)
skcadavre (5/19/2011)
Ninja's_RGR'us (5/19/2011)
Time to track the guilty party! 😉Not too difficult - there are two of us that work on the database servers and it wasn't me. . . 😀
Time to wack the guilty party (just behind the head is enough... no need for permanant dammage :-D)
With a SQL Server Administration book, just for added effect.
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 19, 2011 at 11:57 am
GilaMonster (5/19/2011)
Ninja's_RGR'us (5/19/2011)
skcadavre (5/19/2011)
Ninja's_RGR'us (5/19/2011)
Time to track the guilty party! 😉Not too difficult - there are two of us that work on the database servers and it wasn't me. . . 😀
Time to wack the guilty party (just behind the head is enough... no need for permanant dammage :-D)
With a SQL Server Administration book, just for added effect.
Yup, right over the full encyclopedia of your choice 😀
May 20, 2011 at 12:53 am
Check this article's.
Truncating and shrinking the transaction log file
I Hope this will help others.
Edited: to correct the grammar.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 20, 2011 at 6:23 am
muthukkumaran (5/20/2011)
Check this article's.Truncating and shrinking the transaction log file
I Hope someone will help
So the log file is too small and you want him to shrink it?!?!
Are you sure you posted in the correct thread?
May 20, 2011 at 8:56 am
muthukkumaran (5/20/2011)
Check this article's.Truncating and shrinking the transaction log file
I Hope someone will help
It'll help me in making money, I can charge to fix the damage that it causes.
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 20, 2011 at 9:09 am
GilaMonster (5/20/2011)
muthukkumaran (5/20/2011)
Check this article's.Truncating and shrinking the transaction log file
I Hope someone will help
It'll help me in making money, I can charge to fix the damage that it causes.
🙂 Definiitly gail you only corrected the misinformation.
http://www.sqlservercentral.com/Forums/Topic1078551-1550-4.aspx
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply