Fixing TempDB database problems when starting SQL using a minimum config

So the other day I typo’ed something. Usually this isn’t that big a deal, but in this case when fixing tempdb for a customer I told SQL to put two logical files into the same physical file. I was able to get it to let me do this because I first added the files, then realized that I had put them on the wrong drive so I just changed them using an ALTER DATABASE tempdb MODIFY FILE command to move them to the correct drive. Then I had the client restart the SQL Server later when it wasn’t being used.

When SQL tried to come online however there were some lovely errors in the log and SQL wouldn’t come up.

2013-12-08 13:59:38.23 spid9s Error: 5161, Severity: 16, State: 1.
2013-12-08 13:59:38.23 spid9s An unexpected file id was encountered. File id 3 was expected but 9 was read from “E:TempDBstempdev2.ndf”. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.
2013-12-08 13:59:38.24 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

This was a problem.

So I fired up the SQL Server using the “-c -f -m” parameters. This bring the SQL Server up as a command line application (-c), using a minimal config (-f) and in single user mode (-m). However when I tried to run the ALTER DATABASE tempdb MODIFY FILE commands all I was getting was an error about the tempdb not having the files that I was looking for. Apparently when you start SQL Server using a minimal config it only uses the first two tempdb database files, so I wasn’t able to make changes to the files.

Thankfully there’s another way to fix this sort of problem, but it isn’t for everyone. I had to go and edit the master.sys.master_files catalog view manually. Now thankfully it’s a lot easier then it seems, it’s just update statements. But you are using just T-SQL, probably in sqlcmd (at least that is what I was using) to make the changes. Now normally you can’t change catalog views manually using T-SQL. To make these changes you have to connect to the database engine using the DAC or the Dedicated Administrative Connection. Now if you are in a situation where you have to edit this stuff be VERY careful. If you run an update statement without a WHERE clause in here you’ll destroy every database on the system. But for those brave enough to try it, it’ll get the job done.

After running a bunch of update statements for files 3-9 on database_id 2 (which is the tempdb database on every SQL Server out there) a quick restart of the SQL Server service and the customer was back up and running again.

Denny