Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

issue with tempdb Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 12:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 146, Visits: 382
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')



Post #1470836
Posted Friday, July 5, 2013 12:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
What's in the error log?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1470837
Posted Friday, July 5, 2013 1:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 146, Visits: 382
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.....



Post #1470844
Posted Friday, July 5, 2013 1:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1470849
Posted Friday, July 5, 2013 1:11 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1470852
Posted Friday, July 5, 2013 1:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1470853
Posted Friday, July 5, 2013 1:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
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







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1470862
Posted Friday, July 5, 2013 4:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 146, Visits: 382
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?


Post #1470879
Posted Friday, July 5, 2013 4:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
Did you read the article I referenced?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1470880
Posted Friday, July 5, 2013 5:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 146, Visits: 382
It talks more about the masterdb. I cannot try the suggested cmd's because the instance does not start.


Post #1470885
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse