SQL Server Tips

Rebuild SQL Server system database

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 :

rebuild-system-db-command

Once the above command running successfully, check summary.txt file inside C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log directory  :

rebuild-summary

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 :

restore-master-database

Open SQL Server Configuration Manager and start the SQL Server Service. Everything should be OK now.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s