I need to start this blog post with a disclaimer: you should not be doing this. Hell, *I* should not be doing this.

But when SQL Server 2017 came out Monday, I noticed a few unusual SNAPSHOT_MATERIALIZATION entries in sys.messages and a few related undocumented system objects that hinted about a new feature Microsoft hadn’t announced yet. I played around with it for a while, couldn’t get it to work, so I posted a question on DBA.se: How do you create a view with SNAPSHOT_MATERIALIZATION? Sure enough, within minutes, Paul White had his debugger going, and he’d run the world’s first demo of that feature. Head on over to that Stack question if you want a sneak preview at what the feature does, and give the guy an upvote while you’re there. He worked hard on that one.

For the next step, I wanted to see if folks could play with it without using a debugger. I had a hunch that the feature might be turned on via trace flag, so I started running experiments. Like Edison would say, I haven’t failed yet – I’ve just found 2,289 ways (as of this morning) it won’t work. But just running the experiments are a hell of a lot of fun, and I figure that you, dear reader, might enjoy seeing what goes on in the labs here at Brent Ozar Unlimited when nobody’s looking.

Now, On with the Bad Idea Jeans

Let’s say, just theoretically, that a new version of SQL Server came out, and it had a new undocumented stored procedure that failed with an error message saying it was disabled.

The first thing you might try is looping through trace flags, one at a time, in pseudocode like this:

In our theoretical case here, none of the trace flags enabled our theoretically hidden stored procedure. However, sometimes trace flags have to be enabled at startup, not just at runtime.

Testing Undocumented Startup Trace Flags

The DBCC TRACEON command works when the SQL Server is started up, but that’s too late to set some trace flags. Here’s the tools we’re going to use:

• Startup stored procedure – so that as soon as the SQL Server engine starts, it runs a stored procedure to test our trace flag, log progress, set the next trace flag, and restart.
• xp_readerrorlog – undocumented stored proc to read the error log. Here, I’m using it to dump the log into a table each time SQL Server starts up with a different trace flag. That way, I can analyze the log strings later to look for unusual outputs. More on that in a second.
• xp_regwrite – undocumented stored procedure to set trace flags in the registry so that they run at startup. I don’t do this often, needless to say, so I started with this SQLServerCentral post’s code.
• xp_cmdshell calling shutdown – I wish I could just reliably restart the SQL Server service from inside T-SQL, but I had some unreliable results with my stored procs rolling back when I tried the net start commands to restart the service from inside a proc. I could have also done this with an Agent job doing a waitfor for maybe 60 seconds and then restart, but I wanted very fast restarts, since I gotta do ~500 of them.

I gotta be honest with you, dear reader: any one of those above things is generally a pair of Bad Idea Jeans in and of itself, but we’re gonna layer up. (Not lawyer up, although that’d probably be a good idea too, as heinous as this script is.)

Add those all together, and you get this hot mess. Seriously, you shouldn’t ever run this on a server you care about.

The Payoff: Startup Error Logs by Trace Flag

Each time SQL Server starts up, I’m dumping the error logs into a table. Then, I can go back later to sort through the log messages that each trace flag produced. In the below screenshot:

• TraceFlag – the flag that was on when the SQL Server started up
• Text – an unusual error log message that wasn’t present by default, or wasn’t produced by too many other trace flags

So in that screenshot, we’ve discovered a few things:

• Trace flag 9706 says “Software Usage Metrics is disabled.”
• Trace flag 9850 dumps more diagnostic stuff in the log.
• Trace flag 10264 says “Polybase mode enabled for SqlComposable.”
• And a bunch of trace flags have been deprecated (but you probably weren’t using those anyway)

Should We Use These Trace Flags?

Of course not! That would be really, really stupid. We have no idea what behavior these trace flags really trigger under the covers. For all I know, one of them could report “Flintstones Chewable Data Pages Enabled” when it really runs auto-shrink every ten seconds.

However, when my poor VM has worked its way through all of the trace flags – started at 11498, now down to 9216 – I’ll post the full error log list in a backup so you folks can go spelunking through it. I’d rather have you do that than beat the hell out of some poor SQL Server VM.

This party won’t last long, either. Going forward, given that lots of folks at Microsoft seem to read this blog (surely for comedic value), it’s a fair bet that trace flag behavior will change going forward. They’ll probably switch to more cryptic logging at startup, like saying “Trace Flag 9706 Enabled” rather than saying “Software Usage Metrics is disabled.”