June 8, 2013 at 7:54 am
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!
June 8, 2013 at 8:00 am
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
June 8, 2013 at 3:52 pm
I agree with Gail, no tempdb issue with backups.
Something else must have run.
June 10, 2013 at 10:42 am
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!
June 10, 2013 at 10:49 am
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
June 10, 2013 at 2:19 pm
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!
June 10, 2013 at 4:50 pm
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.
June 11, 2013 at 1:38 am
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
June 11, 2013 at 9:59 am
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!
June 11, 2013 at 11:07 am
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
June 11, 2013 at 11:34 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy