Recently one of our customer database server becoming very noisy and without my knowledge, they restarted that server. After restart, that server wasn’t noisy again but they having trouble cannot login to the application. I’ve found out that SQL Server service won’t started and found the following error in event viewer :
The SQL Server (MSSQLSERVER) service terminated with the following service-specific error: WARNING: You have until SQL Server (MSSQLSERVER) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data.
When I checked SQL Server ERRORLOG, I’ve found the following error :
Error: 9003, Severity: 20, State: 9. The log scan number (25500:21:0) 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. Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online. SQL Server shutdown has been initiated
The above error said that SQL server cannot recover the master database. And I need to restore master from full backup, repair it or I have to rebuild it. Because I was regularly take backup of master database then I want to restore it from full backup, but when I tried to start SQL Server in single user mode, I got same.
The only thing I have to do now is to rebuild master database using the following steps :
Open command prompt (Run as Administrator) and go to C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\.
Run rebuild command as instruction from msdn site :
Once the above command running successfully, check summary.txt file inside C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log directory :
Next thing I have to do is restore master database from backup file. Start SQL Server in single-user mode and restore database from sqlcmd utility :
Open SQL Server Configuration Manager and start the SQL Server Service. Everything should be OK now.