Occasionally we might want to check to see how long SQL Server has been running uninterrupted. For example, how long has SQL Server been running? Or when was the last restart?

Fortunately there are a multitude of ways we can go about this. Some of these methods involve checking the event viewer or going through the SMSS GUI. But here are four ways we can do it with a T-SQL query.

Option 1: The sys.dm_os_sys_info View

The sys.dm_os_sys_info view returns some useful information about the computer, and about the resources available to and consumed by SQL Server. Included in this is the sqlserver_start_time column, which returns the local system date and time SQL Server last started.

Example:

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

Output:

sqlserver_start_time    
------------------------
2024-07-24T02:38:24.916Z

This view is probably the best one to use for finding the startup time of the server. That’s because it returns the actual start time, whereas other DMVs only return information about activity that’s occurred since startup (granted, some activity occurs as part of the startup process).

Option 2: Checking the tempdb Creation Date

Another way to check SQL Server’s startup time is to find out when the tempdb was created. This database is created at startup, and so we can use this time to find out when SQL Server was started. To find this out, we can query the sys.databases view:

SELECT create_date
FROM sys.databases 
WHERE name = 'tempdb';

Output:

create_date             
------------------------
2024-07-24T02:38:28.800Z

We can see that this time is almost identical to the previous result (although a few seconds late).

Option 3: The sys.dm_exec_sessions View

The sys.dm_exec_sessions view returns information about all active user connections and internal tasks.

This view includes a column called login_time, which is the time at which a client process logged into the server.

By getting the minimum value of this column (by using the MIN() function), we can figure out when SQL Server started up:

SELECT 
    MIN(login_time) as min_login_time
FROM sys.dm_exec_sessions;

Output:

min_login_time          
------------------------
2024-07-24T02:38:26.843Z

Option 4: The sys.sysprocesses View (deprecated)

The (deprecated) sys.sysprocesses view returns information about processes that are running on an instance of SQL Server.

This view includes a column called login_time, just like in the previous example. It also includes another column called last_batch, which is the last time a client process executed a remote stored procedure call or an EXECUTE statement.

Here’s an example of returning these values:

SELECT
    MIN(login_time) as min_login_time, 
    MIN(last_batch) as min_last_batch 
FROM sys.sysprocesses;

Output:

min_login_time            min_last_batch          
------------------------  ------------------------
2024-07-24T02:38:26.843Z  2024-07-24T02:38:26.843Z

As mentioned, this view is deprecated, and so use any of the other options if possible.

Conclusion

There are many ways to find out when SQL Server was last started/restarted. While some of these rely on accessing the GUI, we also have the option of running a T-SQL query to get the results we’re looking for.

As mentioned, the first option listed in this article is probably the best/most reliable query, as that returns the actual startup time, whereas the other queries return certain activity that occurs around the start up time, which could be a few seconds off. Plus the view listed in the final option is deprecated, so it’s best to avoid that one.