SQL Server: Trace Flag 3608 - You might encounter this!

SQL Server: Trace Flag 3608 – You might encounter this

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.

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!

By Amit Bansal|2020-09-04T12:26:12+05:30July 23rd, 2014|SQL Server|1 Comment

About the Author: Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: https://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

One Comment

Thomas December 22, 2014 at 10:06 pm - Reply
Hi Amit,
This is a great post and I’m wondering if it answers a question I have regarding trace flag 3608. I’ve been practising emergency SQL Server procedures, which involves starting SQL Server in master-only recovery mode (T3608)
But when I connect to the server and run USE tempdb, it is available! This is not what I would have expected. So, is it the case that tempdb gets started/recovered because I am issuing the USE command? And if I had not issued the command, tempdb would stay “un-recovered”?
Any help would be greatly appreciated.
Regards,
Tom

SQL Server: Trace Flag 3608 – You might encounter this