When was the SQL Server instance restarted?

Most production environments have documentation/logs and checklists maintained by a DBA that would be able to tell precisely when and why a SQL Server restart was issued. But this is not the case in development/quality assurance environments. In fact, as I was writing this post, I realized that my test instance has not been restarted since more than a month!

I was faced with this question at the office recently. One of our nightly jobs failed in quality assurance because the SQL Server service appeared to have had restarted, and we wanted to know when this had happened. I knew one method, which I used that day.

Upon further experimentation, I also found another method (using nothing but T-SQL) to determine when a SQL Server instance had restarted. Today, I will share with you both these methods:

Method 01(A): Reading the SQL Server logs through the UI

What a DBA would do in production (i.e. maintain logs) is also done by SQL Server itself. The SQL Server logs would therefore be the ideal place to go to when searching for information on when the SQL Server service restarted.

To navigate to the SQL Server logs using the SQL Server Management Studio (SSMS) UI, connect to the SQL Server instance in the Object Browser (Covered in part #2 of 10-part tutorial on SSMS), and navigate to the Management node.

Under the Management node, you will see the “SQL Server Error Logs” which would contain a set of log files (the number of log files to maintain is configurable hence; you may have more than that illustrated in the image below).

Double-click on the log file marked “Current”. Generally, in one of these log files, you would find an entry similar to:

“SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.”

The first occurrence of this entry when going in reverse chronological order will indicate the date/time when the SQL Server was started.

The only case when the logs would fail to get the restart date/time is when the SQL Server has been up and running for a time long enough for the logs to have recycled over themselves.

Method 01(B): Reading the SQL Server error logs through T-SQL

In method #1A, we read the SQL Server error logs through the SQL Server UI. We can achieve the same through T-SQL also. The system stored procedure sys.sp_readerrorlog (undocumented, to the best of my knowledge) can be used to read the contents of the SQL Server error log. This system stored procedure takes up to 4 parameters:

• Error log file to read (0 = Current, 1 = Archive #1, and so on)
• Log File Type (NULL/1 = SQL Server Error log, 2 = SQL Agent Log)
• String 1 (first search string)
• String 2 (string to further refine the search)

We need to read the current file in the SQL Server Error Log, and only need to search for one string – “SQL Server is starting”. The stored procedure would therefore be:

EXEC sys.sp_readerrorlog 0, 1, 'SQL Server is starting'

The output is similar to what is shown in the graphical UI of the error log reader:

Method 02: Using Dynamic Management Views – sys.dm_server_services

Reading and working with error logs requires some amount of skill because they provide the user with an ocean of information – all of which is useful. For the apprentice, the error log might be a little too much to swallow and therefore, I would recommend using Dynamic Management Views (DMVs) to get to the required data. In fact, I would recommend DMVs for the experienced DBA too.

Why? They can be used in a T-SQL query just as any other table (so querying them is not a foreign concept) and they provide “real-time” information about the SQL server instance.

The DMV that provides information about the SQL Server and the SQL Server Agent services in the current instance of SQL Server is sys.dm_server_services. Along with information about service status, service account, process_id and clustered information, this DMV also provides information about when the SQL Server and the SQL Server Agent services were last started up!

SELECT * FROM sys.dm_server_services

Isn’t that simple? Just a single query and you have the answer to one of the most common questions of all time!

Until we meet next time,

Be courteous. Drive responsibly.