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?

mtassin (8/18/2010)

---

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.

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