Current Server Up Time

Upon occasion, you may need to know how long your SQL Server has been up and running. Conveniently, that information is readily available through a dynamic management view (DMV).

-- Select the SQL Server start time and calculate duration in various units
    'UpSince' = sqlserver_start_time
    , 'CurrentTime' = GETDATE()
    , 'UpTimeDays' = DATEDIFF(DAY, sqlserver_start_time, GETDATE())
    , 'UpTimeHours' = DATEDIFF(HOUR, sqlserver_start_time, GETDATE())
    , 'UpTimeMinutes' = DATEDIFF(MINUTE, sqlserver_start_time, GETDATE())
    , 'UpTimeSeconds' = DATEDIFF(SECOND, sqlserver_start_time, GETDATE())
FROM sys.dm_os_sys_info

Of course, rather than up-time as such, what we are generally concerned with is when a server becomes unavailable. Given that, some sort of active alerting might be far more useful than up-time reporting. We would obviously prefer not to use SQL Server to notify users when it becomes unavailable… because if it is unavailable how can we rely on it to send an alert?! The best solution is to have a witness — a completely separate server that monitors and reports on SQL availability. (And then a witness to monitor the witness… and a witness to monitor the witness that is monitoring the witness… ;-)) Barring a dedicated witness, we could create an alert at the operating system level that alerts on the SQL Server service running on the OS. To do that:

  1. Create a new task in the system tools > task scheduler
  2. For the trigger, choose to begin the task on an event
  3. Choose custom event and enter the following XML, replacing <your server> with the name of your SQL server instance.
      <Query Id="0" Path="System">
        <Select Path="System">
          *[System[Provider[@Name='Service Control Manager'] and (EventID=7036)]]
          *[EventData[(Data='SQL Server (<your server>)') and ((Data='stopped') or (Data='paused'))]]
  4. For the action, choose to email a suitable distribution group

The XML event definition specifies that we will look for event 7036 within the Service Control Manager log; that event fires whenever a service changes states. Within that event, we are filtering on data within the Event Data node that contains the SQL Server instance name and either the “stopped” or “paused” key words. More explicit filtering is, of course, possible… but it is limited to a subset of the XPath 1.0 syntax. (FYI, certain operators such as “contains” and “starts-with” are not available.)

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: