Instance will not start

  • 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????

  • 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!

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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>

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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"

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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!

  • 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