Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does BACKUP use the transaction log? Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 6:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:02 AM
Points: 58, Visits: 241
Hi,

I noticed several autogrowth events for the transaction log file of a database shortly after its backup job started. I've browsed around MSDN but can't find any mention of whether the transaction log is used for the backup operation.

Not a high-priority issue, just interested to know about it.

Thanks,

Jeff
Post #1451545
Posted Friday, May 10, 2013 6:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
The backup doesn't write to the transaction log, but while there's a backup in progress space in the log can't be reused. If you're seeing autogrow events, it suggests that maybe you need to grow the log a bit larger than what it is, leave space for cases like this.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1451556
Posted Friday, May 10, 2013 6:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:02 AM
Points: 58, Visits: 241
Thanks for replying and the info. I'll increase the log file as you've suggested.

~ Jeff
Post #1451557
Posted Wednesday, May 15, 2013 12:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
jhager, if you're interested, here is an article that explains why the transaction log is involved in full backups and how much of it is required:

http://www.sqlskills.com/blogs/paul/debunking-a-couple-of-myths-around-full-database-backups/


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1452932
Posted Wednesday, May 15, 2013 6:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:02 AM
Points: 58, Visits: 241
Thanks, I'm always interested to learn more about the product and its inner workings.

~ Jeff
Post #1453039
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse