Home » Blog » SQL Server » SQL Server Error Log – Open, Read, Check, Configure & Manage Easily

SQL Server Error Log – Open, Read, Check, Configure & Manage Easily

view SQL Server error log
author
Published By Aswin Vijayan
admin
Approved By Anuraag Singh
Published On October 4th, 2023
Reading Time 8 Minutes Reading
Category SQL Server

You might have a little knowledge about SQL Server error log files but not in depth. This is what exactly this article is for. Here, users can understand the wider perspective of this topic.

Here, we are going to explore what are SQL logs, their location, how to view or check them, & how can we manage or configure them accordingly. Users might confuse between the error log & usual log which is also known as the transaction file. However, both are totally different from each other.

SQL Server log file is quite distinct. Unlike LDF files, we can say that the error log is made up of events defined by users. Its purpose is to troubleshoot various issues.

What’s SQL Log File & SQL Error Log File

The SQL Logs file or transaction log file is also known as LDF file. These are responsible for storing all the activity log data. On the other hand, the error log data file is crucial for recording the error messages & other significant events for database monitoring & problems troubleshooting purposes. These are generated by the database itself based on the user’s configuration.

Also Read: SQL Undo Delete Command & Its Working

Navigate SQL Server Error Logs Location in the System

The location of this error log is:

C:\ Program Files \ Microsoft SQL Server \ MSSQLn.SQLEXPRESS \ MSSQL \ Log \ ERRORLOG

Here, “n” denotes the server instance ID.

The above-mentioned path is a general path. Now, if we talk about the path of the error SQL logs in Server 2016, the instance ID is 13. Here, the path would be:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

log navigation

Manually Change Location of SQL Server Log Files

When we talk about the manual solution, we are diving the entire process into two phases. Phase one is for starting the SQL Server Configuration Manager which has a total of 3 ways. On the other hand, Phase two is for changing the log path. So, let’s begin with phase one.

Phase: 1. Launching the SQL Server Configuration Manager

1st Way: Simply Go to Start & then Search for SQL Server Configuration Manager

open configuration manager

2nd Way: Users can also use the following path to open SQL Server Configuration Manager with ease & change the location of SQL Server error logs easily:

SQL Server Version Path for SQL Server Configuration Manager
SQL Server 2022 Version C:\ Windows \ System32 \ SQLServerManager17.msc
SQL Server 2019 Version C:\ Windows \ System32 \ SQLServerManager15.msc
SQL Server 2017 Version C:\ Windows \ System32 \ SQLServerManager14.msc
SQL Server 2016 Version C:\ Windows \ System32 \ SQLServerManager13.msc
SQL Server 2014 Version C:\ Windows \ System32 \ SQLServerManager12.msc
SQL Server 2012 Version C:\ Windows \ System32 \ SQLServerManager11.msc
SQL Server 2008 Version C:\ Windows \ System32 \ SQLServerManager10.msc

The below image shows the location path method for 2017 version.

SQL version 2017 path

3rd Way: Press Windows+R to open the run dialog box on your system. Here simply type “compmgmt.msc” & press enter.

command prompt

Simply expand the Services & Applications option to open SQL Server Configuration Manager.

services & applications

Phase: 2. Change the SQL Server Logs Path

Step 1. Navigate to the SQL Server Services in the Configuration Manager.

SQL Server Services

Step 2. Right-Click on SQL Server (SQLEXPRESS) & Click the Properties option.

select properties

Step 3. Click on Startup Parameters >> Choose Existing Parameters & Select Log file.

choose parameters

Step 4. Apart from the prefix & name, users can change anything like a folder, etc. Mention a new path under Specify A Startup Parameter box & then update. 

Step 5. Start the Services again to make the changes active.

How to Check Error Log in SQL Server Using Query

Method:1 Using the T-SQL Query

Simply connect to the SSMS & enter the command “xp_readererrorlog” as shown in the image.

xp_readererrorlog

Method:2 Using the Object Explorer

  1. Open SSMS & Connect to the Server.
    connect to server
  2. Go to the Management Option.
    go to management
  3. Under Management, Navigate to SQL Server Logs option.
    navigate to sql server logs
  4. Right Click SQL Server Logs, Under View, Go to SQL Server Log option.
    click sql server log
  5. Opt for the desired log as SQL Server Agent or Database Mail to learn how to check error log in SQL Server using query.
    select desired log

