This blog is a result of a Skype conversation with one of my clients. The issue, for which they contacted me, was that they were not able to stat SQL Service due to database may not be activated. I asked to share SQL Server ERRORLOG files using below blog SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
Here is the information which I saw.
2017-04-02 16:38:02.40 spid11s Error: 913, Severity: 16, State: 6.
2017-04-02 16:38:02.40 spid11s Could not find database ID 3. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.
The above message is scary because it says that it can’t fine Database ID = 3. If you look at documentation, it is the ID for model database. We know that model is a system database which is needed for SQL to start and any issues with this database would cause SQL startup failures. In my case, the client didn’t know how it was detached. Now, the next step is what we should do? There were NO backups available. We had MDF and LDF files. So, I tried below.
Solution / Workaround:
- I started the SQL Instance using trace flag 3608, which is documented in books online. Then we tried to attach the model database and got the below error:
Create Database failed
Error: 33401, Severity: 16, FILESTREAM database options cannot be set on system databases such as model.
- We tried picking model database files from different servers, but no luck.
- Since there was no backup, we were not able to try to restore.
Finally, I have asked them to rebuild the system databases by keeping the copy of MDF and LDF files for all databases as a backup.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=”BUILTIN\Administrators” /SAPWD= AVeryStrongP@$$word123 /SQLCOLLATION= “SQL_Latin1_General_CP1_CI_AS”
Once above command was finished, it was as good as fresh install of SQL Server. Later we attached all user databases and created all system objects.
Reference: Pinal Dave (http://blog.SQLAuthority.com)