July 5, 2013 at 12:36 pm
I needed to move the temp db that was in e:\mssql\data to c:\
This was temporary, because I was replacing the e drive. The sql application is installed on c drive in the default location, but tempdb was in e drive. I ran the code below and restarted the sql services and now SQL studio won't work for me. I can't connect to the server. Is reinstalling SQL server 2008 the only way to fix this?
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'c:\templog.ldf')
July 5, 2013 at 12:49 pm
What's in the error log?
July 5, 2013 at 1:00 pm
Cannot connect to servername
Additional information:
A network-related or instance-specific error occured while establishing a connection to SQL server. The server was not found or was not accessible.....
July 5, 2013 at 1:06 pm
There should be information in the SQL Server error log about it starting. Look for that. tempdb being broken doesn't prevent the instance from starting.
July 5, 2013 at 1:11 pm
http://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
July 5, 2013 at 1:12 pm
Steve Jones - SSC Editor (7/5/2013)
Tempdb being broken doesn't prevent the instance from starting.
Yes it does. If SQL cannot open and clear TempDB, the startup process will fail.
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
July 5, 2013 at 1:28 pm
You are right (as usual), Gail. I thought it would start in read-only mode, but sure enough I get a failure.
The problem is probably rights. Your system cannot open c:\xxx.mdf as a file. You need to do this: http://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx
July 5, 2013 at 4:27 pm
I think you are right. SQL may not have rights to the C: root drive. How do I change the location of the tempdb in sql server if I can't get back into the instance? Is there a setting in registry or something?
July 5, 2013 at 4:28 pm
Did you read the article I referenced?
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
July 5, 2013 at 5:26 pm
It talks more about the masterdb. I cannot try the suggested cmd's because the instance does not start.
July 5, 2013 at 5:37 pm
Maybe scroll down to the section "TempDB location does not exist", which describes how to fix exactly the problem you have?
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply