Recently, I received a call from my coworker who said that he had managed to get SQL Server stuck in Single User Mode. That’s obviously not an ideal situation. When in single user mode, there can be only one connection to the server. Hence the name, ‘Single User Mode’. The engineers over at #Microsoft are getting crafty with their naming schemes!
He started to describe to me the situation that had occurred, and how things had gotten to that point. The background he gave boiled down to this:
- Performing a migration of SQL data to a new server, on a new domain, to hold data in relation to the Exchange migration he was performing.
- In that process, he’d accidentally deleted the directory that the TempDB was stored in.
- The deployment engineer he was working with from the software vendor recommended starting the DB engine in Single User Mode so they could re-point the TempDB location to a new spot. (I don’t actually know if this is possible, and am totally going to run it through my test lab in the near future!)
- Afterwards, tried to start the service and couldn’t get back in. Therefore, SQL is stuck in Single User Mode.
Wow, there’s a lot to digest there. I’m not even sure you can truly get “stuck” in single user mode. It’s just a flag on service start. That’s another thing to look into! He said, “I started the service with net start MSSQLSERVER -m“. That’s interesting to me because he is saying “I started the service manually, with an extra flag”. That means if he ran net start MSSQLSERVER on its own, it should start in Multi User Mode. He said that they did restart the server & service a few times. So clearly that’s not the answer. Weird, but okay.
This is the point that I get logged into the server to see what’s happening with my own two eyes. It’s easy for things to get lost in translation. So my goal is simple. Verify that the service is in fact running, and if so, verify it’s in Single User Mode. If so, figure out why.
Logging into the server, I head into the configuration console. MAYBE by starting the service with the Single User Mode flag, SQL Server is “smart” enough to modify the settings and ensure that it continues to run in Single User Mode until specifically being taken away. Maybe.
Nope, I see no -m in there anywhere. The next thing to do is attempt to start it up and see what happens. I have my doubts at this point that it’s a Single User Mode issue, because he said that they were trying to fix the fact that they removed the directory structure for TempDB.
Starting up the service, I wasn’t met with an error, but the service wasn’t started either. It just died out somewhere along the way. Off to the event viewer to see if there was anything interesting happening there!
There’s lots happening when you start up a service like SQL Server. But the interesting logs are obviously the two errors. This one gave me the exact problem. SQL couldn’t open the TempDB file.
Here’s the thing about the TempDB. It’s temporary. It’s even named TempDB! Of course it’s temporary. Look at Microsoft go! Two useful names in a row! Anyways, TempDB is a unique system database in several ways. One of which, is that it is dropped and recreated every time the server or service is restarted. The Microsoft article about the database even says as much. TempDB is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.
That makes this an extremely simple fix! Recreating the directory structure (D:\TempDB in this instance) allows SQL Server to build the database on startup, and they were back up and running.
So the long story short is this, if you ever find yourself in a situation where you’ve accidentally dropped the wrong drive on a server, or deleted the wrong directory in windows, and this is caused the loss of the TempDB, all you need to do is recreate the file path that SQL Server expects to exist, and it’ll handle the rest.
Doores Ajar 