Have you ever tried to move the TempDB in SQL Server and gotten the settings wrong? In this article by Pinal Dave we find that he made a simple mistake but was able to quickly solve the issue. He was able to start the instance using trace flag 3608 and then correct the error by deleting a incorrectly named file. He then restarted the SQL Server service and the issue was resolved.
Recently I was planning to give a demo about increasing tempdb files and its performance benefit. So while making the demo at home, I was doing multi-tasking. Talking to my daughter, having food and making demo. So, I ran the script and restarted SQL Server service but SQL Service didn’t start.
I looked into the ERRORLOG (and I would suggest you to look at that log in case of any SQL startup issues) and found below errors before SQL shutdown messages.
2014-12-02 17:03:24.42 spid18s Error: 5161, Severity: 16, State: 1.
2014-12-02 17:03:24.42 spid18s An unexpected file id was encountered. File id 3 was expected but 4 was read from “D:\TempDB\tempdev3.ndf”. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.
2014-12-02 17:03:24.42 spid18s 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.