jump to navigation

Looking into the SQL Server Log October 17, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
trackback

In this post I will be looking into the SQL Server Log and how we can use it so we can identify and troubleshoot potential problems.

Recently I asked someone that gave me a call regarding an SQL Server issue, to have a look at the SQL Server Log and he just could not locate it. That is why I decided to write a post for this great tool.

I will show you how to access the SQL Server Log using various ways.

I will be using SQL Server 2012 Developer Edition but the Express edition will suffice .

I connect to the SQL Server instance using SSMS. I log in as sysadmin.In order to access the SQL Server Log I expand the Management node and then the SQL Server Logs node. One can see all the available logs.We have 7 logs in total. The Current and 6 Archived ones.These logs are for the SQL Service Service and not for (e.g) the Agent Service.The Current Log is the log that SQL Server is currently using.

Please have a look at the picture below.

We can use the Log File Viewer to view the contents of the current Log or any archived log.We can filter,search  the log.

Please have a look at the picture below

When you stop and start the SQL Server Service it archives all of the logs.Archive #6 means 6 times ago SQL Server was stopped.

I am going to stop and restart SQL Server Service.What happens now is that the oldest log is deleted and a new one is added. Archive #5 becomes Archive #6.

Please have a look at the picture below.

We can configure the number of archives that SQL Server will have.Select the SQL Server Logs node, right-click on it and select “Configure“. There you can set the maximum number of error log files.

Have a look at the picture below.

We can use the xp_ReadErrorLog stored procedure to read the contents of the SQL Server Log.

In a new query window I type

USE master
GO

EXEC xp_ReadErrorLog

Have a look at the picture below

We can access the SQL Server Log by going to the installation directory of SQL Server. Then I locate the Log folder. Then I open with some sort of  application (Notepad ++) the log I want.

Have a look at the picture below

 

We can use the Event Viewer to have a look at the contents of SQL Server Log.

Have a look at the picture below.

I am sure that you realise that the best way to view the SQL Server Log is the SQL Server Log File Viewer.

 

Hope it helps!!!

Comments»

No comments yet — be the first.

Leave a Reply

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

%d bloggers like this: