mssqlsystemresource.mdf corruption

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

mssqlsystemresource.mdf corruption

Author

Topic

Jake_UK
Starting Member

7 Posts

Posted - 2011-11-08 : 13:47:41

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 <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

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

"
2011-11-08 18:35:14.96 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

2011-11-08 18:35:14.96 Server (c) 2005 Microsoft Corporation.
2011-11-08 18:35:14.96 Server All rights reserved.
2011-11-08 18:35:14.96 Server Server process ID is 5864.
2011-11-08 18:35:14.96 Server Logging SQL Server messages in file 'R:\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-11-08 18:35:14.96 Server This instance of SQL Server last reported using a process ID of 2540 at 11/8/2011 18:23:06 (local) 11/8/2011 18:23:06 (UTC). This is an informational message only; no user action is required.
2011-11-08 18:35:14.96 Server Registry startup parameters:
2011-11-08 18:35:14.96 Server -d R:\MSSQL.1\MSSQL\DATA\master.mdf
2011-11-08 18:35:14.96 Server -e R:\MSSQL.1\MSSQL\LOG\ERRORLOG
2011-11-08 18:35:14.96 Server -l R:\MSSQL.1\MSSQL\DATA\mastlog.ldf
2011-11-08 18:35:14.98 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-11-08 18:35:14.98 Server Detected 2 CPUs. This is an informational message; no user action is required.
2011-11-08 18:35:15.70 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2011-11-08 18:35:15.71 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2011-11-08 18:35:17.75 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2011-11-08 18:35:17.75 Server Database Mirroring Transport is disabled in the endpoint configuration.
2011-11-08 18:35:17.75 spid5s Starting up database 'master'.
2011-11-08 18:35:18.06 spid5s SQL Trace ID 1 was started by login "sa".
2011-11-08 18:35:18.09 spid5s Starting up database 'mssqlsystemresource'.
2011-11-08 18:35:18.09 spid5s Error: 17207, Severity: 16, State: 1.
2011-11-08 18: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.
2011-11-08 18:35:18.09 spid5s Error: 17204, Severity: 16, State: 1.
2011-11-08 18: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).
2011-11-08 18:35:18.10 spid5s Error: 5120, Severity: 16, State: 101.
2011-11-08 18: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)".
2011-11-08 18:35:18.12 spid5s Error: 17207, Severity: 16, State: 1.
2011-11-08 18: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.
2011-11-08 18:35:18.12 spid5s File activation failure. The physical file name "R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf" may be incorrect.
2011-11-08 18:35:18.14 spid5s Error: 945, Severity: 14, State: 2.
2011-11-08 18: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.
2011-11-08 18: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.

tia

Kristen
Test

22859 Posts

Posted - 2011-11-08 : 14:18:22

Is the file available on THAT path?

R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf

Maybe your sort-out of MASTER has set it to expect the DB to be in a non-standard location.

"Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space"

"inaccessible files" - something else got them open? Reboot might clear that.

"insufficient memory" Reisntall may have reset/changed the amount of memory that SQL can acquire

"disk space" Check you are not approaching Disk Full on any drives that SQL might be wanting to use.

"I tried copying an older version of mssqlsystemresource.mdf & ldf to no avail"

That doesn't sound good - unless the COPY you made was with the SQL SERVICE stopped. Do you have a Backup file? that's much more likely to be usable than a file-copy (unless SQL Service was stopped when you made the COPY; if it was then go through the process of ATTACHing the files, and NOT just copying them back into their original location.)

Hopefully Gail will pop-up here soon, then you'll get some proper advice

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-08 : 15:44:16

Operating system error 3 is directory not found (as far as I recall)

Does R:\aSQL\MSSQL.1\MSSQL\DATA\ exist?
It's different from the path for master...

--
Gail Shaw
SQL Server MVP

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2014-08-22 : 07:28:50

Such situation occurs in SQL Server database which can not be resolve without using any external tool. So in this case you must go with reliable and well implemented tool which gives expected results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources