servererror

If you are coming to this page by searching, then I am sure that you are in big trouble. I have come across this error and my SQL Server was not getting started. When I looked into ERRORLOG, I found below errors about the PageAudit Property.

2016-12-30 15:45:36.36 spid9s Starting up database ‘model’.

2016-12-30 15:45:36.37 spid9s Error: 5172, Severity: 16, State: 15.

2016-12-30 15:45:36.37 spid9s The header for file
‘C:Program FilesMicrosoft SQL ServerMSSQL12.DAR_P11DMSSQLDATAmodel.mdf’ is not a valid database file header.
The PageAudit property is incorrect.

2016-12-30 15:45:36.39 spid9s Error: 945, Severity: 14, State: 2.

2016-12-30 15:45:36.39 spid9s Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space.
See the SQL Server errorlog for details.

2016-12-30 15:45:36.39 spid9s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’.
This is an informational message only; no user action is required.

So, since the model database was having serious corruption, SQL was not getting started. The error message essentially means that file header has been corrupted and SQL Server is not able to understand it.

 

WORKAROUND/SOLUTION

In my case since it was model database, there were two possible options.

  1. Get MDF and LDF file from some other server which has “exactly” same version of SQL Server. This would cause to lose data from the previous model database and will get it from another server.
  2. If we have a backup of the model and we wish to restore it first, then we need to rebuild system databases using the setup.exe command line. Before rebuilding, we need to keep a copy of MSDB and MASTER database files. As soon as we rebuild, all system database would be as fresh as new installations. After rebuilding, we can restore backups and bring SQL back to life.

After I followed option 1, SQL Server came back online, but SQL Agent was giving trouble. I looked into SQLAgent.out file and here was the information.

2016-12-30 17:04:25 – ? [100] Microsoft SQLServerAgent version 12.0.2269.0 (X64 unicode retail build) : Process ID 83880

2016-12-30 17:04:25 – ? [495] The SQL Server Agent startup service account is NT ServiceSQLAgent$SAPSQL.

2016-12-30 17:04:25 – ? [393] Waiting for SQL Server to recover database ‘msdb’…

2016-12-30 17:04:25 – ? [101] SQL Server version 12.00.2269 (0 connection limit)

2016-12-30 17:04:25 – ? [102] SQL Server ODBC driver version 11.00.2100

2016-12-30 17:04:25 – ? [103] NetLib being used by driver is DBNETLIB; Local host server is CLD-SQL01MIMECAST_MSO

2016-12-30 17:04:25 – ? [310] 8 processor(s) and 17970 MB RAM detected

2016-12-30 17:04:25 – ? [339] Local computer is CLD-SQL01 running Windows NT 6.2 (9200)

2016-12-30 17:04:25 – ! [000] SQLServerAgent cannot start because the msdb database is not available for normal access

2016-12-30 17:04:25 – ? [000] Configuration option ‘show advanced options’ changed from 0 to 1.
Run the RECONFIGURE statement to install.
[SQLSTATE 01000] (Message 15457) Configuration option ‘Agent XPs’ changed from 1 to 0.
Run the RECONFIGURE statement to install.
[SQLSTATE 01000] (Message 15457) Configuration option ‘show advanced options’ changed from 1 to 0.
Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)

2016-12-30 17:04:25 – ? [098] SQLServerAgent terminated (normally)

If you read through above lines, it is easy to identify that below is the cause:

SQLServerAgent cannot start because the msdb database is not available for normal access

When I connected to SQL Server, I found that MSDB database was in suspect state. So, I need to restore that from the backup.

If you ever face error mentioned in a blog title, restore from backup is the only option left. If you find some other solution, please share via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)

First appeared on

 

About the Author

Pinal Dave

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Start the discussion at forums.toadworld.com