Monday, March 10, 2014

Nothing fancy today... just to let you know trace flag 699 is a bust

I'm involved in or at least near a lot of performance and scalability testing.  One interesting facet of that work is the large amount of work involved in setup. Sometimes multi-terabyte databases have to be converted between widely disparate systems before a performance or scalability benchmark can even be established.  Minimizing the setup time can buy back a few hours that can be well used for investigation, exploration or tinkering.

So, when I first sw rumors that trace flag 699 disabled transaction logging throughout the SQL Server instance, I was intrigued.

In a production instance, I'd stay far away from pushing that big red button.  I personally wouldn't plan to use such a trace flag in a dev/test or QA/validation environment. But in cases where we are simply throwing terabytes of data between database implementations, and the data itself has been securely backed up, the thought of speeding up the migration is very tempting.

So I tinkered a bit with trace flag 699.  I set it globally.  I set it as a startup parameter.  I messed around with a few other scenarios.

I didn't get too scientific... I was really just looking for a binary. Was there any transaction logging after activating trace flag 699?  

The answer is: yes, there was.  As I mentioned, I didn't do a thorough compare.  The steady stream of new log records displayed by fn_dblog was enough for me.

I haven't seen anyone write about trace flag 699 in years.  Hey - if it worked I probably wouldn't tell many folks... a small way to keep some folks from shooting themselves in the foot.

But the references I did see were so old that I began to think that maybe trace flag 699 hasn't done anything for years.

I think it was probably there in the Sybase code base.  And maybe in some Sybase documentation somewhere.  But most likely, trace flag 699 hasn't done anything for years.

If you're trying to 'speed load' a large database, rather than tinkering with trace flag 699, you'll be much better off learning how to make trace flag 610 work for you, by loading data into clustered indexes with all nonclustered indexes created later. 

No comments:

Post a Comment