Is there a pending reboot on my SQL Server?

As a DBA we make every effort to avoid bringing systems off line, however there are times where that is exactly what needs to happen.  From a Windows and SQL Server perspective there are a number of changes that require a reboot.  These reasons range from the application of Windows updates, the changing of Windows policies and changes to SQL Server settings.  Over the years there have been a number of times where I have connected to a server and found a message stating that there is a pending reboot.  When changes are made, of course we want the changes to take effect as soon as possible.  In some cases a change will not take effect until a reboot happens. In order to determine if there is a pending reboot, we need to look at a number of places, including both Windows and SQL Server.

Starting with Windows, there are some changes that require a reboot, including that application of Windows updates.  Checking for a pending reboot for Windows can be done using one of the newest friends of a DBA, Powershell.  The information that needs to be queried is in the registry.

This is the popup that I have seen a number of times showing that there is a pending update.  What makes this the less than ideal option is that I need to logon to the server.

Brian Wilhite has a very nice PowerShell module that you can use to check for pending reboots in Windows.  It can be found on GitHub here. It can also be found on here. You can also download the ps1 file here.

On August 20, 2018 he stated on Microsoft’s Script Center web site that he is no longer making updates to this on the Script Center site, you will need to get it from GitHub instead, link is above.  According to the Script Center site here are the versions of the OS it has been tested on.  I have tested it on Windows 10 and it appears to work just fine.

The script below will install the module and complete the check.

         # Install

Module -Name PendingReboot
# Run
Test-PendingReboot -Detailed -ComputerName localhost
In order to run this script, you must run the Powershell application as Administrator.  If you do not, you will see this error.
Below are the results of running the PendingReboot PowerShell script.  By adding the -Detailed switch you will see more information about what might have caused the need for the restart.  In addition, you can add the -ComputerName switch to check a remote computer.
What I like about this is that it allows me to test a remote computer.  Just as a precautionary measure, make sure to throughout research and exercise the appropriate caution prior to running any code obtained on the internet on production servers.
Of course knowing if there is a pending reboot in Windows is important, however there are a number of SQL Server settings that will also cause a need for a restart of the SQL Server service in order to take effect.  The above powershell script will not detect this.  There are a few ways using SQL Server Management Studio that can tell us if the service needs to be restarted.
The first method is to look at the settings of the SQL Server.  At the bottom you will see “Configured Value” and “Running Value”.  If you look at the images below, you can see that the Default index fill factor is different.  The configured value is 0, while the running value is 85.  This is a sign that the SQL Server service needs to be restarted.








The drawback of this type of check is that I need to manually go to each setting option and do a compare.  Luckily, Microsoft has added a view for us to use to programmatically check this.  The view is sys.configurations.  There are a number of columns that are important for what we are looking for.  Below I have listed the columns that are important for the purpose of this post.  Of course there are more columns that these.

Is_dynamic is the column that will tell us if a restart is needed if that value of the setting is changed. If this is true, a restart of the service is not needed if the value is changed.

is_advanced identifies if it is an advanced option

value_in_use is the column that will tells us the Running value

value will identify that configured value

When looking at this, if Value_in_use and value are different, then a service restart is needed.  In this case, the fill factor(%) is different.  The running value is use is 85% while the configured value is 0.


This is the code used to get the above result set.

SELECT    name
  , is_dynamic
  , is_advanced
  , value_in_use
  , value
FROM sys.configurations
WHERE is_dynamic = 0

In order to determine if a restart is needed, we need to add more to the WHERE clause.  We just need to do a simple comparison of the value_in_use and the value columns.  Of course we are also only looking at the settings that is_dynamic is set to FALSE or 0.  If this query below returns any rows, a restart of the service is needed in order to have it take effect.

SELECT    name
  , is_dynamic
  , is_advanced
  , value_in_use
  , value
FROM sys.configurations
WHERE is_dynamic = 0
  AND value <> value_in_use
  SELECT ‘Restart is needed’ AS ‘Restart Needed?’
  SELECT ‘NO restart is needed’ AS ‘Restart Needed?’
Hopefully you will find this information useful.
Thank you for visiting my blog.

Where did it go???

Have you have ever looked for something and expected it to be there…and it wasn’t? If so, you will understand my day today. I have a SQL Server Agent job that uses VBScript in one of it’s job steps and we tried to move it to a SQL Server 2016 server.  It unexpectedly failed because the ActiveX Script job step type is no longer an option.

Below you will find a screenshot from a pre-2016 server.  As you can see, the job step type is there.

Now here is a screenshot of the job step properties from a SQL Server 2016 server.  Notice, NO script job step type.  However, you will see a PowerShell job step type that we can use instead.

Here is the link to Microsoft that describes other features that were removed from SQL Server 2016.

You can query the sysjobsteps table to find the jobs that have these types of job steps.

, s.step_id
, s.step_name
, s.subsystem
FROM dbo.sysjobsteps s
         INNER JOIN sysjobs j
                 ON s.job_id = j.job_id

I have been telling my students for a number of years that as DBAs we will need to learn PowerShell someday. That day actually arrived a few years ago.  This is a great example of why we should know PowerShell.

I really try to keep up on the features that are removed from versions of SQL Server, however this one slipped through the cracks and I missed it.  I just really never expected it to be removed, although I do understand why MS did remove it.