TempDB

  • Comments posted to this topic are about the item TempDB

  • Good question, thanks.

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

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

    Enjoy,

    Michael

  • this was easy.

    SQL DBA.

  • I agree...

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

    does indeed make for interesting reading regarding system db's

  • 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

  • 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

  • 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]

  • 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

  • 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]

  • 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.

  • 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

  • Thanks for the additional information all! 🙂

  • 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.

  • 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 15 total)

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