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.
2014-12-02 17:03:24.93 spid18s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
Since error message before shutdown were talking about tempDB database, I knew that I might have done something wrong while doing multi-tasking. I went back to query windows and read it completely. Initially I added files on C drive (first two commands) but later realized that I should not keep them on C as I already have space issues there, so I moved them using MODIFY FILE (last two commands)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2',
FILENAME = N'C:\TempDB\tempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 0)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3',
FILENAME = N'C:\TempDB\tempdev3.ndf', SIZE = 1024MB, FILEGROWTH = 0)
-- Space issues on C, moving them to D
ALTER DATABASE [tempdb] MODIFY FILE
( NAME = tempdev2, FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 10MB, FILEGROWTH = 0)
ALTER DATABASE [tempdb] MODIFY FILE
( NAME = tempdev3, FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 10MB, FILEGROWTH = 0)
I called my daughter to check and she told me that she can see tempdev3 many times. Bummer! The second last command should have said tempdev2. mdf
I understood the problem but SQL is not getting started now, how can I modify it? Well, the easiest option is removed and add the file with a proper name
1. Start SQL via trace flag 3608
From the command prompt, we can use
net start mssqlserver /T3608
My machine has a default instance, that’s why I have used MSSQLServer. For named instance, we need to use MSSQL$<InstanceName>
If we open ERRORLOG, we should see below
Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.
2. Run ALTER Database
Run ALTER DATABASE command as below to remove unwanted files. I have connected via SQLCMD and I am removing Tempdev2 because that has file as tempdev3. This can be done from Management Studio as well.
We can also run below query to find the current mapping. Database ID = 2 is always tempDB database.
SELECT name, physical_name FROM sys.master_files WHERE database_id = 2
In my case I got below output before removal.
Based on the output you receive, you have to make appropriate changes.
3. Stop SQL Service and start normally.
Once above steps are performed, we should be able to connect to SQL and make changes to TempDB correctly this time.
Have you ever encountered similar tempdb errors? How did you fix them?
Reference: Pinal Dave (https://blog.sqlauthority.com)
This was VERY useful when we had a SQL issue today!
BTW, on SQL 2012 at least, we didn’t need trace flag -T3608, since starting with minimal configuration (-f) did the job for us
Thanks, have just used this. Very useful post!
This got me out of a tricky situation today, thanks for this post!!!!
Thank you sooo much for this post . I saved a ton of trouble with today . Big thanks to you !!
Thank you, Pinal it helped it today
your the best man in SQL. Thank you so much for this post.
This blog post just saved me. This was a my first time using SQLCMD and this blog walked me through the steps needed to get my instance of SQL server to start again. Thank you very much for this post.