Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Desperate for help, but fearing the worst here :(

Something crashed SQLSERVER 2005 this afternoon approx 3pm. Since then I have been trying to get running again.

Original log file messages told that master log file was corrupt. I had no recent backup so used the following command;

start /wait \setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=

Which seems to get me further down the road to getting SQL server started, but I now get the following message in the log;

2014-08-0818:35:14.96 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
...
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
2014-08-0818:35:14.96 Server Registry startup parameters:
2014-08-0818:35:14.96 Server -d R:\MSSQL.1\MSSQL\DATA\master.mdf
2014-08-0818:35:14.96 Server -e R:\MSSQL.1\MSSQL\LOG\ERRORLOG
2014-08-0818:35:14.96 Server -l R:\MSSQL.1\MSSQL\DATA\mastlog.ldf
...
2014-08-0818:35:18.09 spid5s Starting up database 'mssqlsystemresource'.
2014-08-0818:35:18.09 spid5s Error: 17207, Severity: 16, State: 1.
2014-08-0818:35:18.09 spid5s FCB::Open: Operating system error 3(error not found) occurred while creating or opening file 'R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
2014-08-0818:35:18.09 spid5s Error: 17204, Severity: 16, State: 1.
2014-08-0818:35:18.09 spid5s FCB::Open failed: Could not open file R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf for file number 1. OS error: 3(error not found).
2014-08-0818:35:18.10 spid5s Error: 5120, Severity: 16, State: 101.
2014-08-0818:35:18.10 spid5s Unable to open the physical file "R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf". Operating system error 3: "3(error not found)".
2014-08-0818:35:18.12 spid5s Error: 17207, Severity: 16, State: 1. 2014-08-0818:35:18.12 spid5s FileMgr::StartLogFiles: Operating system error 2(error not found) occurred while creating or opening file 'R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
2014-08-0818:35:18.12 spid5s File activation failure. The physical file name "R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf" may be incorrect.
2014-08-0818:35:18.14 spid5s Error: 945, Severity: 14, State: 2.
2014-08-0818:35:18.14 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2014-08-0818:35:18.14 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

There is no indication in event log what the problem is. I tried copying an older version of mssqlsystemresource.mdf & ldf to no avail.

Any help greatly appreciated as I cannot find anything more on the net.

share|improve this question
    
What happened to R:\aSQL\? Is that a mount point? If so, does it still exist? –  James Aug 22 at 12:40

2 Answers 2

up vote 3 down vote accepted

Error message says mssqlsystemresource is not found.,you can try copying resource database mdf and ldf to its place from another sql server instance.

Source version from which you will copy resource database mdf and ldf should be same as destination version

Below link may help you in knowing how to check resource database version and location of resource database http://blog.sqlauthority.com/2010/01/31/sql-server-location-of-resource-database-in-sql-server-editions/

always its good to find out why all of a sudden this happened and add these files to as part of DR solution.

share|improve this answer

The easiest solution would be to install a new instance of SQL Server (preferably one newer than 2005 RTM, which isn't supported anymore). If you have recent and valid backups:

  • restore your databases on the new instance.

If you don't have recent and valid backups:

  • copy over the other MDF/LDF files, attach them, cross your fingers and say 30 hail marys and hope they're not corrupt too.
  • before you do anything else, set up a proper backup / restore plan. If you get the R: drive reinstated, I highly recommend not putting the backups there.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.