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

Moved Tempdb .mdf now can't restart SQL Server. Expand / Collapse
Author
Message
Posted Monday, June 10, 2013 3:54 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:38 AM
Points: 642, Visits: 519
Hi all,

I've been experimenting with my test box (luckily!!) and was moving various database .mdf and .ldf files to different locations (in line with best practice etc), this all went well and according to plan however, when I did the tempdb I ran the ALTER DATABASE......MODIFY FILE statement for the .mdf file stopped SQL Server, copied (not moved) the .mdf file to the new location then restarted SQL Server now I keep getting a message saying 'The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.'

The event log has the following error messages:
Error 1:-
CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL_MDF_Files\tempdb.mdf'.
Error 2:-
FCB::Open failed: Could not open file D:\Program Files\Microsoft SQL Server\MSSQL_MDF_Files\tempdb.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).

I can't open SSMS to modify the filepath back to the original one so I'm a little stuck! Could anyone offer some advise on how to amend the tempdb .mdf filepath back to its original path without using SSMS?

Many thanks, M



MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: www.markallen-online.co.uk
Business Website: www.weekendwebdesign.co.uk
Post #1461458
Posted Monday, June 10, 2013 3:58 AM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,817, Visits: 35,939
https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

p.s. OS error 5 is Permission Denied. SQL doesn't have permissions to access the new location.



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 #1461459
Posted Monday, June 10, 2013 4:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:34 AM
Points: 1,237, Visits: 9,801
Start SQL Server in minimal configuration mode from the command line (-f flag). This will create tempdb in the default location and allow you to run an ALTER command.

You might also want to try deleting the tempDB files from the new location first and restarting the SQL Service, as you're not meant to copy tempDB when moving it, it's created on startup. If the full path exists and the SQL Server account has permissions, I can't see why it wouldn't be able to create...
Post #1461461
Posted Monday, June 10, 2013 4:08 AM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,817, Visits: 35,939
HowardW (6/10/2013)
You might also want to try deleting the tempDB files from the new location first and restarting the SQL Service, as you're not meant to copy tempDB when moving it, it's created on startup.


Doesn't matter. if the files are available, SQL uses the existing files. If not, it creates new files.



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 #1461464
Posted Monday, June 10, 2013 4:38 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:38 AM
Points: 642, Visits: 519
Thanks for responding so quickly guys.
@Gail - that'll teach me to read the error messages in full - when I grow up I want to be a proper DBA!!



MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: www.markallen-online.co.uk
Business Website: www.weekendwebdesign.co.uk
Post #1461470
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse