Hi Friends,

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.

1_SQL_Server_Trace_FLag_3608_you_might_face_this

I will now manually start SQL Server from command prompt:

2_SQL_Server_Trace_FLag_3608_you_might_face_this

And if we observe, the output shows that model and tempdb databases are started:

3_SQL_Server_Trace_FLag_3608_you_might_face_this

If we open the error log and verify, we will see that, indeed, model and tempdb were started.

4_SQL_Server_Trace_FLag_3608_you_might_face_this

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
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Connect on LinkedIn | Google+
FB page | Know more about me

SQL Server Training, BA QA, Gurgaon, June 2014
SQL Server Training, SQL 2012 Advanced Administration, Coimbatore June 2014