SQL Server Service won’t start because I forgot to move the Resoruce database

On Sunday I got a call from a DBA from another team who had an issue that the SQL Server service was not starting up. It was a SQL Server 2005 instance. The SQL Server Error Log had the following error messages.

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:\mssqlsystemresource.mdf" may be incorrect.
Error: 945, Severity: 14, State: 2.
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

The SQL Server service was unable to find the files of mssqlsystemresource (a.k.a. resource) database. The SQL Server service was not starting up after the server was rebooted. Here is what happened a couple of days ago. The E drive hosting the the system databases had to be re-provisioned and the DBA had to move all the SQL Server databases to a new drive. The DBA moved all the system databases but forgot to move the mssqlsystemresource database. This came into light when the SQL Server service got restarted. But by that time the E drive was already out of the server.

I could not continue to support this case due to some other reason. Here is how I had planned to resolve this issue.

  • Copy the files of the mssqlresource database from another SQL Server 2005 instance which is of the same build to the new drive on this server.
  • Start SQL Server from the command prompt using sqlservr.exe. The -f (minimal configurtion) or -m (single-user mode) parameters have to be passed on to sqlservr.exe along with the Trace Flag 3608 (recovers only the master database).
  • Issue ALTER DATABASE command to move the files of mssqlresource database to point to the new location.

The actual command would be
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr -f -T3608

This command would start the SQL Server in minimal configuration mode and recover only the master database. Then executing the below command in sqlcmd would move the resource database to the new location.

ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=data, FILENAME= 'X:\mssqlsystemresource.mdf')
GO
ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=LOG, FILENAME= 'X:\mssqlsystemresource.ldf')
GO

The output of this command would be similar to the one below.

After making these changes, the SQL Server service would start gracefully.

6 thoughts on “SQL Server Service won’t start because I forgot to move the Resoruce database

  1. Pingback: Tweets that mention SQL Server Service won't start because I forgot to move the Resoruce database | SQL Server DBA Diaries of Pradeep Adiga -- Topsy.com

  2. Maha

    Pradeep, are we sure that the logical name of the data & log file. i believe this couldn’t be found from sys.databases dmv as well.

    Reply

Leave a Reply