The distinction between server administrator and database administrator often blurry. DBAs frequently need to track server uptime. This is particularly important where servers hosting poorly designed legacy applications require periodic reboots. To address this issue and to simply monitor server uptime statistics, we’ve identified a quick and solution.

The approach described below uses an executable available from Microsoft called uptime.exe. Uptime.exe is run from a command line and generates a single statement defining how long the system has been up and running. This information can be pulled into SQL Server for further processing.

Overview

Collecting the data is a very simple 3 step process:

1. Download the uptime.exe executable
2. Enable XP_CMDSHELL
3. Run a script that executes uptime.exe and capture the results

Each of these steps is detailed below.

Step 1. Download the uptime.exe executable:

Uptime.exe is available http://support.microsoft.com/kb/232243. Download the file and copy it to the C:\WINDOWS\system32 on the SQL Server where you are collecting the uptime information. Note that this program does not need to be saved to every computer of interest, only the SQL Server that is aggregating the data. Uptime will be called from a SQL Script in step 3 below.

Step 2. Enabling the XP_CMDSHELL

Since we are going to be executing uptime.exe from SQL Scripts, you must ensure that XP_CMDSHELL is enabled for your server. To determine if it is already enabled, start a new query in SSMS and type SP_CONFIGURE. When you execute this command the system will identify what options are enabled. XP_CMDSHELL should have a 1 next to it if it is enabled. If it isn’t enabled, it will have a zero next to it.

If XP_CMDSHELL is not enabled it can be done through SSMS.  Connect to each database server and execute the following script
 -- To allow advanced options to be changed.
 EXEC sp_configure 'show advanced options', 1
 GO
 -- To update the currently configured value for advanced options.
 RECONFIGURE
 GO
 -- To enable the feature.
 EXEC sp_configure 'xp_cmdshell', 1
 GO
 -- To update the currently configured value for this feature.
 RECONFIGURE
 GO

Step 3. Collecting Data from uptime.exe

Now that the application is installed and accessible, we can collect the data. The script below takes the output from the uptime application and inserts into a temporary table which is deleted after results are displayed.

 --create temporary server uptime table
 CREATE TABLE #tbl_uptime (id int identity (1,1),out_put varchar(max))
 --execute the uptime.exe file pointing it at various servers
 INSERT #tbl_uptime
 EXEC XP_CMDSHELL 'uptime \\SERVER A’   --server name here
 INSERT #tbl_uptime
 EXEC XP_CMDSHELL 'uptime \\SERVER B’   --server name here    
 ---more servers can be added here
 SELECT out_put AS server_up_time
 FROM #tbl_uptime
 WHERE out_put IS NOT NULL
 ORDER BY REPLACE(out_put,'\\','')
 ---more servers here
 DROP TABLE #tbl_uptime

Note that a single copy of the uptime executable residing on the SQL Server can hit any number of other servers. The output from this simple script is below:

This raw output can be parsed using select statements or regular expressions to separate the data into columns. Similarly it can be fed into permanent tables and used to trigger various actions.

Discussion

There are a lot of advantages to using this approach for tracking your uptime. Uptime.exe is a free utility from Microsoft. It requires no installation and runs on both 32 and 64 bit operating systems. As you saw from the description above, implementation takes just a few minutes.

Some don’t like to enable XP_CMDSHELL for security reasons. Namely, once XP_CMDSHELL is enabled, some database users may be able execute commands against the operating system. Depending upon who is using your SQL Server, you may not want to allow that kind of access to your operating system environment.

Conclusion

Uptime.exe allows for a quick and easy way to track how long your servers have been running. This information can be used to track server uptime and to support the timely reboot of legacy systems that require it.