THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Error: 945, Severity: 14, State: 2 | Database 'mssqlsystemresource' cannot be opened

This is a somewhat continuation post from my post Error: 5123, Severity: 16, State: 1 when moving TempDB.  So breaking my laptops SQL Instance and then fixing it got me interested in how else could I break SQL Server and make it not start up.  I recalled having seen a post where someone moved master and the resource database, and they couldn't get SQL Server to startup after doing so.  I couldn't find that specific thread on the forums, but I didn't really need it.  I can break a SQL Server without having to follow someone else's instructions, at least I have been pretty successful at doing so in the past while testing things on development servers.

To move the master database is fairly simple and doesn't require a whole lot of effort to do.  It is documented a few different places online in the Books Online so I won't go into specifics here.  One important item listed in the Moving System Databases BOL entry is:

The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the master database, you must also move the Resource database to the same location as the master data file.

This is where SQL Server 2000 DBA's can get themselves into trouble since we know how to move master, we sometimes assume that we know what to do and just jump right in and do what we know.  It is also one way to generate the error that is the title of this post.  To demonstrate this problem and how to fix it, I moved the master.mdf and master.ldf files on my laptop from c:\program files\microsoft sql server\mssql.2\mssql\data\ to c:\program files\microsoft sql server\mssql.2\mssql\ and used the SQL Server Configuration Manager to change the startup parameters to point to the new path for the master database.

Now if you read the BOL Entry, there is an additional step required to to change the path to the Resource database.  I intentionally left this step off to demonstrate how to fix the error should you make this mistake.  When I attempt to start SQL Server after the move, I get the following in the ErrorLog:

Starting up database 'master'.
Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
CHECKDB for database 'master' finished without errors on 2008-05-10 01:30:00.483 (local time). This is an informational message only; no user action is required.
SQL Trace ID 1 was started by login "sa".
Starting up database 'mssqlsystemresource'.
Error: 17207, Severity: 16, State: 1.
FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.ldf" 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.

To fix this, we have to first drop to command prompt again, by running cmd in the Run box:

Then change directories to the Binn directory under your SQL Instances path:

Then run sqlservr with -f and trace flag 3608 as startup parameters which will start SQL Server in minimal configuration mode and prevent recovery of every database except master.  One note is that the -T3608 is case sensitive.

image

NOTE: Do not use any other startup parameters or Trace Flags as these can cause SQL to fail to start.

When you start SQL Server from the command prompt it will spool the log information out to the command prompt screen. When it shows Recovery is complete the SQL Server Instance is running in single user mode and can be connected to through SSMS or sqlcmd.

image

Once you connect you can then run the correct ALTER DATABASE scripts to fix the Resource Database path.

ALTER DATABASE mssqlsystemresource 
    MODIFY
FILE (NAME=data, FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource 
    MODIFY
FILE (NAME=LOG, FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\mssqlsystemresource.ldf');
GO

Once this has been run, you can close the SQL Server Instance running in the command prompt by pressing Ctrl+C with the window active.  Then restart the SQL Service from the Services.msc snapin or the Computer Management Console and you should be back in business.

Stay tuned as I have a few more intentional crashes that I plan to demonstrate and fix in the next few weeks.

Edit Note:  This should not be an issue in SQL Server 2008 since you can not move the Resource Database as per http://msdn.microsoft.com/en-us/library/ms143359.aspx. However, a reference entry in the BOL regarding what you can't do doesn't stop people from making this mistake.  In the case of SQL Server 2008, you would have to move the Resource database files back to the <Instance Path>\MSSQL\Binn folder where it belongs.

- Thanks to noeldr for pointing this out in comments.


Published Thursday, January 22, 2009 8:21 AM by Jonathan Kehayias
Filed under:

Comments

 

noeldr said:

In SQL 2008 you CAN'T move the resource database!

http://msdn.microsoft.com/en-us/library/ms143359.aspx

Here is the relevant part:

In SQL Server 2008, the default location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. The Resource database cannot be moved.

January 22, 2009 12:19 PM
 

Brett Flippin said:

You also have to make sure to move the mssqlsystemresource.ldf to the same directory as master.mdf and mssqlsystemresource.mdf or you'll throw errors every time you try to install any SQL Server updates or service packs.

January 22, 2009 12:35 PM
 

Naveen malhotra said:

Many thanks Jonathan,

You saved my day.

February 16, 2009 8:07 AM
 

David Benoit said:

Yes, thank you. This was totally what I needed this morning. Had to restore master to another server to get some stored procedures off of it after we had migrated to a new server. Many fun things with that but this really helped a ton. Thanks!

May 19, 2009 11:31 AM
 

Steven said:

many thanks Jonathan, this is exactly what I'm looking for but I have a problem after moving the master database and restarting in single-user mode.

When I try to launch sqlcmd or SMSS I receive the message that only 1 administrator can log in at a time as the database is in single-user mode.  I can't therefore, change the location of the resource database so end up having to move the master database back to its original location (which thereafter works perfectly fine).

I have physically isolated and rebooted the server in question prior to performing any of this to ensure that no one else can get logged onto it.

I am logging onto the SQL Server using Windows authentication as a Domain Admin

Running sp_who against the live server, immediately after starting SMSS, shows the following users:

sa

(my_company_Name)\administrator

(my_company_Name)\SQL

The SQL login is in the ReportServer and msdb databases in an "Awaiting Command" state.  

I don't know enough, unfortunately, to know whether or not the start of the server in single-user mode is still kicking something off that then brings these other users into play, and is thus the cause of my problems, and how I avoid this.

Any ideas/assistance would be greatly appreciated.

Kind Regards

Steven

September 2, 2009 8:03 AM
 

Jonathan Kehayias said:

Steven,

You need to shutdown Reporting Services, SQL Agent (should be down since SQL is down already but just in case), Integration Services when you do this.  Then when you connect, if you use SSMS, don't let the Object Explorer Window connect to the server, or you won't be able to connect a query window.

There is something else concerning here, and that is the high level access you are using to work with SQL.  It would seem to me that you have a service logging on with the Domain Administrator account which is really bad.

September 2, 2009 9:48 AM
 

Steven said:

Jonathan

Thanks for your response.  I take your point regarding the Domain Admin role.  I only logged on with the Domain Admin account this morning to take all security issues out of the loop.  This isn't absolutely isn't something we normally do.

I'll try the steps you mentioned and let you know how I got on.

Thanks again

Steven

September 2, 2009 11:31 AM
 

Steven said:

Jonathan,

All working perfectly now.  Many many thanks for your invaluable help.

Cheers

Steven

September 2, 2009 11:59 AM
 

Roni Vered said:

Thank you very much for your post.

I encountered the same issue while migrating SQL SERVER 2005 from one server to another, that contained different Disk structure (Installation drive was C in the new server, while the Original installation drive was D).

I wish that I would have seen this post earlier, because we overcome this problem by Allocation Storage Space as D drive to the server and copying the mdf,ldf file to the same directory tree as it was originated from.

After we started the server we modified the Database file location with the above command to C, we finished the issue.

10x anyway :)

October 20, 2010 5:27 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement