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.