June 10, 2013 at 3:54 am
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
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
June 10, 2013 at 3:58 am
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
June 10, 2013 at 4:03 am
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...
June 10, 2013 at 4:08 am
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
June 10, 2013 at 4:38 am
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!! 😀
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
July 21, 2016 at 2:41 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply