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 PowerShellGallery.com 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.
Test-PendingReboot -Detailed -ComputerName localhost
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.
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.
WHERE is_dynamic = 0
AND value <> value_in_use
SELECT ‘Restart is needed’ AS ‘Restart Needed?’
SELECT ‘NO restart is needed’ AS ‘Restart Needed?’