There are several undocumented stored and extended procedures that Microsoft uses internally in SQL Server. When you start to dig for information on them, the general line from Microsoft is not to use them because they could be removed at any time.

That said, there are a number of them that seasoned DBAs use all the time, such as sp_msforeachdb and sp_msforeachtable. It’s unlikely that Microsoft will remove them anytime soon, though, apparently, those two are not available in Azure. Every DBA that uses these does so at their own risk.

One reason DBAs will often use these procedures is because they see SQL Server using them. However, when a DBA does use them, they tend to expect them to operate a certain way. However, I recently learned that even this expectation should be taken with a grain of salt.

I recently came across an example that I thought I’d share as a case study on why one should be careful using them. To help debug an issue a client was having, I wanted to increase the number of error log files their server stored.

For a one-off like this, I tend to go to the GUI and change the value. In this case, I changed it from the default of 6 to 15.

Changing the value is quick and easy for a single machine, but often tasks like this must be performed on dozens if not hundreds of instances. That led me to do some research to find out what was going on behind the scenes.

xp_instance_regwrite

Most dialogs in SSMS have the option to script out the action instead of performing it. Because I like to know what SSMS is doing, I took advantage of this feature and scripted out the command.

This is what you get when changing the maximum number of log files:

Save the results as Set_NumErrorLogs_15.sql. After running the script, you should get back (0 rows affected), but if you look at the setting in the SSMS GUI, you’ll see the updated number.

You could copy this command to every machine you wanted to run it on and execute it manually each time. However, this doesn’t scale well if you have hundreds of machines. If you recall one of my previous articles, Using a Server List to Control PowerShell Scripts, you will remember me showing you a method to create a text file to control the running of script across multiple servers.

This approach is an excellent way to make a change like the one above. Because you may be wary of using an undocumented extended procedure, you may want to approach this differently. Instead, you might want to use what would appear to be the appropriate built-in PowerShell cmdlet; in this case, Google suggests New-ItemProperty for updating registry values.

New-ItemProperty

One issue with New-ItemProperty is that it doesn’t have a parameter to specify which server it should be run against. That’s an issue you can try to address later.

Before you go too far, though, create a script called New_ItemProperty_1.ps1 as follows:

-Force will make sure New-ItemProperty overwrites an existing value if it exists.

-Whatif is a handy parameter many cmdlets have that show you what will happen when you run the cmdlet without it carrying out the command.

After running the script, you might get an error like the following meaning that the path doesn’t exist:

If you get the following:

This means you do not have permissions to change the registry.

In some cases I’ve found you actually will not get an error:

If you get this message, go ahead and remove the –Whatif argument and rerun the command. You should see:

If you go back into SSMS and look at the number of error logs, you will notice that the value has not actually updated! “Curiouser and Curiouser” as Alice might say.

Assuming you get the first error, you’re probably scratching your head and wondering why the command failed. The next step is to read the registry to see what is there. Call this script Get_ItemProperty_1.ps1.

You should get an error like the following:

Just to make sure you have the correct syntax, try the following script and call it Get_ItemProperty_2.ps1:

You should see results similar to the following (yours will depend on the version of the OS you’re running):

The results prove that it’s possible to read the registry this way.

xp_instance_regread

Before continuing, I want to show the opposite of how SQL Server writes to the registry. Again, this is an undocumented extended procedure. To confirm that you know what the number of error logs is on your current instance, run the following:

If you followed the steps above, you should get:

It’s quite obvious that SQL Server can use these two extended procedures to write and to read from the registry, but strangely, a quick search of the registry may not show the key or value under the path shown!

Using Regedit

At this point, I would recommend you open Regedit and look at the registry directly. The Registry should look similar to this if the path exists on your machine:

This is looking promising. The value seems to be there, but, if you change this to a different value, SQL Server does not actually use this value. For example, you could even delete this and SQL Server will still show a value of 15 logs.

Now you are probably as confused as I was. The SQL Script referenced HKLM:\Software\Microsoft\MSSQLServer\MSSQLServer\ and set the value of NumErrorLogs as you can confirm by checking the GUI in SSMS.

My first thought was that perhaps since SQL Server runs under domain account on my client’s computer, that somehow permissions were preventing me from accessing the key correctly. I ruled that out via further tests and when I realized as local admin, I should be able to see any keys, regardless of permissions.

