SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by VM / TempDB / Latest Posts

RE: TempDB

zymos — Wed, 07 Mar 2012 14:12:47 GMT

Thank you for the question.

RE: TempDB

Chama — Fri, 17 Sep 2010 13:08:49 GMT

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

RE: TempDB

Chris Harshman — Fri, 03 Sep 2010 11:03:34 GMT

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.

RE: TempDB

VM-723206 — Tue, 24 Aug 2010 07:16:22 GMT

Thanks for the additional information all! :)

RE: TempDB

webrunner — Wed, 18 Aug 2010 15:19:13 GMT

[quote][b]mtassin (8/18/2010)[/b][hr][quote][b]webrunner (8/18/2010)[/b][hr]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[/quote]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.[/quote]Thanks! That is what I was curious about. Thanks also to Nils for additional information about the UNDO/REDO logging in tempdb.- webrunner

RE: TempDB

Nils Gustav Stråbø — Wed, 18 Aug 2010 15:01:44 GMT

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.

RE: TempDB

mtassin — Wed, 18 Aug 2010 14:40:46 GMT

[quote][b]webrunner (8/18/2010)[/b][hr]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[/quote]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.

RE: TempDB

webrunner — Wed, 18 Aug 2010 14:16:52 GMT

[quote][b]mtassin (8/18/2010)[/b][hr][quote][b]webrunner (8/18/2010)[/b][hr]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?[/quote]1. That's easyOn our test serverALTER DATABASE tempdb SET RECOVERY FULLResultMsg 5058, Level 16, State 1, Line 1Option 'RECOVERY' cannot be set in database 'tempdb'.2. I think you'd be better off creating a server side trace.[/quote]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

RE: TempDB

mtassin — Wed, 18 Aug 2010 14:09:53 GMT

[quote][b]webrunner (8/18/2010)[/b][hr]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?[/quote]1. That's easyOn our test serverALTER DATABASE tempdb SET RECOVERY FULLResultMsg 5058, Level 16, State 1, Line 1Option 'RECOVERY' cannot be set in database 'tempdb'.2. I think you'd be better off creating a server side trace.

RE: TempDB

webrunner — Wed, 18 Aug 2010 09:13:24 GMT

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

RE: TempDB

SQLRNNR — Wed, 18 Aug 2010 08:42:32 GMT

Thanks for the Q

RE: TempDB

matthew.pullen — Wed, 18 Aug 2010 08:01:50 GMT

I agree...http://msdn.microsoft.com/en-us/library/ms365937.aspxdoes indeed make for interesting reading regarding system db's

RE: TempDB

SanjayAttray — Wed, 18 Aug 2010 07:47:48 GMT

this was easy.

RE: TempDB

michael.kaufmann — Wed, 18 Aug 2010 06:38:13 GMT

Some may find this additional information on Recovery Models of System DBs helpful:[url]http://msdn.microsoft.com/en-us/library/ms365937.aspx[/url]Enjoy,Michael

RE: TempDB

Daniel Bowlin — Wed, 18 Aug 2010 06:37:13 GMT

Good question, thanks.

TempDB

VM-723206 — Tue, 17 Aug 2010 23:16:37 GMT

Comments posted to this topic are about the item [B]TempDB[/B]