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.