August 10, 2015 at 1:34 pm
Hello experts,
I need some help with a error I caused on a new server we're developing.
While trying to move the tempdb log file to a different disk, I mistakenly executed the following and then stopped and restarted SQL Server. Yeah, copy and paste screwup on my part. 🙁 🙁
USE master
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'L:\MSSQL11.MSSQLSERVER\MSSQL\Data\templog.ldf')
GO
Now I can't start SQL Server and see this error in the log files:
2015-08-10 15:28:42.55 spid7s Error: 5171, Severity: 16, State: 1.
2015-08-10 15:28:42.55 spid7s L:\MSSQL11.MSSQLSERVER\MSSQL\Data\templog.ldf is not a primary database file.
2015-08-10 15:28:42.55 spid7s Error: 1802, Severity: 16, State: 4.
2015-08-10 15:28:42.55 spid7s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
I did not have remote connections enabled yet, so the resolutions I have found that include sqlcmd or starting in single user configuration are not working.
Does anyone know of a way that might allow me to restore the usual tempdb settings, which I think would allow SQL to start again?
Many thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 10, 2015 at 2:41 pm
You need to either create that path so that the file can be created, or start SQL in single user mode (remote in to the server and work directly) to do the ALTER DATABASE.
Edit: Sorry, just noticed that you made the data and the log point to the same file. In this case you'll have to connect in single user mode to fix it.
See the TempDB section of https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2015 at 2:46 pm
OK, looks like I dug myself out of my own hole. A rare but welcome occurrence.
Here is why I had trouble fixing this:
1. I had not turned on the DAC or learned how to use it - my bad for sure which I will remedy quickly.
2. I did not know other ways of starting the SQL Server to correct the messed-up tempdb config.
Finally, I found this really helpful page, which walked me around the single-user error a lot of people probably get when trying the command-line sql connections.
http://sqlserver-help.com/2013/12/06/help-how-to-fix-error-reason-server-is-in-single-user-mode-only-one-administrator-can-connect-at-this-time/[/url]
The steps that worked for me were:
1. Make sure to right-click on cmd and Run As Administrator.
2. Use a command that includes the /T3608 trace flag so SQL doesn't try to create and check tempdb:
net start MSSQLSERVER /T3608 /m"SQLCMD"
When I got that to work, I still got a shutdown error because of tempdb (of course). But at these pages I learned about the trace flag to skip the tempdb part of startup:
http://dba.stackexchange.com/questions/54075/broken-tempdb-location-and-cant-recover
And this site reminded me to check the ERRORLOG file to confirm whether or not SQL is still shutting down at the create tempdb step, since sometime in the Services or SQL Server Configuration Manager GUI it seems like things are running when they are not.
http://sqlserver-help.com/tag/could-not-create-tempdb/[/url]
And finally, this page walked me through the safest (given that I was nervous) way for me to enter the SQL commands, with GO after I had checked each one for accuracy.
http://sqlserverdb.blogspot.com/2012/04/how-to-start-sql-server-without-tempdb.html
After which I entered this, pressing Enter after each line, which worked:
> sqlcmd
use master
go
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='T:\Data\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='L:\Log\templog.ldf')
go
quit
Luckily, at that point, I was able to restart SQL Server services and connect via SSMS.
Even though this was not a server in full production yet, I was quite scared at having made this mistake, so I'm extremely relieved I was able to fix it without have to reinstall SQL Server. As someone once told me, "Measure twice, cut once." I will be FAR more careful next time I am trying to optimize file locations.
Hope this helps someone else in a time of need.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 10, 2015 at 2:48 pm
GilaMonster (8/10/2015)
You need to either create that path so that the file can be created, or start SQL in single user mode (remote in to the server and work directly) to do the ALTER DATABASE.Edit: Sorry, just noticed that you made the data and the log point to the same file. In this case you'll have to connect in single user mode to fix it.
See the TempDB section of https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
Yes, thanks, Gail! I was able to piece together a recovery, and I will definitely read through your article closely, as it covers TempDB as well.
Thanks!!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply