8

After reading an article, dated 2012, about the undocumented functions fn_dblog and fn_dump_dblog I was wondering why they are still undocumented and unsupported if they have been around for a long time and are quite useful. Same with this sp_msforeachtable and sp_msforeachdb article from 2004.

I could benefit from one of them to answer a question on the forum (During a log backup is the data backed up to the start or end of the operation?), but I'd like to know why they're shipped with SQL Server in a undocumented and unsupported way until now.

I understand they're said not to be reliable, but if that was the only reason, in 16 years they could have been corrected. So there must be another explanation.

3
  • 5
    I would like someone from MS to answer this, anyways Paul Randal has some good views on why some commands still remain undocumented
    – Shanky
    Oct 27, 2020 at 13:47
  • Interesting, @Shanky. Thanks. So in the end it might be related to the lack of time even if it's not the main reason. In one of the links on the OP, Paul Randal talks about a fix released for fn_dump_dblog, so they're not forgotten functions written once and left there the same way until now.
    – Ronaldo
    Oct 27, 2020 at 14:30
  • 1
    I am aware that as of now MS is very very busy with new release coming both for on-premises and Azure. The command "really" needs to be one demanding a proper use case to become a supported function, otherwise it will sit there being used widely as unsupported.
    – Shanky
    Oct 27, 2020 at 14:33

2 Answers 2

9

As someone working for a large software vendor, I can say that undocumented and unsupported1 features typically fall into one of these three categories:

  1. Tools for the exclusive use by the vendor support engineers. They are rarely needed, typically require intimate knowledge of internals, hard to set up and use, and often dangerous enough to be protected by one-time passwords or keys that should be explicitly provided by the vendor. They are never intended to become product features. They are there to aid support when troubleshooting hard to debug problems.

  2. Fixes and workarounds for esoteric bugs. They are implemented as hot fixes for critical situations. Such bugs are rare, and their root causes are eventually resolved, so these "features" lose their usefulness at that point, and it's not in the vendor's interest to provide continuous support for them.

  3. Beta and experimental features. They are typically first made available and sparsely documented for a small cohort of beta testers and major customers who requested them. Some of these will eventually become fully supported, some others will be left there to rot when stakeholders lose interest or when they conflict with other features that are deemed more important or useful.

As mentioned in the comments to your question, each fully supported and documented feature carries a cost to the vendor, and that cost must be justified by the expected benefit. If there is little benefit to the vendor from maintaining a certain feature, there's no reason to support it.


1 - They are not really totally unsupported; they are supported on a "best effort" basis, meaning "We might have a technote somewhere that briefly describes the feature, but we won't spend much time helping you make it work, and if it doesn't work or if it wipes out your data, well, tough".

3
  • Thanks, mustaccio. These could be good reasons and analyzing it now I completely agree with you about the unsupported not being "totally unsupported" since there was an effort to fix fn_dump_dblog. But it's possible more reasons exist since sp_msforeachdb, for example, is not 1) of exclusive use by the vendor; 2) a fix or workarounds for esoteric bugs (at least not by now) or 3) beta and experimental features (because of the long time it exists).
    – Ronaldo
    Oct 27, 2020 at 15:46
  • " the long time it exists" does not contradict "left there to rot"
    – mustaccio
    Oct 27, 2020 at 15:53
  • True. If that's the case, being such a huge software as SQL Server is, couldn't it be a problem to just leave pieces of code that are no longer wanted? I mean, isn't it a bad practice that could make maintenance harder? And it was kept over different versions of SQL Server, not just updates of the same version. Could retro compatibility be a reason?
    – Ronaldo
    Oct 27, 2020 at 16:41
1

I remember having to use one a long time ago. Ever try to change the SQL Compatability level of a database from inside a transaction? Turns out you can, if you copy the contents of the sp_ command that did it. (Disclaimer: this code only had to work on exactly SQL 2005 and its last service pack had already been released.)

In older times, the features you were only supposed to be able to access from the UI were implemented using various kinds of nonstandard syntax calling sp_ commands or xp_ commands. xp_ commands were hardwired, but there really weren't all that many of them. sp_ commands just contained TSQL that did stuff. They way they were actually implemented was undocumented, but you could use sp_helptext to read the code. Doing so was usually a bad idea.

Then there's one more undocumented feature people still use. The old password hash functions from SQL 7 are still there and callable. They're long undocumented and nobody should be using them anymore, but if you have an ancient application that still has to check passwords last changed in the prior century, it's the only way.

And there's ALTER DATABASE SET EMERGENCY that used to be something even more wild in even older versions. MS was forced to make a proper documented command out of it because of people finding the undocumented command and missing the rest of the procedure to use it. In this case, laziness caught up with them.

4
  • "but if you have an ancient application that still has to check passwords last changed in the prior century, it's the only way." - surely by now they could be reimplemented as a UDF or SQL-CLR function? Or moved into application code?
    – Dai
    Oct 28, 2020 at 9:01
  • 1
    "ALTER DATABASE SET EMERGENCY [...] because of people finding the undocumented command" - how on earth did people stumble upon that command?
    – Dai
    Oct 28, 2020 at 9:01
  • @Dai: There's probably a way to enumerate all DBCC commands, which is what it used to be.
    – Joshua
    Oct 28, 2020 at 15:37
  • @Dai: The password was hashed using the ancient function. How do you check it?
    – Joshua
    Nov 19, 2020 at 3:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.