In my recent post on using DBCC TRACEON() in conjunction with setting startup parameters, I pointed out that you can frequently avoid the need to restart a SQL Server immediately by specifying the startup flag/parameter necessary and then just executing DBCC TRACEON(####, -1) with the same parameter.

In most cases I’ve found that that works very well.

When DBCC TRACEON() Doesn’t Work

In some cases though, you’ll see that when you take such an approach, that the trace flags that you set will occasionally ‘not-take’ or won’t ‘stick’ after you close your connection/etc. (Or, the same thing goes with DBCC TRACEOFF() – where you try to remove a trace flag – and it doesn’t ‘stick’.)

First and foremost, if you see that your changes aren’t sticking, then make sure you executed DBCC TRACEOFF/TRACEON with the –1 second argument – which forces the statement global. (I didn’t do as good of a job as I should have with my previous post.) And if you’re sure you did that, then you may have run into one of those use-cases where DBCC TRACEON/TRACEOFF isn’t working as you would expect.

Sadly, this behavior is sort of by design. And, honestly, given how complex the code is that must be putting these trace flags into play, it’s not too terribly surprising that trace-flags can be a bit difficult to make ‘stick’ on a busy production system (which is mostly where I’ve noticed problems with trace flags not sticking past the end of a connection/etc.).

More importantly though a key thing to note from Books Online when it comes to DBCC TRACEON() is in the Remarks section, where BOL states that “to avoid unpredictable behavior”, you need to enable trace flags in one of two ways: Either by

a) Using startup parameters with the –T flag as I outlined in my previous post, or

b) Running DBCC TRACEON(####,-1) when nothing else is running queries.

It’s that second part that’s tough – as you’re rarely going to find that on a busy production server.

In my experience, I can usually get a trace flag to ‘stick’ on busy servers. But not always.

So, the take-away is that if you’re going to use the approach that I outlined in my previous post, you’ll want to make sure you keep an eye on things – and if the trace flag you’re putting into place is something that’s mission critical or could have serious impact were it to ‘captian kirk’ in and out of existence… then you’ll want to watch out for “unpredictable behavior” and only start those trace flags off with a service restart.

If you're using SQL Server Management Studio 2008 to manage database objects, you may run into an issue that causes you some grief when you right click on a sproc and either specify the Modify or Script > Create options. Specifically, you may see something that wraps your entire sproc in a call to sp_executesql, like what follows:

Not only does this behavior needlessly complicate things, but it also makes ad-hoc modifications to your sproc a bit more difficult as any ticks that you had in your actual sproc body, now have to be escaped (i.e. ' becomes '') and then you also need to make sure that you keep on top of that with any modifications or edits you may make.

Personally, I'd rather see the scripts generated by SSMS look more like the following – as that's also what they look like under the covers:

As near as I can tell, there are at least one (possibly two) factors involved. First, the exact issue that causes this problem is the scripting option "Include IF NOT EXISTS clause", as shown below.

When that option is set to True is when I've encountered the problem.

Or, more specifically, it appears that when I set that option to True, connect to a SQL Server 2000 server, and generate a script – then I run into the problem with sprocs from SQL Server 2000. More interestingly though, is that this seems to break something internally, because then my scripts for SQL Server 2005 and 2008 servers/objects also get broken.

Seems like a bug to me, but maybe it's just a 'by design side-effect'. Either way, switching the option to False seems to solve the problem for me.