TempDB

  • VM-723206

    SSCrazy

    Points: 2964

    Comments posted to this topic are about the item TempDB

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Good question, thanks.

  • michael.kaufmann

    SSCrazy

    Points: 2816

    Some may find this additional information on Recovery Models of System DBs helpful:

    http://msdn.microsoft.com/en-us/library/ms365937.aspx

    Enjoy,

    Michael

  • SanjayAttray

    SSChampion

    Points: 13157

    this was easy.

    SQL DBA.

  • matthew.pullen

    SSChasing Mays

    Points: 644

    I agree...

    http://msdn.microsoft.com/en-us/library/ms365937.aspx

    does indeed make for interesting reading regarding system db's

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the Q

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • webrunner

    SSC-Dedicated

    Points: 30304

    I'm grateful for the point. It was easy for me - which I can't say about too many questions, but I'm especially glad I didn't second-guess myself into a wrong answer by being suspicious and thinking that the obvious answer was somehow a trick.

    Also, I have two side hypothetical questions:

    1. What would happen if tempdb could be set to bulk-logged or full recovery? What would be the consequences for the SQL Server system (performance, etc.)?

    2. Are there ways to audit tempdb processing that are (1) feasible and (2) might be desirable for those wanting to track that activity for security reasons, given that it can't be logged fully and gets recreated every time SQL Server restarts? Or is that just a pointless idea stemming from my having seen a few CSI episodes?

    Just curious.

    Thanks,

    webrunner

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

  • mtassin

    SSC-Insane

    Points: 23099

    webrunner (8/18/2010)


    I'm grateful for the point. It was easy for me - which I can't say about too many questions, but I'm especially glad I didn't second-guess myself into a wrong answer by being suspicious and thinking that the obvious answer was somehow a trick.

    Also, I have two side hypothetical questions:

    1. What would happen if tempdb could be set to bulk-logged or full recovery? What would be the consequences for the SQL Server system (performance, etc.)?

    2. Are there ways to audit tempdb processing that are (1) feasible and (2) might be desirable for those wanting to track that activity for security reasons, given that it can't be logged fully and gets recreated every time SQL Server restarts? Or is that just a pointless idea stemming from my having seen a few CSI episodes?

    1. That's easy

    On our test server

    ALTER DATABASE tempdb SET RECOVERY FULL

    Result

    Msg 5058, Level 16, State 1, Line 1

    Option 'RECOVERY' cannot be set in database 'tempdb'.

    2. I think you'd be better off creating a server side trace.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • webrunner

    SSC-Dedicated

    Points: 30304

    mtassin (8/18/2010)


    webrunner (8/18/2010)


    I'm grateful for the point. It was easy for me - which I can't say about too many questions, but I'm especially glad I didn't second-guess myself into a wrong answer by being suspicious and thinking that the obvious answer was somehow a trick.

    Also, I have two side hypothetical questions:

    1. What would happen if tempdb could be set to bulk-logged or full recovery? What would be the consequences for the SQL Server system (performance, etc.)?

    2. Are there ways to audit tempdb processing that are (1) feasible and (2) might be desirable for those wanting to track that activity for security reasons, given that it can't be logged fully and gets recreated every time SQL Server restarts? Or is that just a pointless idea stemming from my having seen a few CSI episodes?

    1. That's easy

    On our test server

    ALTER DATABASE tempdb SET RECOVERY FULL

    Result

    Msg 5058, Level 16, State 1, Line 1

    Option 'RECOVERY' cannot be set in database 'tempdb'.

    2. I think you'd be better off creating a server side trace.

    Thanks - just one clarification. For #1 above, I understand that changing the recovery model from Simple is not possible in the actual SQL Server software - which is why the error is thrown.

    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

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

  • mtassin

    SSC-Insane

    Points: 23099

    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.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    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.

  • webrunner

    SSC-Dedicated

    Points: 30304

    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

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

  • VM-723206

    SSCrazy

    Points: 2964

    Thanks for the additional information all! 🙂

  • Chris Harshman

    SSC-Forever

    Points: 42145

    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.

  • Chama

    SSC Enthusiast

    Points: 198

    We must or should be create database backups of model becuase we never can be happen it for advise

Viewing 15 posts - 1 through 15 (of 16 total)

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