May 23, 2010 at 10:20 am
After failed instance of SP2 we cannot restart the INSTANCE.
How to get back the mssqlsystemresource.ldf file and mssqlsystemresource.mdf
2010-05-23 08:58:27.61 spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2010-05-23 08:58:27.61 spid5s Log file 'U:\SQL_Logs\MSSQL10\Data\mssqlsystemresource.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
2010-05-23 08:58:27.61 spid5s The log cannot be rebuilt when the primary file is read-only.
2010-05-23 08:58:27.61 spid5s Error: 945, Severity: 14, State: 2.
2010-05-23 08:58:27.61 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2010-05-23 08:58:27.61 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
May 23, 2010 at 10:57 am
Resolved.
May 23, 2010 at 11:24 am
for the benefit of others who might get the same problem ,how?
---------------------------------------------------------------------
May 26, 2010 at 10:35 am
Please share the resolution.
MJ
June 5, 2010 at 7:03 pm
The service pack 2 put the mssqlsystemresource1.mdf files in the default directory in our case P Drive. So I managed to move the mssqlsystemresource1.mdf files to the U Drive and remove the original mssqlsystemresource.mdf files and then rename the mssqlsystemresource1.mdf files to mssqlsystemresource.mdf. Worked for me.
SP3 However was not so good..this was installed with sql running and not selecting the instances correctly. This resulted in sys.endpoints error and having to restore the master file...after restoring the master file it did not fix the error. The SP3 is a higher version and even restoring master file to its SP2 version still errored. Please ensure you shutdown SQL Services if running two instances of SQL on the same server. If you have one instance SP3 works fine.
This caused us to resintall the entire SQL Server on another server, copy files, restore master, to temp sql server extract logins and restore msdb to temp to get jobs. Please ensure you test this to save you the time in setting up a brand new sql server. Please make sure you have copies of all system files before installing SP3.
Thanks
June 6, 2010 at 8:39 am
thanks for posting back tracey.
sounds like you fell foul of the old gotcha of not having the resource database with your master database.
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply