SQL Server Error Log

Author: s | 2025-04-25

This SQL Server tutorial explains, how to View SQL Server Error Logs, How to locate SQL Server error log file location, SQL Server view error log stored procedure, etc. This SQL Server tutorial explains, how to View SQL Server Error Logs, How to locate SQL Server error log file location, SQL Server view error log stored procedure, etc.

Viewing the SQL Server Error Log - SQL Server

Last Updated 10 years agoMost of us get caught up in fragmentation, finding the slowest queries, and looking at new features. We forget the little things that make managing a SQL Server easier – like cylcing the SQL Server error logs.What’s the Error Log?The SQL Server error log is a file that is full of messages generated by SQL Server. By default this tells you when log backups occurred, other informational events, and even contains pieces and parts of stack dumps. In short, it’s a treasure trove of information. When SQL Server is in trouble, it’s nice to have this available as a source of information during troubleshooting.Unfortunately, if the SQL Server error log gets huge, it can take a long time to read the error log – it’s just a file, after all, and the GUI has to read that file into memory.Keep the SQL Server Error Log Under ControlIt’s possible to cycle the SQL Server error log. Cycling the error log starts a new file, and there are only two times when this happens.When SQL Server is restarted.When you execute sp_cycle_errorlogChange everything!When SQL Server cycles the error log, the current log file is closed and a new one is opened. By default, these files are in your SQL Server executables directory in the MSSQL\LOG folder. Admittedly, you don’t really need to know where these are unless you want to see how much room they take up.SQL Server keeps up to 6 error log files around by default. You

View the SQL Server error log in SQL Server

Most applications have a log of some sort that tracks activity. SQL Server is no different. It has a log file known as the error log for tracking what’s happening on an instance of SQL Server. Server. Each instance of SQL Server has its own set of error log files. In this article, I will discuss what the error log is, how SQL Server manages it, and where you can find it. I’ll also show you how to configure the SQL Server error log. What is the error log?The error log is a file that tracks what kind of activity is happening on an instance. The log is just a journal of events that have occurred on a SQL Server instance, in chronological order. The log doesn’t track everything, but it does track significant events and errors that occur while SQL Server is running. These errors could be informational only, warnings, as well as actual instance and application errors. You can find things like start up and shut down information, backup and restore commands, as well as custom application messages in the error log file. A DBA can also configure SQL Server to write additional logging, for example, logins and logouts. The error log is a great place to look for problems or potential problems associated with an instance of SQL Server.The error log is not a single file but a series of files. Each time SQL Server starts up, a new error log file is created. A running instance

Manage the error log - SQL Server

SQL Server (MSSQLSERVER) service entered the stopped state.The Windows system event log shows the following error entry if SQL Server shuts down unexpectedly:Error 3/10/2023 8:37:46 AM Service Control Manager 7034 None The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s).Check the end of the SQL Server error log for clues. If the error log ends abruptly, this means that it was shut down by force. For instance, if SQL Server was terminated by using Task Manager, the SQL Server error report wouldn't reveal any information about any internal problems that might have caused the process to shut down.ResolutionEnsure that authorized database and system administrators have access to the system to minimize unexpected terminations of the SQL Server service. After you examine the event logs, investigate why a service had to be terminated unexpectedly.If a SQL Server internal health issue caused SQL Server to terminate unexpectedly, there might be clues of a possible fatal exception (including a memory dump diagnostic file being generated) at the end of the SQL error log. Review the clues and take the necessary action. If you find a dump file, consider contacting Microsoft SQL Server support, and provide the SQL Server error log and dump file content for further investigation.Lease time-out: An Always On health eventAlways On uses a "lease" mechanism to monitor the health of the computer on which SQL Server is installed. The default lease time-out is 20 seconds.SymptomsHere's a sample output of an Always On lease time-out from the cluster log. You can search these strings to locate a lease time-out in the cluster log.00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Availability Group lease is no longer valid 00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0. 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:35:57.0, 98.068572, 509227008.000000, 0.000395, 0.000350 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:7.0, 12.314941, 451817472.000000, 0.000278, 0.000266 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:17.0, 17.270742, 416096256.000000, 0.000376, 0.000292 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:27.0, 38.399895, 416301056.000000, 0.000446, 0.000304 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:37.0, 100.000000, 417517568.000000, 0.001292, 0.000666For more information about lease time-out, see the Lease Mechanism section in Mechanics and guidelines of lease, cluster, and health check timeouts for Always On availability groups.Diagnose and resolve Always On lease time-out eventsThere are two main issues that can trigger a lease time-out:SQL Server memory dump: When SQL Server detects certain. This SQL Server tutorial explains, how to View SQL Server Error Logs, How to locate SQL Server error log file location, SQL Server view error log stored procedure, etc.