Automatically Read, Check, & Examine Logs

In order to avoid all the hassles in manual solutions, all that users need to do is just trust the automated solution. SysTools SQL Log Analyzer Tool is the most trusted & experts recommended solution that no other solution can match.

Download Now Purchase Now

The tool is equipped with advanced algorithms & exceptionally amazing features to provide users with detailed results. Moreover, it can even repair & recover corrupted Log file data.

Follow the five simple steps mentioned below after downloading the tool:

Step-1. Launch the Tool & Hit the Open button add LDF files.

click open to view SQL Server error log

Step-2. Select the Online or Offline Mode and Enter the SQL Server Credentials here.

select mode

Step-3. Preview all the Corresponding Files through the left panel.

preview SQL Server error log

Step-4. Apply the Desired Filters for data export or further examination. 

apply filters

Step-5. Finally, Hit the Export button to export the data.

click export

Configure the Number of SQL Server Error Log Files

SQL users must be aware of the fact that SQL keeps the current & six archived log data files by default. Now, for validating the value of these files, just Right-Click on the SQL Server Logs folder in SSMS and then Configure to proceed.

SQL Server logs all data in the current log file & the size does not matter here. However,  this makes it difficult to view them in SSMS, especially in busy instances. Therefore, SQL Server creates a new log file and archives the current one based on the following scenarios:

  • During SQL Service Restart
  • Manual Error Log Recycle

SQL Server permits users to configure the SQL Server Log files from up to 6 to 99 log files. Keep in mind that the number can not go below 6 here. The reason for this is that SQL always keeps at least 6 archived logs.

configure number

To change the default number, users can checkbox the “Limit the number of error log files before they are recycled” as shown in the image.

30 file

Now, the T-SQL query for the same can be specified as

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30
GO
Also Read: Fix SQL Server Error 26 Easily

Configure the Size of SQL Server Error Log File

Now, just like the number, it is also possible for users to set the size of the error log file. To avoid the file being large, heavy, messy & corruption-prone, users should put a limit.

set size

Simply mention the size in KB to limit the error log size based on the requirements. For example, here, we limit the log file size to 1 GB which is equal to 1048576 KB.

1gb size

The corresponding command for the same is:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 1048576
GO

Manual Recycling of SQL Server Error Logs

SQL Server enables manual recycling of error logs which makes the management efficient. It is indeed possible to recycle logs on a regular basis by just increasing the number of log files & using an Agent job alongside. To initiate this process manually, simply run the system stored procedure “sp_cycle_errorlog,” but only users with the sysadmin fixed server role have permission to do so.

EXEC sp_cycle_errorlog
GO

SQL Server Agent Log File

SQL Server Agent also possesses a discrete error log just like the SQL logs case. Follow the below path to find it:

SQL Server Agent >> Error Logs
error logsAs a default function, the Agent Log file logs several warnings & errors. Although, if users want, they can include information messages as well.
  • Warning: The motive of these messages is to aware users of the potential threats. For example, “Job Alpha was deleted while it was running”
  • Error: The motive of these messages is to grab immediate attention of the DBA. For example, being unable to start a mail session will result in error message.

error & warnings

To add information messages, check the options as shown below:

add information message

To manually recycle the SQL Server Agent File, users can simply Right-Click on the Error Log Folder & then Hit the Recycle button.

recycle

Users can also do this with the command:

USE msdb ;  
GO  
EXEC dbo.sp_cycle_agent_errorlog ;  
GO

The current agent error log is archived with a stored procedure using the following process:

  • Creation of a new current agent error log
  • Current Agent error log ABC.out transforms into ABC.1
  • ABC.1 transform into ABC.2
Also Read: Best 5 MDF Viewer Tools for Users

Conclusion for SQL Server Logs Discussion

Now, that we discussed all the aspects of SQL Server error log file, it’s time to end tis blog. Using the above mentioned solutions, users can easily view & check their SQL logs & they won’t face any difficulties. Furthermore, the automated solution for viewing, examining, exporting the files is top notch & this is why even experts recommend that. Therefore users should also consider it without a doubt.