Trace Flag 2861 – The Trace Flag, The Myth, The Legend

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.