How to View SQL Server Error Log File Size and Its Location

What is SQL Server Error Log File?

Error log file in SQL Server is a type of file, which contains the messages generated by SQL Server during every faulty transaction executed on the database. The error log also contains information like; events and also contains chunks of stack dumps.

On the other hand, we can say that, the error log file is a hidden block of information. Moreover, one very important thing is that if the MS SQL Server error log file gets spacious, it becomes more time consuming for the user to read the error log.

View SQL Server Error Log File Size

To determine the size of SQL Server error log file first we should check the server. A convenient way to find out the Error Log File size in SQL Server is go to the Windows Explorer on the server. Now open the Error Log File path and check its file size as displayed in following screenshot:

The undocumented stored procedure xp_enumerrorlogs, can also be used to check the error log file size. There is need of a policy, which verifies each Error Log file size against the configured threshold. Here we will create a new policy dbi services. The stored procedure xp_enumerrorlogs shows the following information about the error log file:

1. Archive Number
2. Creation Date (recycling date)
3. Size in byte

Creating Stored Procedure to Check the SQL Server Error Log File Size

CREATE TABLE #ServerError Log
(
Archive INT,
dtDATETIME,
FileSize INT
)

INSERT INTO #ServerErrorLog
Exec xp_enumerrorlogs
SELECT Archive, FileSize/1024/1024 AS [Error Log Filef size (MB)]
FROM # Server ErrorLog
DROP TABLE #ServerErrorLog

How to Find SQL Server Error Log File Location

There are many ways to find the location of the SQL Server Error Log file used by the instance. Here we will look at three different ways to find out the error log location:

1. Reading the SQL Server Error Logs
2. SQL Server Configuration Manager
3. Windows Application Event Viewer

Reading SQL Server Error Log: The MS SQL Server error log is one of the numerous ways to detect what is happening in your database. You need to execute the given T-SQL statement, which uses the XP_READERRORLOG extended stored procedure to read the SQL Server error log file and to find the location of SQL Server instance.

USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
GO

In the XP_READERRORLOG you can use the below stated parameters.

• Value of error log file: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
• Log file type: 1 or NULL = error log, 2 = SQL Agent log
• Search string 1: String to search for
• Search string 2: String two to search for refining the result
• Search from the start time
• Search to end time
• The sort order for the results: N'asc' = ascending, N'desc' = descending

By default, there are six types of archived SQL Server error logs, which are currently being used.

Using SQL Server Configuration Manager: To view SQL Error log file location via SQL Server Configuration Manager follow the below steps.

• Go to the Start > Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager
• Now, select the SQL Server Services > right click on SQL Server > select Properties from drop down window.
• Then on the Properties window > Advanced > expand the drop down > click on Startup Parameters. Now you can see the location of the SQL Server error log file that is mentioned next to the "-e".

Using Windows Application Event Viewer: To determine SQL error log file location via Windows Application Event Viewer, follow the given steps

• Go to the Start > All Programs > Administrative Tools > Server Manager.
• In Server Manager >Go to the Diagnostics >Click on Event Viewer >Expand the Windows Logs and select Application on the left pane of the window.
• Now you need to filter the events with event ID. To set the filter go to the application and choose Filter Current Log option.
• To view SQL Server error log file location you need to double click on an event and now you can see the error log.

Conclusion

I chose to write this blog because finding the SQL Server Error Log file location is a problem mostly confronted by users. Users are generally not aware, how to check the size of the error log file. In this article, we also have discussed how to determine the location of the SQL Server Error log file.