SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moved Tempdb .mdf now can't restart SQL Server.


Moved Tempdb .mdf now can't restart SQL Server.

Author
Message
WWDMark
WWDMark
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2037 Visits: 530
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555003 Visits: 47750
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, MVP, M.Sc (Comp Sci)
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


HowardW
HowardW
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16783 Visits: 9893
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555003 Visits: 47750
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, MVP, M.Sc (Comp Sci)
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


WWDMark
WWDMark
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2037 Visits: 530
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!! :-D


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
smithrk
smithrk
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 13
HowardW (6/10/2013)
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.


This worked for me.
scripted my planned change
opened a New Query in mgt studio, propted me for the UID and Pwd.
Ran my script.
removed my switches and restarted the server.
Problem solved. it took me longer to find this solution than to apply a working fix.

-side note
I have no idea what caused the issue in the first place.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search