Note (4/30/2021): DPA/SolarWinds still executes queries to turn this TF on, even though they’ve been told multiple times by myself and others that it no longer exists. You might want to get a better monitoring product if you’re still using this.
Note (7/4/2021): I’m a random person on the internet, but I also work with SQL Server for Microsoft; this information comes from looking at the source code back as far as I could find which was 2005 RTM through 2019. I can assure you that this TF does not exist.
I’ve been asked, intermittently, the same question for the last 3-4 years regarding trace flag 2861. It comes up in migration talks (I mean, you re-evaluate the trace flags you have enabled when you migrate/upgrade/etc., right? You don’t just copy/paste and go about your day, I hope.), when investigating an issue and going through the errorlogs, when asked why a vendor is needing sysadmin permissions to run DBCC TRACEON, and the like.
A few years ago (yeah, you guessed it about 3 or 4) I ran into a similar issue and I wanted to trust but verify, since that’s what we all should be doing – within reason – and thus went to find how applicable 2861 would continue to be, given a migration from 2008 to 2014.
If you do a cursory (no pun intended) search on the major search engines you’ll find all kinds of posts about 2861 needed for zero cost plan capture. This article, for example, have publish dates such as November 19th, 2018. This, however, was 2-4 years ago and these posts hadn’t yet existed. There were a few others that stated the same information, though, and so I wanted to just “be sure, myself”.
I went digging… and digging… and digging. I found absolutely nothing. From what I could surmise, trace flag 2861 didn’t even exist! That seems quite unreasonable since there were a few articles of major vendors who wrote code in their product specifically to turn it on. I spoke with a few people who might have an idea and sure enough I had just enough research to point me in the right direction.
Here’s the low down, trace flag 2861 did exist for the briefest of moments in history. It was added in SQL Server 2000 SP3 (I can’t find the exact build number at this time) and subsequently removed before SQL Server 2005 shipped. That’s right, it existed for roughly two service packs of SQL Server 2000 before being removed, for good.
If you’re still using this trace flag today, it literally does nothing. I can see some product vendors still bake it into their code, even though I’ve contacted them with my findings at the time. In fact, new information has been written about it and that it needs to be turned on… well it’s hard to turn on a trace flag that doesn’t exist in 2005+ on a 2017 server. Good luck with that.
TL;DR: TF2861 only exists in SQL Server 2000 SP3 and SP4, was removed before 2005 shipped, and if you’re using it now you’re definitely incorrect and should removed it from your startup parameters/application code that enables it.
9 thoughts on “Trace Flag 2861 – The Trace Flag, The Myth, The Legend”
Crazy that vendors insist on baking this old stuff into their code…
A bit of trivia: TF2861 was introduced in build 8.00.0652.
Initially, both compiled and execution plans were cached, but this was found to cause memory issues in some cases. Later only compiled plans were cached.
The KB-Article 326507 no longer exists though, or at least I couldn’t find it.
Awesome, thanks Sean! Since the codebase wasn’t easily accessed anymore I couldn’t find the exact build.
I have no idea why they continue to do it, I’ve sent emails, etc., and I get so many questions on this TF from people – which is what prompted this post.
Here’s an old copy of KB 326507.
Awesome, thank you Aaron!
Great thanks for very good article and comments! Added to SQL Server Trace flag list: https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Trace%20Flag.md#2861
Thanks Sean, let me follow up with the team here to understand what DPA is doing. My understanding is we disabled this years ago, but it was still an advanced option for a customer to enable. I don’t think we support SQL 2000 anymore, and I will advocate we remove any reference.
Great! Let me know what you find and I’ll update the post accordingly 🙂
It seems DPA 2020.2.690 is still poking the TF2861 bear.
Thankfully, SolarWinds details how to disable it in DPA at https://support.solarwinds.com/SuccessCenter/s/article/Turning-off-SQL-Server-trace-flag-2861-in-DPA?language=en_US
In that article, they say:
> DPA turns this flag on to get text for quick-running SQL statements.
Thanks! It’s sad that the article was updated recently and still points to another article, and both are entirely incorrect but not updated, even when the author has posted here. 🙁
Comments are closed.