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

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.

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

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

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.

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

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

Phase: 2. Change the SQL Server Logs Path

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

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

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

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.

Method:2 Using the Object Explorer

1. Open SSMS & Connect to the Server.
   
2. Go to the Management Option.
   
3. Under Management, Navigate to SQL Server Logs option.
   
4. Right Click SQL Server Logs, Under View, Go to SQL Server Log option.
   
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.
   

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.

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

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

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

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

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.

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.

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

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.

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.

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
As 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.

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

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

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

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.