Monitoring the Error Logs - SQL Server

Skip to main content This browser is no longer supported. Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. SQL Server Agent Properties (General Page) Article02/13/2025 In this article -->Applies to: SQL Server Azure SQL Managed InstanceUse this page to view and modify the general properties of the Microsoft SQL Server Agent service.OptionsService stateDisplays the current state of the SQL Server Agent service.Auto restart SQL Server if it stops unexpectedlySQL Server Agent restarts SQL Server if SQL Server stops unexpectedly.Auto restart SQL Server Agent if it stops unexpectedlySQL Server restarts SQL Server Agent if SQL Server Agent stops unexpectedly.FilenameSpecify the file name for the error log....Browse to the error log file.Include execution trace messagesIncludes execution trace messages in the error log. Trace messages provide detailed information on SQL Server Agent operation. Therefore, the log file requires more disk space when this option is selected. This option should only be selected while troubleshooting a problem that may involve SQL Server Agent.Write OEM fileWrites the error log file as a non-Unicode file. This reduces the amount of disk space consumed by the log file. However, messages that include Unicode data may be more difficult to read when this option is enabled.Net send recipientType the name of an operator to receive net send notification of messages that SQL Server Agent writes to the log file.See AlsoOperatorsSQL Server Agent Error Log --> Feedback Additional resources In this article

SQL Server Agent Error Log

IntroductionOne small issue you may not be aware of in SQL Server is it’s default behavior for logging successful database backup messages. By default, every successful database backup logs a message in the SQL Server error log. This happens for all backup types, for all of the databases on the instance. If you have a large number of databases that are doing frequent transaction log backups this can be a problem. This can cause your SQL Server error log to have a lot of information that is not normally very useful. You can alter this behavior with SQL Server Trace Flag 3226.Figure 1: SQL Server Log File ViewerBack in 2007, Microsoft’s Kevin Farlee wrote a blog post about Trace Flag 3226. This trace flag has been in the product since SQL Server 2000, so it has a long history. Despite this, when I ask the audience during many of my presentations, many people have not heard of TF 3226.This is how Microsoft describes this trace flag:By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.This trace flag stops successful database backups from logging a message in the SQL Server error log and the System Event log. Failed database backups will still log a message in the SQL Server error log, which is more important to know about. You can also still query the msdb database to see your database backup history. Enabling SQL Server Trace Flag

SQL Server Error Log Configuration

Then click on the Advanced tab. The location of the error log file directory is identified in the Dump Directory item. To see the full name of the error log file directory, click on the little down error to the right of the Dump Directory item, as shown below in Figure 1.Figure 1: Location of error log file directory in Configuration ManagerA second method to find the location of the error log files is to use SSMS to browse one of the error log files. To do this, you must connect to the instance of SQL Server in which you want to find the error log location with SSMS. Expand the Management item and the SQL Server Logs item. Then double-click on the Current error log file. When you do this, the Log File Viewer will be displayed. To find the error log file location you can either browse thru the log file until you find it or use the Search… option to find it. When using the search option, use the string Logging SQL Server messages in file as the search criteria. The image in Figure 2 shows these steps. The log location can be found in the line that is highlighted in the log.Figure 2: Using SSMS to find the location of error log filesTypes of error log files and their naming conventionsEach SQL Server instance has two different types of error log files. There is an error log file for the currently running instance and then a

Navigating SQL Server Error Logs

Of SQL Server writes to current log (the one created at startup) and by default has six archived error log files. If you need to keep more than six archived files, you can override the default to keep as many as you need (more on this later).If an instance of SQL Server crashes or will not start for some reason, the error log is the place to go for troubleshooting these problems. As a DBA, you should periodically review the error log to look for potential problems. By reviewing the log, you might find some unusual things going on that might otherwise go unnoticed, like a backup job has failed or someone trying to hack the SA password.Where can the error log files be found?By default, the error log files are stored in the following location: Program Files\Microsoft SQL Server\MSSQLn>.\MSSQL\LOG\ERRORLOG, where is an archived version number, and is the name of the instance. This is only the default location. Some instances might be set up to write their error log files to a different location. If the error log files are not in the default location, then there are a number of different ways to find them. I will show you two of those methods.The first method is to use SQL Server Configuration Manager. To find the location of the log using this method, first, open up SQL Server Configuration Manager. Next double click on the instance of SQL Server that you want to locate the error log file location.. This SQL Server tutorial explains, how to View SQL Server Error Logs, How to locate SQL Server error log file location, SQL Server view error log stored procedure, etc.

