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.
Best Answer
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.