SQL Server troubleshooting – what happened with my instance

Goal

Obtaining information about what happened during my last restart of SQL Server instance and if this was a normal restart or an unexpected restart

My way of achieving it

Using SSMS, xp_cmdshell and Wevtutil  we can achieve the goal and we can overcome some of the challenges we face in case we are after the same information using some of the other approaches. I have chosen to not use  SQL Server Error logs because of the

– cycling of the logs during each SQL server instance restart or by using the sp_cycle_errorlog in our environments

– information inside the logs doesn’t contain information outside of SQL Server

I want to mention that the execution time of the script is influenced by the information captured and kept in the windows application logs and the reason for which I decided to display by default 1 sql server restart times.

In case of a clustered instance we have to provide the credentials that will be used to ran the Wevutil remotely and another mention as important as first one is that the script is working only for 2 nodes cluster and in case we would like to run it on a clustered instance that has more than 2 nodes then we need to tweak the script a little bit.

The output of the script is shown below

irmi

We can use the information to check what happened during last restart and see what types of errors , critical, warnings appeared in the last 60 minutes before the last restart in the system and application log.

The script can be downloaded from here

Leave a Reply

Your email address will not be published. Required fields are marked *