The next step is for you to go to the root of the HKEY_LOCAL_MACHINE and search for NumErrorLogs

If you do this, you will finally discover where NumErrorLogs shows up.

This screenshot is from my test server. Instead of MSSQL14.SQLEXPRESS, it was MSSQL13.MSSQLSERVER on my client’s machine since they’re running SQL Server 2016.

Despite giving xp_instance_regwrite a specific path, it wrote to a different location.

Mystery solved!

When you stop to think about it though, the name is actually a giveaway. The extended procedure xp_instance_regwrite is NOT a general extended procedure to write to the registry; it’s designed to write to registry entries for a specific instance on that server. This makes it far more useful when it is used as intended: to set a value for a specific instance.

What does this mean if you want to set the NumErrorLogs using a PowerShell script? The answer is that there is the quick and dirty way and the right way. You can guess which I choose for my client’s script to get this done quickly.

Save the following script as Set_NumErrorLogs_15_Quick_And_Dirty.ps1:

You will need to either create a SQLServerList.txt file with the list of your server instances or hardcode them. Only one of those first two lines should be used.

Remember, xp_instance_regwrite writes to the registry of one specific instance of SQL Server. This means you can update the number of error logs for multiple instances on the same SQL Server since the script connects to each one and runs the command there.

That’s the quick and dirty way.

I wanted to create what I’d call “the right way” that doesn’t depend on an undocumented extended procedure. Fortunately, in PowerShell, that’s as simple as it sounds.

SqlServer Module

It was pointed out to me in a previous article that Microsoft had deprecated the older module, sqlps, in favor of the newer sqlserver module. I had purposely written older scripts using sqlps because it was available on older systems like what was available at my client’s. But time marches on, and it was time to upgrade.

To detect if your system has the sqlserver module installed, you can run:

If you don’t see anything returned from the command, you don’t have it installed. To install it, follow the directions here. Note, in my case, I had to use the –AllowClobber parameter.

After successfully installing, testing the results should return something like this:

Your results may vary depending on what version you have installed or downloaded.

Once you have it successfully installed, you may need to restart your PowerShell IDE. In my case, it recognized that I had installed the module, but gave me errors if I tried to use any of the cmdlets.

Now that there is a proper way to do this in PowerShell, you can update your script as Set_NumErrorLogs_15_Cleanly.ps1:

Using this cmdlet is much better than using the undocumented extended procedure, and because the cmdlet is part of a Microsoft supported module, you run a far lower risk of it suddenly not working in the future.

Note the cmdlet get-sqlerrorlog does not appear to have a setting to get the number of error logs but does allow you to read and review the error logs for a particular machine.

Dbatools.io

Besides the Microsoft supported sqlserver module, there is a very popular 3^rd party supported module I haven’t mentioned before and don’t use enough: dbatools. This is a PowerShell module created and maintained by several fellow DBAs, a few I’ve even had the honor of meeting. This module is a powerful set of cmdlets that I vow to use more of in the coming year and perhaps write about. I may even add to the project to extend the tools.

Installing dbatools is as easy as running:

Note you must be running as an administrator. For more details go to the download page

Once installed, you can run a command such as:

You should get results like:

This is a good start! (Note it appears at least on my machine I had to run this as administrator). Now you can get the number of error logs across instances if you want to ensure all your settings are consistent. Unfortunately, if you look for the obvious mirror to this, set-dbainstanceproperty, it doesn’t appear to exist!

For anyone enterprising, however, here is an opportunity to contribute to an amazing 3^rd party tool. I would recommend that they provided a syntax similar to the following:

To be even more useful, make it smart enough so that if an array of strings is provided to –sqlinstance, the cmdlet will loop through the array.

Conclusion

I started this journey because I wanted to write a script that I could run across multiple machines, and because I wanted to understand precisely what the script was doing. In the end, it reinforced the idea that one should be wary of using undocumented stored or extended procedures because they may operate differently than you expect, even if they end up doing the right thing in the end. Also, of course, you’ll find many may not operate in the Azure or Linux environments.

If you’re going to be scripting, it is worth taking the time to learn PowerShell and how to perform the same actions in PowerShell. Don’t rely on undocumented and hence unsupported stored procedures or extended procedures.

Scripts mentioned in the article are available here.