Does a backup cause TempDB to grow

  • I received an alert this morning that our Temp drive had run out of space. When investigating it, I found that no large jobs had run outside of a weekly FULL backup databases job, which backs up about ten databases varying in size from 50GB - 2 TB.

    Do backups (if large enough) cause TempDB to grow?

    Thanks!

  • Backups do not use TempDB.

    Does the backup job do anything other than take a backup?

    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
  • I agree with Gail, no tempdb issue with backups.

    Something else must have run.

  • I did not think that was the case, but wanted to verify with the experts. No other maintenance takes place during that time, so it wouldn't be an index rebuild or anything.

    This server is heavy ETL/BI work, but our changes are tracked meticulously. The only other possibility would be a large historical update on a source data system we subscribe to, requiring a massive delta pull. I will look in to that possibility.

    Thanks!

  • Does your backup process do a database consistency check prior to taking the backup?

    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
  • There is a simple Maintenance Plan backup process for the databases, ands the 'Verify Backup Integrity' option is selected.

    I realized I have no idea what that option is actually doing. Any information on that option is appreciated.

    Thank you!

  • The check backup integrity shouldn't matter, but I think Gail is thinking that if you have a DBCC check of some sort (verify database integrity), this could be using tempdb if configured to do so.

  • Ddono25 (6/10/2013)


    I realized I have no idea what that option is actually doing. Any information on that option is appreciated.

    Virtually nothing other than adding a false sense of security. 🙂

    It's the RESTORE VERIFYONLY command.

    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
  • That is good to know.

    I went searching on the subject, and could not find it, but is that action (RESTORE VERIFYONLY) logged at all? I am trying to cover all of my bases, and it would be helpful to find some info on the subject. Is there a place that has all statements/actions that are logged vs. not logged depending on the recovery model?

    I will continue to look and update here if I find it.

    Thanks again!

  • Logged as in written to the transaction log? If so, no. It's not a data modification, it has no effect on any existing database, therefore it's not written to any transaction log.

    As for what is and is not logged based on recovery model, that's easy. All data modifications are logged in all recovery models.

    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
  • Sorry, I was reading up on different logging/recovery models as well as TempDB issues, and mistyped 'logging' instead.

    Thank you for the great information, definitely a lot more to investigate!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply