TempDB

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by VM » TempDB

16 posts, Page 2 of 2

««1 2

TempDB

Rate Topic

Display Mode

Topic Options

Author

Message

Nils Gustav Stråbø

Posted Wednesday, August 18, 2010 3:01 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 1,786, Visits: 3,323

Also interesting to know that tempdb's log does not behave like normal database logs. It only logs UNDO (for rollbacks) operations and not REDO since there is no need for any recovery during SQL Server startup. TempDb is just recreated from scratch.

Post #971491

webrunner

Posted Wednesday, August 18, 2010 3:19 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:05 PM
Points: 2,117, Visits: 2,209

mtassin (8/18/2010)

webrunner (8/18/2010)

What I am wondering is, if hypothetically this restriction were lifted and one could set the recovery model of tempdb to Full, what would be the impact of a tempdb in Full recovery model on that hypothetical SQL Server system? Would it slow to a crawl, encounter errors, etc.?

Thanks again,
webrunner

You'd defeinately see a performance hit as tempdb's log continued to grow like mad.

After all... Tempdb is the clearing house for just about everything. Order by, Group by, insert, update, delete... all of it goes through there to some degree or other. I've got databases where my t-log backups are close to a GB every 15 minutes... I'm not sure I could take t-log backups fast enough to keep the t-log file for tempdb from growing faster than backups could keep it at a reasonable size.

Thanks! That is what I was curious about. Thanks also to Nils for additional information about the UNDO/REDO logging in tempdb.

- webrunner

-------------------
"The chemistry must be respected." - Walter White

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Post #971495

VM-723206

Posted Tuesday, August 24, 2010 7:16 AM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, April 06, 2013 12:20 AM
Points: 649, Visits: 263

Thanks for the additional information all! :)

Post #974094

Chris Harshman

Posted Friday, September 03, 2010 11:03 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:19 AM
Points: 1,562, Visits: 1,716

Unfortunately it is possible for TempDB to not be in simple recovery mode. Where I recently started as the DBA, they have an instance where someone in the past must have changed the mode to full recovery, then performed the upgrade to 2005. Since in 2005 and 2008 you can't change the recovery mode as someone mentioned, I'm stuck with it in full recovery mode and haven't found a way to change it. Fortunately, the log for this TempDB doesn't seem to grow out of control as was suggested.

Post #980365