Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TempDB Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 11:16 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Comments posted to this topic are about the item TempDB
Post #970906
Posted Wednesday, August 18, 2010 6:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
Good question, thanks.
Post #971073
Posted Wednesday, August 18, 2010 6:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 09, 2013 11:12 PM
Points: 1,263, Visits: 1,081
Some may find this additional information on Recovery Models of System DBs helpful:
http://msdn.microsoft.com/en-us/library/ms365937.aspx

Enjoy,
Michael
Post #971074
Posted Wednesday, August 18, 2010 7:47 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:29 PM
Points: 3,924, Visits: 1,586
this was easy.

SQL DBA.
Post #971139
Posted Wednesday, August 18, 2010 8:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, January 31, 2014 8:07 AM
Points: 462, Visits: 119
I agree...
http://msdn.microsoft.com/en-us/library/ms365937.aspx
does indeed make for interesting reading regarding system db's
Post #971147
Posted Wednesday, August 18, 2010 8:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 20,455, Visits: 14,075
Thanks for the Q



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #971193
Posted Wednesday, August 18, 2010 9:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:19 AM
Points: 2,289, Visits: 2,543
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #971214
Posted Wednesday, August 18, 2010 2:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:05 PM
Points: 3,566, Visits: 72,411
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
For tips on how to post your problems
Post #971455
Posted Wednesday, August 18, 2010 2:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:19 AM
Points: 2,289, Visits: 2,543
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #971459
Posted Wednesday, August 18, 2010 2:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:05 PM
Points: 3,566, Visits: 72,411
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
For tips on how to post your problems
Post #971483
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse