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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1061 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 (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216373 Visits: 46277
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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6017 Visits: 9892
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 (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216373 Visits: 46277
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1061 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
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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