TempDB

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: 31866 More actions · Answer 6

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 More actions · Answer 7

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: 31866 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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: 31866 More actions · Answer 11

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 More actions · Answer 12

VM-723206

SSCrazy

Points: 2964

August 24, 2010 at 7:16 am

#1210993

Thanks for the additional information all! 🙂

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 13

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 More actions · Answer 14

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