PRB: Problems with Altering Model Database to Move Syslogs (67932)

SYMPTOMS

If you alter the model database to move the syslogs to a separate device, create some tables within a model, and then bring the SQL Server down, when an attempt is made to bring the server back up the server will not start. The following error messages are written to the error log:

...
Recovering database 'model'
clearing temp db
Error 806, Severity: 21, State: 1
Could not find virtual page for logical page 358
Error: 1619, Severity:21, State: 1
Could not open TEMPDB, unable to continue.

CAUSE

SQL Server looks for information within the model database when it rebuilds tempdb. Problems occur when the syslogs of the two databases are not the same size.

WORKAROUND

To avoid this situation, alter both the model and tempdb in the following manner:

NOTE: Make sure the master device is large enough:

   alter database model on master=2
   go
   alter database tempdb on master=2
   go
   sp_logdevice model, master
   go
   sp_logdevice tempdb,master
   go