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

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

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

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

  • 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

  • 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

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

    🙂

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

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

  • 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

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

  • 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

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

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