According to SQL Server documentation, if SQL Server is started with Trace Flag 3608, it will not start and recover any database except the MASTER system database. So none of the system databases; msdb, tempdb, model, etc will start or recover if this trace flag is used while starting SQL Server.
One of our customers faced an issue where they saw that despite using the trace flag 3608, tempdb & model system databases were started and the error logs also showed that master, tempdb & model databases were started. Well, this is not how Trace Flag 3608 is supposed to behave.
Let us reproduce this behavior.
I am using SQL Server 2012 SP1 for this demo. In the image below, you can see that SQL Server service is not running.
I will now manually start SQL Server from command prompt:
And if we observe, the output shows that model and tempdb databases are started:
If we open the error log and verify, we will see that, indeed, model and tempdb were started.
What’s going on?
Well, when I was scrolling down and investigating the error log, I saw that Report Server databases were also started. Now this tipped me off that it could possibly because of Reporting Services running on the same box. I guessed that Reporting Services service, which was running all this while, probably, wanted to access these databases and that’s why they were started and recovered. Now this makes sense since the documentation also says that if Trace Flag 3608 is used, other databases may be started and recovered if they are accessed.
My guess was right.
I stop the Reporting Services service.
Start sqlservr.exe with the trace flag 3608.
And this time, model and tempdb databases are not started.
You might encounter this so it’s good to be aware!
Want to watch this in action? Watch here!
Amit Bansal (SQL MCM, SQL MVP) | Twitter | FaceBook | LinkedIn | FB Page|Ask a SQL Question Browse more blogs on SQLServerGeeks.com