Tag: SQL Server 2014

SQL 101: Two places to start troubleshooting

Getting started

Someone calls you at 1 AM on Saturday and says, “Is there an issue with SQL?” First, breath and take a look at your logs. This post is about two places you can take a look to see if there’s an issue, quick, so you can get back to sleep. Especially when I was new to SQL Server, I thought fixing an issue would involve querying the server to see what was there. However, both of these locations are available through the GUI and are easy to get to. The two places I’d start looking are the Windows Server Application Log, and the SQL Server Log.

What to look for

In both of these locations, look for the last date time when you knew SQL was working. Then look messages about errors from SQL in between then and now.

1: Windows Server Application Log

My favorite way to read this log is with the app Event Viewer. You can search for this app in Windows or you can go to the file directory: C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Administrative Tools

Start up this app. By default, it shows the logs of your local computer. To connect remotely, go to the top left of the app, click Action -> Connect to another computer

Once you’ve got the app running, go to Windows Logs and click on Application. It’s as easy as that. If you have multiple SQL Server instances running on the same server, you can look at the “Source” column to see what instance wrote to the log.

Here’s what you should see:

2: SQL Server Error Log

Another useful log. To find this one, you can go to the file location listed in the screenshot above. That’s for my local instance, yours will be slightly different since my instance is named “FOURTEENTWO” (SQL Server 2014, instance #2 on this server).

Or

You can open SQL Server Management Studio (SSMS). Connect to your server, in Object Explorer go to Management -> SQL Server Logs

Guaranteed?

No. I can’t guarantee that this will help you fix your issue. But I always forget it! It’s saved me a lot. Here’s a list of things that are logged in these locations, that I’ve needed to find:

  • When SQL Server started
  • When SQL Server crashed and where the log dump is stored from that crash
  • Changes in compatibility level of databases

 

Using Live Query Statistics on older SQL Servers

First, thanks to everyone who attended my session at SQL Saturday #634 Iowa City! I spoke about execution plans and how to begin reading them. Here’s a link to my session.

I had an attendee come up afterwards who mentioned that Live Query Statistics could be used with the newest version of Management Studio to watch query execution plans live on older versions of SQL Server. I was completely unaware of this, and this feature is really awesome. Live Query Statistics allows you to watch what indexes or actions SQL Server is doing while your query executes. This works really well if your query runs for one or more minutes and you want to see what is taking this much time.

Once I learned this, I was curious what versions this feature can be used on. I figured there were some limitations, so I made sure I had the latest release of Management Studio (Standalone), at the time of this post, it’s 17.1. It appears to me that the oldest version of SQL Server where this will work is SQL Server 2014, based on a test I ran locally and documentation.

Here’s the screenshot, directly from Microsoft’s documentation on Live Query Statistics:

This is super cool since it’s a lot of work to upgrade a SQL Server, and this lets me use these features without upgrading (yet).

 

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close