SQL Server Error Log

Author: p | 2025-04-24

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

View the SQL Server error log in 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. 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.

Manage the error log - SQL Server

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

Monitoring the Error Logs - SQL Server

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

SQL Server Agent Error Log

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

SQL Server Error Log Configuration

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