Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

Corrupt Model database

An interesting case arose at work the other day. SAN issues had caused SQL Server to shutdown and it was unable to be restarted because the data files were unavailable.

Once the SAN was back online the instance still could not be restarted. The error logs were reporting that tempdb could not be created because there was not enough disk space. After  looking at disk space and the file sizes in sys.master_files it seemed that there was plenty of disk space for tempdb.

A closer look at the error logs revealed an issue with the model database transaction log file. The SAN issues had resulted in the model database’s ldf file¬†disappearing. The SQL server restart sequence was starting the master successfully, attempting unsuccessfully to start the model database, logging the error, and then attempting to create tempdb as a copy of model – this was causing the error I was seeing.

I had a model database backup, but I couldn’t restore it because SQL Server wouldn’t start.

The solution was to restore the model backup with a different name to another instance. Detach the database. Copy the mdf and ldf files to my original instance. Start SQL Server.


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...