Sql server went offline because of master DB

Question

Sql server went offline because of master DB

sandhyarao49

SSCrazy

Points: 2215
More actions
November 15, 2010 at 6:35 am

#241232

Hi,
Yesterday my test machine had an unexpected reeboots and because of this my test sql server went offline. I tried to start the services but it failed with the below error.
-------------
The log scan number (518:184:2) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
---------------------
Can any one suggest how to fix it.
Regards,
sandhya

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Restore your latest backup of master. Refer to Books Online for the proper procedure, it's not as simple as restoring as user database.

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

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 2

Hi

Thanks for the reply.

Seems the database has been corrupted.

Can you please help me to understand why the database has been corrupted.

I will try to rebuild the master and restore it from previous backup.

Waiting for your reply.

Regards,

Sandhya

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sandhyarao49 (11/15/2010)
Seems the database has been corrupted.
Can you please help me to understand why the database has been corrupted.

Yup. Probably something to do with the unexpected reboot. Likely something in the IO subsystem isn't guaranteeing IO writes hardened on disk as it should be. Check that if the disks have write-cache enabled the write-cache is batter-backed.

I will try to rebuild the master and restore it from previous backup.

Should work. You have a recent backup of master then?

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

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 4

Hi,

I find difficulty in rebuilding the master DB for the default instance of the test machine.

So i have overwriteen the master DB files with the same version and build of some other test machine and restored the master from backup.

can you kindly let me know is this process correct as a workaround for the rebuilding Master DB.

Regards,

Sandhya

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

If you have restored master from backup, you should not need to rebuild it.

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 6

Hi,

I have rebuilted the master and it worked fine.

For some reason we have rebooted the machine and now the server fails to start and below is the error message.

-------------

"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."

--

Kindly throw some light in this regard so that i can fix it.

Regards,

Sandhya

steveb. SSC-Forever Points: 46733 More actions · Answer 7

did you restore master from a backup of the db server you are working on? or is it a backup from another server?

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 8

Initially i find difficulty in rebuilding the master Db so i followed the below steps.

1. Overwriten the master DB files from another server which has the same build and version.

2. Restored the master from the backup of the corrupted server.

After that every thing worked perfectly.

Rebooted the machine as it a test machine .

Now when i tried to access the server, it went to offline state.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

sandhyarao49 (11/15/2010)
"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."

That's not an error, it's a warning and it will not cause the sQL service to fail. Look in the error logs, there's something else in there that's the actual error.

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