SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Does BACKUP use the transaction log?


Does BACKUP use the transaction log?

Author
Message
jhager
jhager
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1320 Visits: 341
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555191 Visits: 47757
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, MVP, M.Sc (Comp Sci)
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


jhager
jhager
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1320 Visits: 341
Thanks for replying and the info. I'll increase the log file as you've suggested.

~ Jeff
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)

Group: General Forum Members
Points: 105330 Visits: 14506
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
jhager
jhager
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1320 Visits: 341
Thanks, I'm always interested to learn more about the product and its inner workings.

~ Jeff
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search