Another email from one of my blog reader, which made me think about the incorrect information which is available on the internet.
Hi Pinal,
I have corruption in model database and due to that SQL Service is not able to start. Here are the messages in SQL ERRORLOG
2015-09-23 21:45:07.00 spid11s    Srting up database ‘model’.
2015-09-23 21:45:07.45 spid11s    Error: 5125, Severity: 24, State: 2.
2015-09-23 21:45:07.45 spid11s    File ‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\model.mdf’ appears to have been truncated by the operating system. Expected size is 4288 KB but actual size is 4280 KB.
2015-09-23 21:45:07.49 spid11s    SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
What should I do? I have backup of the model database and want to restore it. I have spoken to many folks and I was getting different answers. Can you please provide me right direction?
Thanks,
<Name Hidden>
In this case problem is straight forward, restore backup of model when current model is corrupted and SQL is not starting due to that. If we search on internet, we get below incorrect hints:
- Start SQL with trace flag 3608. To do that Use this from command prompt: sqlservr.exe -T3608
- Start SQL Server via command prompt using sqlservr.exe -c –m
To test above, I renamed the database files of model database and then tried restore after started via trace flag 3608. Here is the message which I received during restore.
Shared Memory Provider: The pipe has been ended.
Communication link failure
Restoring model is not a big deal, it doesn’t need any special switch or startup parameter. We can use T-SQL query or Management Studio UI to restore model.
Our problem above is that SQL is not getting started because model is in bad state. Below are the working solutions.
Hopefully, this flow chart would help in getting exact steps needed. Â Model can be restored directly via T-SQL or SSMS.
Have you ever found incorrect information on the internet? Never trust information from untrusted sources.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
Gail Shaw has an extensive post on this.
If your SQL Server 2014 can’t start and show error of “model.mdf” then do the following steps:
1) Copy “model.mdf” and “modellog.ldf” files form “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Binn”
2) Paste\overwrite those copied file inside “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA”
3) Restart SQL server 2014
Sanket, your solution worked 100%
Many thanks
Awesome It’s successfully
CMD command for rebuilding your system databases:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
Source:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2017
Hi sir i have also faced same issue i resolved this by copy paste from
“C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\”
I generally don’t use that folder as its for rebuilding purpose.
Hi Pinal
This post has saved me..
I was getting below error while starting SQL SERVER services.
2018-07-01 18:39:32.96 spid6s [INFO] HkHostDbCtxt::Initialize(): Database ID: [3] ‘model’. XTP Engine version is 0.0.
2018-07-01 18:39:32.96 spid6s Starting up database ‘model’.
2018-07-01 18:39:32.98 spid6s [INFO] HkHostDbCtxt::Initialize(): Database ID: [3] ‘model’. XTP Engine version is 0.0.
2018-07-01 18:39:33.50 spid17s [INFO] HkHostDbCtxt::Initialize(): Database ID: [7] ‘SACC LIVE’. XTP Engine version is 0.0.
2018-07-01 18:39:33.57 spid6s [INFO] HkHostDbCtxt::Initialize(): Database ID: [3] ‘model’. XTP Engine version is 0.0.
2018-07-01 18:39:33.59 spid6s Error: 9003, Severity: 20, State: 1.
2018-07-01 18:39:33.59 spid6s The log scan number (33:1888:1) passed to log scan in database ‘model’ 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.
2018-07-01 18:39:33.60 spid6s SQL Server shutdown has been initiated
2018-07-01 18:39:33.60 spid6s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
I replaced model LDF and MDF file from working server and was able to start service.
Great Thanks.