Undocumented Store Procedures on SQL Server 2008

 

Introduction

                Hello, a few days ago I was think that even the high usage that I personally use of the xp_readerrorlog (or sp_readererrorlog) stored procedure to review the SQL Error Log, if do an internet search about it you will not find any official references to it. Like this stored procedure there are a few more that are undocumented on Books Online, but they can be helpful on different situations.

¿Why does undocumented store procedures exist?

                Some authors think that in many cases there’s no important apparent reason to release documentation about this store procedures, so the users and administrators can use them without problems, or that they shouldn’t be available at all.

                While this statement have some logic from the end user, we don’t think it’s not at all right, I have found information about this store procedures and other undocumented tools in general (DBCC commands, Functions, Trace Flags and Startup Options) might be used for testing purposes while building the product or might have been created for a deep debugging by Microsoft technical support.

¿It’s OK to use the undocumented store procedures?

                We think that is not by chance or oblivion (like another author might think) that this tool are not documented, some of them might be dangerous when used wrong, and the fact that are not documented also warning about that in any moment (hotfix, patch, service pack or a new version is released) its functionality or behavior might change, or might be totally eliminated, it’s also true that this tools are not test against through and exhaustive tests, and is not recommended at all the use of them at end user applications (should not be referenced on the code).

                For an example of how real it’s the risk when we implement this procedures inside the code I found an issue where a server processor was used at a 100%, on this server were a SQL Server 2005 SP3, on this instance an end user process was constantly calling the xp_readerrorlog, the root cause of the problem was a bug related to the execution of this extended store procedure (https://support.microsoft.com/kb/973524).

                So we need to understand, that using this undocumented store procedures is at our own risk. However many of them are really useful on our daily activities.

 

A Few Useful undocumented procedures:

 

  1. 1.        xp_dirtree

The execution of this store procedure gives us as a result the list of all the subdirectories of a directory specified as an input parameter:

EXECUTE master.sys.xp_dirtree ‘<path>’

Theres another extended store procedure (xp_subdir) that only returns the first level subdirectories, is like using the xp_dirtree with the condition depth = 1.

 

  1. 2.        xp_enum_oledb_providers

The execution of this store procedure gives us as a result the list of all the OLE DB for SQL Server:

EXEC master.sys.xp_enum_oledb_providers

  1. 3.        xp_enumerrorlogs

The execution of this store procedure gives us as a result the list of files on the SQL Error Log with their last modification date

EXECUTE master.sys.xp_enumerrorlogs

  1. 4.        xp_enumgroups

The execution of this store procedure gives us as a result the list of the windows groups and its description

EXECUTE master.sys.xp_enumgroups

 

  1. 5.        xp_fixeddrives

The execution of this store procedure gives us as a result the list of the disks (drives) and the amount of free space (on MB) for each of them:

EXEC master.sys.xp_fixeddrives

 

  1. 6.        xp_readerrorlog

To finish, he execution of this store procedure gives us as a result the content of the SQL Error Log files, it’s pretty useful when looking for errors on the instances:

EXEC master.sys.xp_readerrorlog

It can receive input parameters:

  1. 1.       Error log file you want to read: 0 = Actual, 1 = Archive #1, 2= Archive #2, and so on.
  2. 2.       Type of error log you want to read: 1 or NULL = SQL Error Log, 2= SQL Agent.
  3. 3.       String Lookup 1; string of characters to filter the show results.
  4. 4.       String Lookup 2; secondary string of characters to filter the show results, an do a precise lookup
  5. 5.       Beginning Date/Hour lookup: to look from this date and hour
  6. 6.       Ending Date/Hour lookup: to look until this date and hour
  7. 7.       Results Order: ‘asc’= ascendant, ‘desc’= descendent

The store procedure master.sys.sp_readerrorlog makes a call to the xp_readerrorlog, but the difference is that the last one can only receive the first 4 input parameters mention

On Conclusion

                As we could see, the procedures presented where mostly extended store procedures, this execute an DDL that runs directly on SQL Server, on many cases these can only be executed by sysadmins.

                There are others undocumented stored procedures onSQL Server 2008, if you want to obtain information from all store procedures available on SQL 2009, you can use the below query, and them look which of them appear on the SQL help or not:

SELECT OBJECT_NAME(c.id), c.*

FROM master..syscomments c JOIN master..sysobjects o ON c.id = o.id

WHERE o.type in('X', 'P')

ORDER by 1, 4

*Tip: the intellisense tool, can give you some information on undocumented store procedures and functions, as an example type:

EXECUTE master.sys.sp_re

You’ll see that intellisense will look for the sp_readerrorlog and give you information about the input parameters.

 

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”