December 20, 2011 at 4:17 pm
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
OS NameMicrosoft(R) Windows(R) Server 2003 Standard x64 Edition
Version 5.2.3790 Service Pack 2 Build 3790
Other OS Description R2
Nightly this same database is backed up with BackUpExec though an agent in that completed in about one hour.
I have attempted to backup through SSMS a database in simple recovery mode with the FULL or COPY method. The database is approximately 55 GB. After a couple of hours the database transaction log starts growing from 34 MB to filling the 50 GB drive. I had to stop the backup as the full T-log file prevents any more writes.
Why is the backup causing the T-log file to grow?
😀
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
:hehe:
December 20, 2011 at 5:12 pm
You'll have to show some code and explain more about the situation. A full backup doesn't add to the transaction log and cause it to grow. Something else is happening.
December 20, 2011 at 5:38 pm
Well I am not sure what "code" I could show as I am using SSMS to run the backup. Is there something particular you would like me to post up from a DM?
😀
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
:hehe:
December 20, 2011 at 6:16 pm
That screams index rebuild / reorg or big ETL.
December 20, 2011 at 11:14 pm
Gosh... I think the backup and the log file growing don't have a thing to do with each other. I think someone had a bit of a problem with a query at the same time you were doing the backup... unless you did this all with a maintenance plan that also rebuilt all indexes and you have a huge table.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2011 at 1:01 am
A full backup does write to the transaction log, but it's just a couple of small entries. What's far more important is that for the duration of the full backup, log space cannot be marked as reusable as the full backup has to be able to incorporate that log.
Hence, if you have a long running full backup that overlaps with heavy database activity, you may see the log grow because the log space isn't getting marked reusable by a log backup/checkpoint.
However a 55GB database shouldn't take so long to backup as to cause those kinds of problems.
Is the log growing during the backup or afterwards? If afterwards, check that the DB didn't get switched to full recovery somehow.
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
December 21, 2011 at 10:28 am
The backup runs for about two hours and gets to about 80% when the log file starts to grow as described above. This has happened twice on consecutive attempts to backup the database, once during the business day the other after hours.
I did check and there is a maintenance plan that runs index reorganizations that executed during this time frame. This task completes in less than an hour, 35 to 55 minutes daily, slowing through the day.
😀
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
:hehe:
December 21, 2011 at 10:47 am
wchaster (12/21/2011)
I did check and there is a maintenance plan that runs index reorganizations that executed during this time frame. This task completes in less than an hour, 35 to 55 minutes daily, slowing through the day.
There's your problem. Index reorgs are fully logged and, since there's a full backup running the log space can't be reused until the backup is complete.
Move the job scheduled so that the index reorg and the backup aren't overlapping and the problem should go away.
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
December 21, 2011 at 11:56 pm
wchaster (12/20/2011)
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)OS NameMicrosoft(R) Windows(R) Server 2003 Standard x64 Edition
Version 5.2.3790 Service Pack 2 Build 3790
Other OS Description R2
Nightly this same database is backed up with BackUpExec though an agent in that completed in about one hour.
I have attempted to backup through SSMS a database in simple recovery mode with the FULL or COPY method. The database is approximately 55 GB. After a couple of hours the database transaction log starts growing from 34 MB to filling the 50 GB drive. I had to stop the backup as the full T-log file prevents any more writes.
Why is the backup causing the T-log file to grow?
BWAA-HAAA!!! Based on what Gail just said, tell Boris Kogan to stop running his code while you're doing backups. 😛 It's not that his code is bad but, IIRC, his "AR" code does a heck of a lot of "writes" to the DB. There's also some overnight code that build new billing cycles and populates older ones to the tune of about 4 million rows per day. Maybe even more if the VOIP program was a big success.
If I remember correctly, some of the overnight CostGuard runs for the Finance department where pretty brutal in the "write" department, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply