mistery of tempdb..??

Model database is template for newly created databases, i read tempdb also created using the template of model database, if its true why the tempdb recovery model will be simple even the model database recovery model is full..?

Also if we add multiple datafiles to tempdb they will exists even after restart..?
more ▼

asked Mar 21 '11 at 10:46 PM in Default

Manikreddy gravatar image

414 24 26 28

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

The tempDB seems to be a special case. Objects within the Model database are created in TempDB when the server restarts; however, the size / recovery model are not taken over.

Multiple datafiles in TempDB will persist after restart, but they'll be set back to their original size, as reported in master_files - take a look at @Fatherjack's [question][1] and the discussion afterwards.

[1]: http://ask.sqlservercentral.com/questions/34681/inconsistent-system-database-details
more ▼

answered Mar 21 '11 at 11:36 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

And just imagine what would happen to tempdb if it wasn't on the simple recovery model!
Mar 22 '11 at 12:56 AM David Wimbush
I was just thinking about that on my way into work... yuck.
Mar 22 '11 at 01:07 AM ThomasRushton ♦

I dare:


Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.
Mar 22 '11 at 02:02 AM WilliamD

@WilliamD - that's trying to set the recovery mode to its current value... coward! ;-)

also, option "OFFLINE" and "READONLY" can't be set for that database.

(Just reading Ch3 in Delaney...)
Mar 22 '11 at 02:11 AM ThomasRushton ♦

Sorry @Thomas, changed my comment to FULL now, it still proves the point though. This was a sandbox, so no cowardice on my part. :)

Seems that MS has been careful here to make sure (this is a SQL 2K8 box, so YMMV):


Msg 5058, Level 16, State 4, Line 1
Option 'READ_ONLY' cannot be set in database 'tempdb'.


Msg 5058, Level 16, State 4, Line 1
Option 'OFFLINE' cannot be set in database 'tempdb'.
Mar 22 '11 at 02:29 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
as tempdb is a globally shared database , which is used by all the user defined databases and server objects.it is created only when server is restarted.we can shrink the database , we cant have the backup of database.for better performace assign tempdb to other drive.
more ▼

answered Mar 24 '11 at 02:14 AM

ramesh 1 gravatar image

ramesh 1
2.1k 59 67 69

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 21 '11 at 10:46 PM

Seen: 1646 times

Last Updated: Mar 21 '11 at 10:46 PM