Life without model

By Jasper Smith on 28 July 2002 | Tags: Administration

This article is based on INF: Moving SQL Server Databases to a New Location with Detach/Attach (Q224071). During the process of testing this process on a test server and moving the model and msdb databases a "funny" thing happened, the model database "disappeared". Here's what happened and why and how SQL Server dealt with the loss. Moving the system databases might be required when adding new disks or external arrays to your server or reconfiguring the RAID strategy.

In order to detach both the model and msdb databases, SQL Server must be started with the -T3608 trace flag enabled .In EM right click on the instance and choose Properties and Click on Startup Parameters and add the -T3608 startup parameter. Having done this I stopped and started the SQL Server service and opened a QA session. (You can use sp_helpfile to determine the location of the data and log files)

use master
go
sp_detach_db 'model'
go
sp_detach_db 'msdb'
go

With that done I then attempted to re-attach them (for the purpose of this example I am not actually moving the files at all)

use master
go

sp_attach_db 'model',
	        'D:\MSSQL$TEST2\Data\model.mdf',
	        'D:\MSSQL$TEST2\Data\modellog.mdf'
go
sp_attach_db 'msdb',
	        'D:\MSSQL$TEST2\Data\msdbdata.mdf',
	        'D:\MSSQL$TEST2\Data\msdblog.ldf'
go

This resulted in the following output

Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'D:\MSSQL$TEST2\Data\modellog.mdf' may be incorrect.

Did anyone spot the typo? Realising my error I changed the model log filename to modellog.ldf and ran the attach command for just the model database again.

use master
go

sp_attach_db 'model',
	        'D:\MSSQL$TEST2\Data\model.mdf',
	        'D:\MSSQL$TEST2\Data\modellog.ldf'

This resulted in the following output

The command(s) completed successfully.

So, apart from a minor problem, everything seemed to have gone well. I removed the -T3608 startup parameter and stopped and started the SQL Server service and opened up Enterprise Manager.

What's wrong with this picture ?

A distinct lack of the model database - so how is SQL Server running? Lets have a look at sysdatabases table (some columns removed for brevity):

name	        dbid    status		status2
master		1	24		1090519040
msdb		3	1073741848	1090519040
Northwind	6	28		1090519040
pubs		5	24		1627389952
tempdb		2	8		1090519040

It seemed quite odd to me that SQL Server started up without errors whilst the model database was missing but looking at the physical files and the SQL error log helped shed a bit of light on the situation. tempdb data and log files were exactly the same size as msdb and there was an odd entry in the SQL Error log

WARNING: PRIMARY tempdb file is smaller than PRIMARY model file. Resizing.

So it looks very much like SQL Server has taken msdb to be the "model" database because it attached with dbid=3 when the first attempt to attach model failed because of the misspelled logfile name and because this was a clean install the tempdb was still the same size as the old model and had to be grown to match the size of the "new model" - msdb. We can confirm this by running a quick query to see if tempdb contains all the tables from msdb

select count(*) as Result
from msdb.INFORMATION_SCHEMA.TABLES a
left join tempdb.INFORMATION_SCHEMA.TABLES b
on a.TABLE_NAME=b.TABLE_NAME
where b.TABLE_NAME IS NULL

Result
--------------
0

So tempdb has ALL the tables that msdb contains which is consistent with the behaviour we would expect, if indeed SQL Server is using msdb as the "model" database. So next I tried creating a new database called TEST.

create database TEST

Re-running the INFORMATION_SCHEMA query above we find that our new database TEST contains ALL the msdb tables

So lets push things a bit further and see what works and what breaks!

My assumption is that SQL Server is using the msdb database as it would normally use the model databases because it was attached with a dbid of 3 which seems to be hard coded into the SQL Server code as the model database. Both tempdb and any user database created contains all the objects and data from the msdb database. All attempts to reattach the original model database have failed , although no errors are raised. The sp_attach_db stored procedure actually is a wrapper for the CREATE DATABASE ... FOR ATTACH TSQL statement so lets try and run that and see if we get any useful errors

CREATE DATABASE [model] ON
(FILENAME ='D:\MSSQL$TEST2\Data\model.mdf' ),
(FILENAME= 'D:\MSSQL$TEST2\Data\modellog.ldf' )
FOR ATTACH

The command(s) completed successfully.

Well, nothing useful there, except we can conclude that the CREATE DATABASE statement also seems to have some built in low-level logic that prevents it attaching a database called model when a database with a dbid of 3 already exists I was able to run the above statement with a database name of model2 and it attached the database, and if I run an attach statement for any other database twice, then the second run always raises a "database already exists" error as we would expect. Thus it seems that the name model is a special case and is hard coded into parts of the SQL Server base code.

We can see more examples of this when we open up Enterprise Manager and try and view the Properties of any database

Error 21776 : [SQL-DMO] The name 'model' was not found in the Databases collection

Quite right! But why is this error generated. I can only assume that the Enterprise Manager code has a hard coded reference to the model database by name and since it does not exist it throws an error. After clicking on the OK button for the error the Properties dialog is displayed as normal.Whilst we were able to create a database as normal via TSQL what happens if we try and use Enterprise Manager to create a new database. First we get the same error as above, we clear the error and enter a database name of TEST and click on OK. We now get another error

Error 1803 : CREATE DATABASE failed. Could not allocate enough disk space for a new database on the named disks. Total space allocated must be at least 12MB to accommodate a copy of the model database.

If we look at the Data Files and Transaction Logs tabs of the Create Database dialog we see they are defaulting to 1 MB This would be the case if we had an unaltered model database, and in a normal installation if we increase the size of model then this Create Database dialog reflects that change by defaulting the file sizes to those of our altered database. In our case though, because model is missing, the dialog cannot cope with this, and defaults to 1 MB and thus our CREATE DATABASE fails because we need at least 12 MB - the size of msdb. Changing the Data File size to 12 MB allows us to create the database succesfully, once again it contains all the objects and data from our msdb/model database.

So it seems that bar a few little errors, our msdb/model database is working well in its "model" role but is it still msdb? I set up jobs,alerts,Database maintenance Plans,DTS packages using both Enterprise Manager and Query Analyzer and all ran without error. One interesting side effect of our msdb/model hybrid is that you cannot create a database whilst SQL Agent Service is running! This is due to the fact that the CREATE DATABASE statements needs exclusive access to the model/msdb database and with SQL Agent running it gets blocked by the SQLAgent - Generic Refresher and Alert Engine.

So we have a working SQL Server, we can create new databases and still run jobs,alerts,backups and save DTS packages using our hybrid model/msdb setup with only a few minor errors that seem to be due to the hardcoding of the name model into the SQL Server Code and the Enterprise Manager code. However, its not really ideal so lets put it back to normal. We add the -T3608 startup parameter back and restart the SQL Service and then run the following

sp_detach_db 'msdb'
go
sp_attach_db 'model',
	        'D:\MSSQL$TEST2\Data\model.mdf',
	        'D:\MSSQL$TEST2\Data\modellog.ldf'
go
sp_attach_db 'msdb',
	        'D:\MSSQL$TEST2\Data\msdbdata.mdf',
	        'D:\MSSQL$TEST2\Data\msdblog.ldf'
go

We remove the -T3608 startup parameter and restart the SQL Service and all is back to how it is meant to be and no lasting has been done to our server. If nothing else, this shows you have to do something VERY wrong to upset SQL Server. It takes a licking and keeps on ticking :-)