#0195-SQL Server – When was the SQL Server instance restarted?


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).

image

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.

image

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:

image

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

image

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.

Advertisements

2 thoughts on “#0195-SQL Server – When was the SQL Server instance restarted?

  1. marc_jellinek@hotmail.com

    There are a number of other ways to determine when the instance was restarted:

    Check the Windows Application Event Log. Look for Event 17110, 17176 or 17162. These events are logged when an instance starts.

    Check the Windows Application Event Log for Event 17177 (This instance of SQL Server has been using process id {x} since {y}. {y} will be when the instance was restarted.

    Other variations will say how many seconds the instance has been using the process id. Just subtract the number of seconds from the date and time stamp on the log record to figure out when the instance started.

    Check when tempdb was created (it’s recreated with each service restart)

    SELECT [create_date] FROM [master].[sys].[databases] [db] WHERE [db].[name] = ‘tempdb’

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

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

WordPress.com Logo

You are commenting using your WordPress.com 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