SQL Server Error Log Reader

Can easily change this. Open up your copy of SSMS and:Expand the “Management” folder.Right click on “SQL Server Logs”Select “Configure”Check the box “Limit the number of error log files before they are recycled”Pick some value to put in the “Maximum number of error log failes” boxClick “OK”It’s just that easy! Admittedly, you have to do this on every SQL Server that you have, so you might just want to click the “Script” button so you can push the script to multiple SQL Servers.Automatically Rotating the SQL Server Error LogYou can set up SQL Server to automatically rotate your error logs. This is the easiest part of this blog post, apart from closing the window.To cycle error logs on a regular basis, restart your SQL Server nightly.Only joking.You can set up a SQL Agent job with a T-SQL step. All it has to do is EXEC sp_cycle_errorlog. Schedule the SQL Agent job to run as frequently as you’d like and you’re good to go. The upside of this approach is that it’s automatic and the SQL Server error logs will be more granular, making it easier to find the error messages you’re looking for.It’s Just That Easy!Cycling the SQL Server error log is easy – you just need a regularly scheduled agent job. Rotating the logs makes it easier to find error messages. Let’s face it – you’re only looking for error messages when there’s a problem. That’s all there is to rotating the error logs. Related

Managing the SQL Server Error Log

Healthy with given HealthCheckTimeout and FailureConditionLevel 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group You can see that there were non-yielding events that occurred at the time of the health check time-out.If non-yielding events are detected, check the cause of the non-yielding event. Consider contacting the SQL Server support team to investigate the non-yielding events.2. Check the SQL Server error logCheck the SQL Server error log for correlating events at the time of the health check time-out. These events might provide "bread crumbs" that suggest further steps to scope the root cause of the health check time-outs.For example, the following log entry shows that a health check time-out occurred in the cluster log:0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group : [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0.In the SQL Server error log, within seconds of the health check time-out, SQL Server reports that it detected severe I/O latency:2021-02-23 20:49:54.64 spid12s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\agdb_log.ldf] in database id 12. The OS file handle is 0x0000000000001594. The offset of the latest long I/O is: 0x000030435b0000. The duration of the long I/O is: 26728 ms.Review the system event log for possible system clues that could be related to the health check time-out event. When you review the Windows system event log, you might find an I/O issue that's reported at the same time for the same health check time-out:02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,,"Reset to device, \Device\, was issued."02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,,"The IO operation at logical block address for Disk 6 (PDO name: \Device\) was retried."SQL Server health: An Always On health eventAlways On monitors different kinds of SQL Server health events. While it hosts an availability group primary replica, SQL Server continuously runs sp_server_diagnostics that reports on SQL Server health by using different components. When any health problems are detected, sp_server_diagnostics reports an error for that particular component, and then sends the results back to the Always On health detection process. When an error is reported, the Availability Group role shows the failed state and possible failover if the availability group is configured to do this.SymptomsHere's an example of a SQL Server health issue as reported by sp_server_diagnostics in the cluster log. SQL Server reports an "error" state in the system component to Always On health monitoring, and the "contoso-ag" availability group is transitioned to a failed state.NoteA SQL Server health issue generates a similar report to that of health check time-out. Both health events report Availability Group is not. This SQL Server tutorial explains, how to View SQL Server Error Logs, How to locate SQL Server error log file location, SQL Server view error log stored procedure, etc. This SQL Server tutorial explains, how to View SQL Server Error Logs, How to locate SQL Server error log file location, SQL Server view error log stored procedure, etc.

Viewing the SQL Server Error Log - SQL Server

Keep in mind, having an unlimited size setting means each log file will be different sizes, whereas limiting the size would make every error log file the same size.Issues with multiple restartsWhen SQL Server is restarted, the oldest archived error log file is deleted, and a new one is created. This cycling of the error log every time SQL Server restarts normally isn’t a problem, unless it deletes an archived error log file automatically that contains information that is needed for troubleshooting or trend reporting.Since the log file contains messages and errors related to an instance, it is crucial not to lose this information until you no longer need it. The problem with having the default number of archived error log files is you might lose valuable troubleshooting information if an instance is restarted more than six times over a short period of time. This can happen for a number of different reasons. I have seen nighttime operations staff and DBAs restart an instance multiple times in an attempt to get an instance that has crashed back up and running. Suppose an instance of SQL Server is restarted multiple times without review of the error log files. The restarts might inadvertently cycle off the one critical error log file containing the error messages related to the crash. For this reason, it is important to configure SQL Server to retain enough archived error log files so they are not automatically deleted until they are no longer needed.Keep in mind that cycling