January 26, 2010 at 10:15 am
I installed a new Named Instance on my SQL Server 2005 box.
Once the install was done and the service pack was applied, I began to do my usual cleanup ... moving the system databases etc...
now my instance will not start. I know what I did.. I just can't figure out how to fix it!!!
When I ran my alter statements to move the MSDBData.mdf and Model.mdf to their new locations, I ran the alter statements like this:
ALTER DATABASE MSDB
MODIFY FILE ( NAME = MSDBData ,
FILENAME = 'F:\MSSQL\OVOPS\Datafiles\MSDBData.mdf' )
GO
ALTER DATABASE Model
MODIFY FILE ( NAME = Modeldev,
FILENAME = 'F:\MSSQL\OVOPS\Datafiles\MSDBData.mdf' )
GO
OBVIOUSLY I named the MSDB and the MODEL .MDF files the same!!!! DOH!!!!
Not realizing what I did.... I stopped the instance, moved the system .mdl and .ldf files to their new locations and when I tried to start the instance it will not start.
Does anyone have any ideas on how I can get myself out of this pickle????
January 26, 2010 at 10:25 am
Since the instance does need model, but doesn’t need MSDB to start, try renaming your model to MSDBData.mdf (keeping a copy of that original MSDB file handy of course), start the service, then run the correct Alter Database statements. Good luck!
January 26, 2010 at 10:35 am
How about start sql server with minimal configuration and single user mode -m and -f. Once your server is restart, you could alter the database and set the right files again.
January 26, 2010 at 11:01 am
What does the SQL error log say? It's a textfile, will have the name ERRORLOG.
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
January 26, 2010 at 11:14 am
2010-01-26 13:03:20.18 Server This instance of SQL Server last reported using a process ID of 9096 at 1/26/2010 9:34:35 AM (local) 1/26/2010 2:34:35 PM (UTC). This is an informational message only; no user action is required.
2010-01-26 13:03:20.18 Server Registry startup parameters:
2010-01-26 13:03:20.18 Server -f -T3608
2010-01-26 13:03:20.18 Server -T3608
2010-01-26 13:03:20.18 Server -dF:\MSSQL\OVOPS\Datafiles\master.mdf
2010-01-26 13:03:20.18 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\LOG\ERRORLOG
2010-01-26 13:03:20.18 Server -l G:\MSSQL\OVOPS\Logfiles\mastlog.ldf
2010-01-26 13:03:20.18 Server Error: 17113, Severity: 16, State: 1.
2010-01-26 13:03:20.18 Server Error 3(The system cannot find the path specified.) occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
January 26, 2010 at 11:32 am
I tried to start from a command line using -f -m and this is what I got:
C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\Binn>sqlservr -sOVOPS -m -f
2010-01-26 13:29:44.60 Server Microsoft SQL Server 2005 - 9.00.4035.00 (Int
el X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2010-01-26 13:29:44.60 Server (c) 2005 Microsoft Corporation.
2010-01-26 13:29:44.60 Server All rights reserved.
2010-01-26 13:29:44.60 Server Server process ID is 5284.
2010-01-26 13:29:44.60 Server Authentication mode is MIXED.
2010-01-26 13:29:44.60 Server Logging SQL Server messages in file 'C:\Progr
am Files\Microsoft SQL Server\MSSQL.5\MSSQL\LOG\ERRORLOG'.
2010-01-26 13:29:44.60 Server This instance of SQL Server last reported usi
ng a process ID of 5140 at 1/26/2010 1:03:20 PM (local) 1/26/2010 6:03:20 PM (UT
C). This is an informational message only; no user action is required.
2010-01-26 13:29:44.60 Server Registry startup parameters:
2010-01-26 13:29:44.60 Server -f -T3608
2010-01-26 13:29:44.60 Server -T3608
2010-01-26 13:29:44.60 Server -dF:\MSSQL\OVOPS\Datafiles\master.mdf
2010-01-26 13:29:44.60 Server -e C:\Program Files\Microsoft SQL Serve
r\MSSQL.5\MSSQL\LOG\ERRORLOG
2010-01-26 13:29:44.61 Server -l G:\MSSQL\OVOPS\Logfiles\mastlog.ldf
2010-01-26 13:29:44.61 Server Command Line Startup Parameters:
2010-01-26 13:29:44.61 Server -s OVOPS
2010-01-26 13:29:44.61 Server -m
2010-01-26 13:29:44.61 Server -f
2010-01-26 13:29:44.61 Server Error: 17113, Severity: 16, State: 1.
2010-01-26 13:29:44.61 Server Error 3(The system cannot find the path speci
fied.) occurred while opening file 'master.mdf' to obtain configuration informat
ion at startup. An invalid startup option might have caused the error. Verify yo
ur startup options, and correct or remove them if necessary.
C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\Binn>
January 26, 2010 at 11:42 am
It looks to me that the server could not find master file. It is looking for it at: F:\MSSQL\OVOPS\Datafiles\master.mdf Can you verify that you actually move master.mdf to this location? The question was related to msdb and model move, but you also seem to have problem with master itself.
It is advised that you should not move master or mssqlsystemresource. It is possible to move it in 2005, but not 2008.
--sopheap
January 26, 2010 at 11:43 am
Ok, it's not model or msdb that you messed up, it's master.
Is the master data file in this directory? F:\MSSQL\OVOPS\Datafiles\master.mdf
Is the master log file in this directory? G:\MSSQL\OVOPS\Logfiles\mastlog.ldf
If not, move the files there and try again to start SQL. If they are, check that SQL has permissions to those 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
January 26, 2010 at 11:48 am
Sopheap Suy (1/26/2010)
It is advised that you should not move master or mssqlsystemresource. It is possible to move it in 2005, but not 2008.
It's possible to move master, providing you follow the correct procedure. It's the system resource DB that shou;d not be moved in 2008.
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
January 26, 2010 at 12:47 pm
My start parameters are looking for the master here:
dF:\MSSQL\OVOPS\Datafiles\master.mdf
2010-01-26 13:29:44.60 Server -e C:\Program Files\Microsoft SQL Serve
r\MSSQL.5\MSSQL\LOG\ERRORLOG
2010-01-26 13:29:44.61 Server -l G:\MSSQL\OVOPS\Logfiles\mastlog.ldf
I checked both paths and the master.mdf and mastlog.ldf exist.
January 26, 2010 at 12:55 pm
There's a space missing on the first of those parameters
-dF:\MSSQL\OVOPS\Datafiles\master.mdf
should be
-d F:\MSSQL\OVOPS\Datafiles\master.mdf
Fix that and see if that lets SQL start or at least changes the errors.
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
January 26, 2010 at 12:56 pm
I checked the registry and the startup parameters for this instance seems fine:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.5\MSSQLServer\Parameters]
"SQLArg0"="-f"
"SQLArg1"=" -T3608"
"SQLArg2"=" -dF:\\MSSQL\\OVOPS\\Datafiles\\master.mdf"
"SQLArg3"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL.5\\MSSQL\\LOG\\ERRORLOG"
"SQLArg4"="-lG:\\MSSQL\\OVOPS\\Logfiles\\mastlog.ldf"
January 26, 2010 at 1:01 pm
Are you 100% sure that the directory F:\MSSQL\OVOPS\Datafiles\master.mdf exists, the file is there and SQL has full permission to that directory?
If this is a clustered instance, check that the drive is a dependency of the SQL service (if it's not, it won't be able to see it)
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
January 27, 2010 at 6:55 am
gail,
Thanks for all your help. My startup parameters WERE miss-aligned (spaces where there shouldn't have been and one missing)
I fixed the startup parameters in the registry and then I was able to start in minimal / single user mode and fix my MSDB\Model Alter statement debackle!
January 27, 2010 at 8:51 am
For future reference, it is much easier to install the system databases into their final location instead of using the default values and then moving them.
During the install, select the advanced options and change the location for the data files only. Do not change the location for anything else. Doing it this way moves all of the folders and files in the MSSQL.x directory to that new location, including the log files - error files, etc and sets the right permissions.
The only cleanup after that related to system setup is to make sure you define correct default directories for data and log files and moving of the temp database (if needed).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply