mistery of tempdb..??

Question

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..?

Answer 1

0

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 and the discussion afterwards.

answered Mar 21 '11 at 11:36 PM

ThomasRushton ♦
29.5k ● 6 ● 9 ● 36

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:

ALTER DATABASE tempdb SET RECOVERY FULL

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):

ALTER DATABASE tempdb SET READ_ONLY

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

ALTER DATABASE tempdb SET OFFLINE

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)

Answer 2

0

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.

answered Mar 24 '11 at 02:14 AM

ramesh 1
1.7k ● 46 ● 63 ● 68

(comments are locked)

mistery of tempdb..??

Follow this question

Related Questions