SQL Server won't start - can't find mssqlsystemresource.mdf

Question

SQL Server won't start - can't find mssqlsystemresource.mdf

jamie_collins

Ten Centuries

Points: 1192
More actions
May 24, 2009 at 1:35 pm

#131530

Moved system db's to new drive, moved systemeresource files without doing alter database command. Now server can't start stating can't find the files in the original drive. Try copying back but I get the file in use error... I stop everything in Config manager but still no luck. Any ideas?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 1

Try to use this post from Jonathan and see if it helps.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/22/error-945-severity-14-state-2-database-mssqlsystemresource-cannot-be-opened.aspx

*Caution*: use at your own risk and I am NOT an expert DBA.

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

jamie_collins Ten Centuries Points: 1192 More actions · Answer 2

OK. Finally solved it...

Numerous posts explain that you can bring up the server with the 'f' switch. What this does is only brings up master database and the other db's will be brought online when accessed... All of the blogs then specify to use Management Studio to go in and run your alter database commands... Only problem with that is this attempts to access the systemresource database and can't because the files aren't in the proper directory anymore... My main problem was I had lost access to the network drive they were originally installed in, hence I couldn't just copy them back and then bring the server back online. I ended up using the sqlcmd utility to run the alters and the server is now up and running.

ally_r Right there with Babe Points: 723 More actions · Answer 3

Glad to hear you got your problem sorted. If I understood it correctly you could have changed the SQLS Server Service startup parameters within SQL Server Configuration Manager and pointed to the new system DB directories. See below

Cheers

Ally

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 4

Ally, no - the problem here was that Jamie was having a problem accessing the system resource database which is not referenced anywhere in startup procedures. The only way to define where that database resides in using ALTER DATABASE.

So, of course - they changed that whole thing in 2008 and you can't have this database anywhere other than in the same directory as the master database. At least, that is my understanding.

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

ally_r Right there with Babe Points: 723 More actions · Answer 5

Fair enough, guess I didn't read that properly. Not that I new that...

🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jeffrey Williams (5/24/2009)
So, of course - they changed that whole thing in 2008 and you can't have this database anywhere other than in the same directory as the master database. At least, that is my understanding.

In 2008 the system resource DB is treated as if it were a dll. It's stored in the BINN directory under the SQL installation directory, regardless of where master is, and it cannot be moved without breaking everything.

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

jamie_collins Ten Centuries Points: 1192 More actions · Answer 7

Right. The catch 22 was that I could bring up the server in 'maintenance mode' but as soon as I start SSMS the resource db is accessed which of course isn't where it's supposed to be. Glad to have the restrictions for that db placed in 2008. My headaches will go away. Well at least for that issue...

Rao.V Hall of Fame Points: 3452 More actions · Answer 8

GilaMonster (5/24/2009)
Jeffrey Williams (5/24/2009)
So, of course - they changed that whole thing in 2008 and you can't have this database anywhere other than in the same directory as the master database. At least, that is my understanding.
In 2008 the system resource DB is treated as if it were a dll. It's stored in the BINN directory under the SQL installation directory, regardless of where master is, and it cannot be moved without breaking everything.

hi, i have moved master db to another location and restarted services and everything is fine but aftr restrtng srvr issue strtd with mssqlsystemresource db, i forgot abt it.. now wen i search for the mdf/ldf files of resource db in stndrd location im not finding those files and nw sql is not starting.. any suggestions??? how to bring back systemresource db..??

version is sql server 2k8 r2 developer edition..

im able to start sql server with /f /T3608 optons but not able to login and not able to find the systemresource db mdf/ldf files.. any help???

EdVassie SSC Guru Points: 60478 More actions · Answer 9

My advice: Put Master back to its original location and leave it there.

There are no data integrity or performance advantages in moving master or modfel DBs. All you do is introduce risk into your system. The fact that you are having a problem after moving master is one example of this risk.

Microsoft have a long track record of various SPs, CUs and version upgrades failing if the master and system resource DBs are not in their default locations. Although to my knowlege this has not happened recently, it is a brave person who assumes this will not happen again.

The simplest way to avoid these risks is to do nothing - leave master where the SQL install put it. If you have a site standard about database file locations, then add a clause about master and model remaining in their original locations.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Rao.V Hall of Fame Points: 3452 More actions · Answer 10

EdVassie (9/26/2011)
My advice: Put Master back to its original location and leave it there.
There are no data integrity or performance advantages in moving master or modfel DBs. All you do is introduce risk into your system. The fact that you are having a problem after moving master is one example of this risk.
Microsoft have a long track record of various SPs, CUs and version upgrades failing if the master and system resource DBs are not in their default locations. Although to my knowlege this has not happened recently, it is a brave person who assumes this will not happen again.
The simplest way to avoid these risks is to do nothing - leave master where the SQL install put it. If you have a site standard about database file locations, then add a clause about master and model remaining in their original locations.

i do take ur advice to account but here not sure wat happened but when i installed another instance of sql server in the same server then i am able to see the missing mssqlsystermresource mdf/ldf files where they shud be and now the server is running fine without any issue... not sure wat fixed the issue though... the named instance installed recently also has its own system resource files.

EdVassie SSC Guru Points: 60478 More actions · Answer 11

All SQL instances will have their own master and resource databases. Both databases are updated when you apply a SP or CU, and need to be at the same fix level in order for SQL Server to work correctly.

Because you are allowed to have multiple instances of SQL Server on the same Windows instance with each SQL instance potentially having a different version or fix level, then each instance must have its own copy of these databases.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara