June 3, 2008 at 6:55 am
I needed to relocate tempdb files from the standard installation drive (c:) to d:. I have had to do this before and I used the following script:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\MSSQL\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\MSSQL\templog.ldf');
GO
I apparently got ahead of myself and omitted the file names on the MODIFY statements. So, the directory structure is there, but there is no file name.
Does anyone have anyone have any suggestions on what I can do to correct this problem? I have no idea where to go from here. I can start the server using sqlservr.exe but it doesn't seem to get me anywhere
June 3, 2008 at 7:31 am
Hi Zigster,
did you stop and restart the sql server instance?
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\MSSQL\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\MSSQL\templog.ldf');
GO
Now you must stop and restart the sql server instance.
Then take a look to file changes:
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');
Important, you have to delete the old tempdb.mdf/ -ldf
[font="Verdana"]CU
tosc[/font]
June 3, 2008 at 9:18 am
My problem started when I restarted the SQL Server Instance. Right click on the SQL server instance and selecting restart in SSMS would not work. I started checking the event logs to see why as the message was vague and ambiguous. There are three events that point me to what helped me figure out what went wrong.
============================================================================
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:5123
Date:6/3/2008
Time:9:20:25 AM
User:N/A
Computer:
Description:
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'd:\mssql\data'.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 03 14 00 00 10 00 00 00 ........
0008: 0e 00 00 00 44 00 4f 00 ....D.O.
0010: 4e 00 4e 00 41 00 53 00 N.N.A.S.
0018: 51 00 4c 00 35 00 54 00 Q.L.5.T.
0020: 45 00 53 00 54 00 00 00 E.S.T...
0028: 00 00 00 00 ....
============================================================================
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17207
Date:6/3/2008
Time:9:20:26 AM
User:N/A
Computer:
Description:
FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'd:\mssql\data'. Diagnose and correct the operating system error, and retry the operation.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 37 43 00 00 10 00 00 00 7C......
0008: 0e 00 00 00 44 00 4f 00 ....D.O.
0010: 4e 00 4e 00 41 00 53 00 N.N.A.S.
0018: 51 00 4c 00 35 00 54 00 Q.L.5.T.
0020: 45 00 53 00 54 00 00 00 E.S.T...
0028: 06 00 00 00 6d 00 6f 00 ....m.o.
0030: 64 00 65 00 6c 00 00 00 d.e.l...
============================================================================
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17204
Date:6/3/2008
Time:9:20:26 AM
User:N/A
Computer:
Description:
FCB::Open failed: Could not open file d:\mssql\data for file number 1. OS error: 5(Access is denied.).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 34 43 00 00 10 00 00 00 4C......
0008: 0e 00 00 00 44 00 4f 00 ....D.O.
0010: 4e 00 4e 00 41 00 53 00 N.N.A.S.
0018: 51 00 4c 00 35 00 54 00 Q.L.5.T.
0020: 45 00 53 00 54 00 00 00 E.S.T...
0028: 06 00 00 00 6d 00 6f 00 ....m.o.
0030: 64 00 65 00 6c 00 00 00 d.e.l...
============================================================================
I have resolved the issue however. I'll post my solution just in case someone else has a moment and makes this mistake....
Close SQL Management Studio and reopen, but don't sign in. From a command prompt, goto C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
Start the SQL Service in single user mode by typing: "sqlservr.exe -f" (see http://msdn.microsoft.com/en-us/library/ms180965.aspx)
Switch immediately to SSMS and hit new query.
Retype and execute the commands using the subdirectories and file names.
Close SSMS and the Control C the dos command and exit.
Restart SQL Service.
(I had to Reboot for it to take affect)
June 3, 2008 at 9:23 am
:), I was going to suggest what you posted. Single user mode usually helps here.
July 30, 2008 at 7:49 am
Please restart the sql service, then issue can resolve.
if you get same error reply to ravikiran.avadhutha@hotmail.com
Thanks
RK
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply