Лог ошибок ms sql server

This article covers an overview of SQL Server logs for monitoring and troubleshooting issues in SQL Server.

Introduction

The logs are the best resources for a database administrator in troubleshooting any issues. These issues can be related to server configuration, startup, recovery, performance, trace flags, deadlocks, IO, or Checkpoint delay. For
example, suppose your SQL Server instance restarted due to unknown reasons, and after startup, SQL Services are up;
however, your application cannot access the database. Therefore, to investigate issues, you can look at the latest
SQL Server logs and monitor the database recovery process and estimated time in completion.

The database administrator can also configure SQL Server for additional logging into the error logs. For example, we can enable a trace flag to capture deadlocks information. The DBA should review these logs proactively to look for potential problems. You can identify information such as backup failure, login failure, IO errors by reviewing logs. These error logs are great to look for existing or potential problems in SQL Server instances.

SQL Server logs are known as SQL Server Error logs. This error log has informational, warning, and critical error messages. You can view a few of these logs in the Windows event viewer logs as well. However, it is recommended to use SQL Server logs to get detailed information.

SQL Server Logs and its location

Once you connect to a SQL Server instance in SSMS, navigate to Management -> SQL Server Logs. As shown below, it
has the current log and six archive logs ( Archive#1 to Archive #6).

Method 1: Using the xp_readerrorlog extended procedure

The current logs are the latest error log file, and you can use them to view recent activity since SQL Server starts
or manual log file recycling. SQL Server error log is a text file stored in the log directory of SQL Server
instance. You can use the extended procedure xp_readerrorlog to find out the current location of
the error log.

This query has the following parameters:

1. Error log file: value 0 for the current , 1 for Archive#1, 2 for Archive #2
2. Logfile type: Value 0 for SQL Server error log, 1 for SQL Server Agent log
3. Search string1
4. Search string 2
5. from time
6. end-time
7. Sort results – Ascending (N’ASC) or descending (N’Desc)

For my demo instance, the error log file location is C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Method 2: Using the SERVERPROPERTY() function

We can query the SERVERPROPERTY function as well to identify the location of the SQL Server ERRORLOG.

Method 3: Using the SQL Server Configuration Manager

Alternatively, open SQL Server Configuration Manager and view startup parameters. The log file location is specified
using the -e switch.

You can browse the log directory and view the current archive error log files. These error logs can be opened in a
text editor such as Notepad or Visual Studio Code.

Configure the number of SQL Server Log files and their size

By default, SQL Server maintains a current and six archived log files. To validate the value, right-click on the SQL
Server Logs folder in SSMS and Configure.

SQL Server logs all information in the current log file irrespective of log file size. On a busy system or instance
with many errors, you might find it challenging to view the log file in SSMS. SQL Server creates a new log file and
archives the current file in the following case.

• During SQL Service restart
• Manual error log recycle

However, if you restart servers frequently due to unknown reasons, you might lose all historical archive log data because it maintains only six archive logs. Since the error contains valuable information that can help you troubleshoot, we might not want to lose this crucial data. Instead, we might want to retain log files on a critical production system for a week or even a month.

SQL Server allows the configuration of up to 6 to 99 error log files. We cannot specify a value less than six because, in any case, it maintains six archive error logs.

To change the default number of error log files, put a check on the box labeled – “Limit the number of error
log files before they are recycled”. For example, the following screenshot shows a maximum of 30 error
log files.

Its equivalent T-SQL script uses xp_instance_regwrite extended stored procedure and updates the registry value.

• Note: SQL Services must be restarted so that these changes are in effect

As stated earlier, by default, the error log size is unlimited. For example, if you do not start SQL Server for a
longer period and do not manually recycle log files, this file will grow huge. Therefore, in the error log
configuration, value 0 shows log size is unlimited.

You can specify the size in KB to limit the error log size as per your requirement. For example, here, we limit log file size to 1 GB

Its equivalent T-SQL script updates the ErrorLogSizeInKb in the SQL Server registry.

Manual recycle error logs

SQL Server allows recycling error logs manually to manage them effectively. For example, suppose you increased the number of error log files to 30. Thus, we can create a SQL Server agent job that recycles error logs at midnight. This way, we have an error log file for each day unless SQL Service is restarted in between. To recycle manually, execute the system stored procedure sp_cycle_errorlog. The user with the sysadmin fix server role can execute this stored procedure.

SQL Server Agent log file

SQL Server agent also has a separate error log similar to the SQL Server logs. You can find it under the SQL Server Agent – > Error logs folder.

Right-click on the Error log folder and Configure. It gives the agent error log location and agent log level.

The agent log file extension is *.OUT and stored in the log folder as per default configuration. For example, in my
system, the log file directory is C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT.

By default, agent log file logs errors and warnings; however, we can include information messages:

• Warning messages: These messages provide information about potential problems. For example, “Job X was deleted while it was running”
• Error message: It gives information that requires immediate intervention of a DBA, such as being unable to start a mail session

To add the information message, put a tick on the checkbox labeled Information.

SQL Server uses up to 9 SQL Server agent log files. The current file name is SQLAGENT.OUT. The file with extension .1 indicates the first archived agent error log. Similarly, an extension .9 indicates the 9th (oldest) archived error log.

SQL Server agent log files are recycled each time SQL Server Agent is restarted. To do it manually, right-click on
the Error Logs folder and Recycle.

Alternatively, use the stored procedure sp_cycle_agent_errorlog to recycle SQL Server agent log files manually.

The stored procure archives the current agent error log using the following process:

• A new current agent error log is created
• Current Agent error log SQLAgent.out coverts to SQLAgent.1
• SQLAgent.1 converts to SQLAgent.2

Conclusion

The SQL Server error log file has information, warning, and critical messages of an instance. It is helpful for troubleshooting, audit logins (success, failure). The database administrator can configure the number of required archived error logs and directories to store these files.

You should proactively look for messages getting logged as part of your daily and weekly health check reports.

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 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\MSSQL<n>.<instance name>\MSSQL\LOG\ERRORLOG, where <n> is an archived version number, and <instance name> 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. 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 Manager

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

Types of error log files and their naming conventions

Each 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 number of archived error log files.

All error log files names start with ERRORLOG. The error log for the running instance is called just ERRORLOG, whereas all other error log files names start with ERRORLOG but have a number for the file extension. The archived error log files are ERRORLOG.1, ERRORLOG.2, ERROLOG.3, … up to the number to the configured number of archived error log files. Where ERRORLOG.1 is most recently archived error log file, ERRORLOG.2 is the second most recently archived error log file, ERRORLOG.3 is the third most recently archived error log file, etc. If an instance is configured with the default number of error log files, then the last archived error log file is named ERRORLOG.6.

When an instance is restarted, the oldest archived error log file (ERRORLOG.6 if the default number is used) is deleted, then each of the remaining error log files is renamed. ERRORLOG.5 is renamed to ERRORLOG.6, ERROLOG.4 is renamed to ERRORLOG.5, etc. until ERRORLOG.1 is renamed to ERRORLOG.2. The last current error log file (ERRORLOG) is renamed to ERRORLOG.1, and the error log file for the newly started instance is created with the name ERRORLOG.

Changing the number and size of the error log

By default, an instance of SQL Server retains six archived error log files, and the size of each log file is unlimited. An unlimited size means it will grow as big as it needs to be, or until it runs out of disk space. You may find that these default setting are fine, but they can also be overridden.

How many archived error log files do you need and what is an appropriate maximum size? Like most SQL Server questions, the answer is “it depends.” Here, I will demonstrate how the number and size of the error logs might help or hinder your use of the error log files and share my personal opinion of how many log files I like to have.

First, let me explore the number of archived log files. I like to retain at least two months’ worth of error log files. By having multiple months of error log information, I can track trends from one month to the next. Keep in mind that depending on what is logged and the activity of an instance, two months’ worth of error log files might take up a lot of disk space.

There is no easy way to keep exactly two months’ worth of error log files since a new error log is created and the oldest archived one is deleted every time an instance is restarted. The number of months of error log files depends on how often SQL Server is restarted and the number of archived error log files that are kept. Suppose an instance is recycled frequently over a short period of time due to some maintenance activity. Error log information will be cycled off based on the configured value for the number of archived error log files that should be retained.

There a few different methods to change the error log file configuration, I’ll mention two of them. The first method is to use SSMS. To use this method, first connect to the instance, expand the Management folder, right click on the SQL Server Log folder and then select the Configure item from the menu displayed, as shown in Figure 3.

Figure 3: Bringing up the error log configuration screen

When clicking on Configure, the window shown in Figure 4 is displayed.

Figure 4; Configuration Options for the Error Log

By reviewing Figure 4, you can see that my instance is currently configured to retain 6 archived error log files and the maximum size of the error log file is set to be 0, which means unlimited.

To change the number of archived error log files to retrain, first click on the checkbox labeled Limit the number of error log files before they are recycled. Doing this will enable the Maximum number of error log files option, so the number of error logs to retain can be changed. Note the number of error log files to retrain needs to be between 6 and 99.

To set a maximum limit of each error log file, enter the desired size into the Maximum size of the error log file in KB. When the error log configuration is set to limit the size of the error log file, a new error log file will be created automatically after the current log file reaches the maximum size. I personally don’t like to limit the size of the log file, but 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 restarts

When 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 off a critical error log file is a rare situation. However, if an error log file that has valuable information about a crash is cycled off, it does hamper your troubleshooting options. Therefore, it is wise to have someone review the error log, or at least copy the error log file to a backup location after a crash to make sure you have all the log files to help with troubleshooting. At least by copying off the error log file after a crash, you can guarantee it will not be cycled off if lots of restarts are performed.

Cycling the ERRORLOG

A new error log is created every time SQL Server is restarted. Sometimes SQL Server stays up for weeks or even months before an instance is restarted. When an instance has been running for a long time, the error log will be quite large. It’s hard to review and find errors in large log files, especially when they contain error messages that could cover long periods of time.

To help keep the error log files from spanning months’ worth of messages without recycling, you can cycle the error log with a command. Cycling the error log will close the current log and create a new one. The code in Listing 1 uses the system stored procedure sp_cycle_errorlog to cycle the error log file.

Listing 1: Cycling the errorlog.

To execute the sp_cycle_errorlog system stored procedure, you need to be a member of the sysadmin fixed server role. Stored procedure sp_cycle_errorlog takes no parameters and returns a 0 if the error log is successfully recycled and a 1 if the cycling of the log has failed.

Error log file

The error log file contains messages and errors associated with a SQL Server instance. It is a valuable tool for troubleshooting problems with SQL Server as well as auditing what goes on within an instance of SQL Server. A DBA can configure SQL Server to store the error logs wherever they’d like and adjust the configuration to keep as many archived error log files as needed. As a DBA you should review this log every time SQL Server crashes and as part of your daily and weekly system checks. This review will ensure that SQL Server is functioning normally, with no unexpected errors showing up in the log.

How to Open, Read & Check MS SQL Error Log File – Ultimate Tutorial

The SQL Server Error Log sounds like SQL Transaction Log, which keeps a record of database transactions and ensures database integrity. Right? Unfortunately, Error Log file is not like that.

Topics To Be Covered

• What is Inside SQL Server Error Log ?
• Where is SQL Server Error Log Located ?
• How to Change Error Log File Location in SQL Server?
• How to Read SQL Error Log?

SQL Error Log files are very much different from SQL Server LDF. It majorly comprises User-defined events and is used to troubleshoot system problems. In this technical guide, we are going to provide complete information about Microsoft SQL Server Error Log File.

Let’s get started!

Where is MS SQL Server Error Log Stored?

You can easily find the SQL Error Log location for your dedicated SQL Server version. All you need to use the following path to access it.

Drive:\ Program Files \ Microsoft SQL Server \ MSSQLV.SQLEXPRESS \ MSSQL \ Log \ ERRORLOG

It is a general path that provides you SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 error log file location. For example, it is the SQL Server 2016 Error Log file location in Windows 10.

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

How to Change Error Log File Location in MS SQL Server 2017/ 2016/ 2014/ 2012

Sometime SQL users need to update the path of an SQL Error log file. The overall procedure is classified into two stages.

Stage 1: Start the SQL Server Configuration Manager

One can open the Configuration Manager with different techniques. All of them are mentioned below.

Method #1 – To open Configuration Manager, you can type the SQL Server Configuration Manager in the search box of Start and open it as shown in the screenshot.

Method #2 – Else, you can use the following path to open SQL Server Configuration Manager.

Here, we opened the SQL Server Configuration Manager with the above–mentioned location in SQL Server 2017.

Method #3 – You can also open the Run dialog box and type ‘compmgmt.msc’ in the box.

From the following screen, expand the Services and Applications section and get the SQL Server Configuration Manager.

Stage 2: Change the SQL Server Error Log Path

Step-1. Under the SQL Server Configuration Manager, go to the SQL Server Services option.

Step-2. Perform right-click on SQL Server(SQLEXPRESS) as shown in the screenshot and choose the Properties option.

Step-3. From the SQL Server(SQLEXPRESS) Properties, click on the Startup Parameters tab. Afterward, under the Existing parameters section, choose the log file whose location you want to change.

Step-4. The starting prefix (like e,d) and the current Error Log file Name will not change. Rest of the folder can be changed. Mention the new path under the Specify a startup parameter box and update it.

Step-5. The changes to SQL error logs location are saved but they are not active. For this. You need to start the SQL Server Services.

Methods to Open SQL Server Error Log Files

There are two ways through which one can view the Error Log. Now, whenever, the SQL Server could not open Error Log file, then use any of the below-mentioned techniques. Also, users need to know that these methods are not advised to how to check error log in SQL Server database.

Approach #1: Use T-SQL to Read SQL Error Log

Connect with the SSMS and type ‘xp_readererrorlog’ in the panel. It is a SQL Server query to read Error Log.

Approach #2: Use Object Explorer Option

Without the help of T-SQL, you can also view the error log. Finally, let’s check out how to read SQL Server Error Log.

Step-1. Open Microsoft SQL Server Management Studio on your system.

Step-2. Time to connect with Object Explorer. Open Connect to Server Window and select the Server name. Afterward, choose the Authentication Type and click on Connect.

Step-3. Go to the Management section from the Object Explorer in order to read SQL Error Log.

Step-4. Expand the Management Section and go to the SQL Server Logs.

Step-5. Right-click on SQL Server Logs option. Go to the View option and select SQL Server Log from the menu to view errorlog SQL Server database.

Step-6. The Log File Summary will appear on the screen. From the same window, you can opt for different Log – SQL Server Agent, Database Mail.

That’s all about Microsoft SQL Server Error Log.

Automated Solution to Read, Examine & Learn How to Check Error Log in SQL Server

Well, the manual method works, but not all the time. The success ratio here is quite low. Evidently. experts recommend users opt for an advanced automated solution. Evidently, this is exactly why experts recommend the automated solution.

Moreover, if you want to examine the SQL Transaction Log file, then you can try SysTools LDF File Reader Software. Download this tool & then simply follow the below instructions to read & examine your SQL error log files.

Download Now Purchase Now

Step-1. Launch the Software & then Click on the Open button to add LDF log files.

Step-2. Select the Online or Offline mode & Enter SQL Server Details in the dialog box.

Step-3. Preview the Files that you are concerned with by selecting them from the left panel.

Step-4. Apply Filters if you want to Export the records for further examination.

Step-5. Click on the Export button to get the records on your preferred location.

Automated Tool Features for SQL Server Logs

The automated tool is way better than the manual one because of several factors. Let’s have a look at these features in detail that makes this utility special.

• Execute complete analysis of SQL transaction log files without hassles.
• Recover & repair corrupt SQL LDF files as well to restore the database files.
• Analyze all transactions like Insert, Update, and Delete with advanced features.
• Easily examine the files without any need for the SQL Server application.
• Supports data types: Datetime2, datetimeoffset, sql_variant, hierarchyid, geometry & geography.
• Microsoft SQL Server 2019, 2017, 2016, 2014, 2012, 2008 & SQL Server 2005 LDF are supported here.

How to Check Error Log in SQL Server with T-SQL Method Manually?

We can also use the SERVERPROPERTY() command to get the location of an error log file in SQL Server at the moment. However, while executing the queries, users need to be very careful. This is because these commands can be risky & might result in data loss.

 SELECT SERVERPROPERTY('Name_of_Error_Log_File') AS 'Error log file location';

After executing the queries, users can browse in the directory to find the records. They can see the error logs there without any hassles.

• ErrorLog: Error log file at the moment.
• ErrorLog.1: Error log file 1 of the archive
• ErrorLog.2: Error log file 2 of the archive

Drawbacks of the Manual Method to View SQL Server Logs

Now, these manual solutions are full of shortcomings that can be highly dangerous for users’ log files. We are aware of how crucial a Log data file in SQL Server is. How it helps in tracking all the transactions & the user who executed that is what we learn from it.

Let’s quickly have a look at the manual method drawbacks in depth:

Very Poor Interface: The interface of the manual solutions is very poor. The T-SQL commands are not interactive at all & spoils user experience. Moreover, when we talk about the SSMS Method, it’s not also that good.

No Technical Expertise: These manual solutions require more technical proficiency than just the basic knowledge. This makes this task more complex & hard to execute. Also, it’s true that users might be needing SQL experts’ assistance for such methods.

Lack of Customization Features: For every single task, there is a long command. There are no features for getting the customised results just like what users need. Moreover, users must understand that they can’t get the exact results as expected everytime.

Security Standards: There is no protective layering if users are opting for such manual methods. However, the automated solution keeps the original file intact to avoid any corruption or data integrity issues.

Interrupts Business Hours: To find the errorlog SQL Server with manual solutions, users need to be prepared for long time consuming tasks. This can reduce efficiency & even interrupt the usual working hours of the business.

Conclusion

Finally, after understanding all the core concepts of SQL Server logs carefully, it’s time for us to conclude this article. Also, we know the best ways as well to open, read & check SQL Server error logs in a smart manner.  The above-mentioned manual & automated solutions are good enough for users to get the desired results they want.

However, when we dig deep, it’s very easy for us to say that manual solutions are a bit complex. Therefore, as per experts’ opinion, the automated tool is the only reliable option left which makes the task hassle-free.

Frequently Asked Questions –

Q-5. How to create an error log in SQL Server?

Ans: Follow the instruction as mentioned carefully:

• Go to Object Explorer & Expand Database.
• Navigate to Expand Management.
• Right Click SQL Server Logs here.
• Now Click on Configure to create errors.

The SQL Server Error Log is one of the most useful thing to troubleshoot database server issues. System errors get logged here along with a host of other useful information.

Read on to see how to understand, analyze, and manage the SQL Server Error Log…

Why Should You Care About Reading This Log?

So why should you care at all about the error log? While it is true that the Windows Events Logs contains many useful bits of information, the more specific errors get written to the SQL Server error log. Are you troubleshooting issues on your SQL Instance? If so then reading and understanding this log will immensely help.

The SQL Server Error Log:

• Wealth of information about the SQL Instance
• A great source of troubleshooting information
• The answers are often in the logs – you just have to read them

RTFM – The Truth is Out There!

There is a very old retort that has been around since the early Unix days for when n00bs ask questions on message boards: RTFM. Developers can be harsh spoken and system administrators can be thick skinned. Learning a programming language, for example, is a tough task. There become a lot of elementary questions asked and some people get turned off by the curt responses. The truth of the matter is that there is no substitute for reading the documentation.

Professionals who contribute to forums don’t want to do your homework or provide free consulting for such basic things. You really need to put in some work first and then present what you’ve got and ask your question.

There is no substitute for reading logs to understand behavior

Reading logs is not sexy. For non-robots it can be boring and tedious. However the SQL Server error log is a goldmine of useful information to troubleshoot your SQL Server Instance. Are you getting crash dumps? Deadlocks? Connectivity issues? Services crashing? Corruption? All of these can be discerned from analyzing the error log. You would do well to have this skill!

Here’s my favorite xkcd comic showing what happens when you don’t read the documentation:

Anatomy of SQL Server Error Log

Now let us examine the parts which comprise the error log. 

There are a few ways to find and view the error log.

1. SSMS
2. T-SQL
3. File System

SQL Server Management Studio

In SSMS you can find it in the object browser. Here it is:

From here you can double click on the current error log and open it up. You will see something like this:

You can filter the log as such:

The output will look like this:

Go ahead and export to a file. Play around with it and get familiar with reading these logs.

T-SQL

SQL Server 2005 introduced a new undocumented command: sp_readerrorlog. This allows us to query the error log which can be powerful.

A word of caution – undocumented means Microsoft can change or remove it anytime and does not provide support for this command.

It is a wrapper around an extended procedure called xp_readerrorlog. You can know this by querying:

Output:

Here are some examples of querying the error log:

File System

My preferred method of accessing and analyzing the error log is by way of the file system.

How do we know where the file is? By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. Sometimes a DBA will move it and we need to look harder. You can always just search the file system in Windows Explorer for “ERRORLOG”.

Using the SQL Server Configuration Manager:

You see the services running for SQL Server. Right click on the service for your instance and look at the startup parameters. This is the path where your log files are likely to be.

Additionally you can query the log location in T-SQL:

Structure of Error Log

Besides errors you can learn a lot about an instance by reading some of the beginning lines.

Just by reading the first 35 lines I can discern:

• The exact version and build of SQL Server
• The OS version and build
• Time zone
• Authentication mode
• Default locations for log files
• CPU recognized
• Memory allocated
• Collation
• IFI

If you knew nothing about a server this can tell quite a lot!

How to Manage the Error Log

There are 2 options to be aware of when managing the error log:

1. Cycling the log
2. Controlling the size of the log

By default the error log will create 7 log files to cycle through. When you add an eighth it drops the oldest one and renames the rest accordingly. The first “ERRORLOG” is the current one always.

Every time the SQL Server service is restarted a new error log file is created

Cycling the Error Log

We can cycle the error logs (meaning create a new one and push the last out of the queue) by using sp_cycle_errorlog:

Here are the log files before running the command:

…and after:

NOTE: I accidentally ran it twice – that is why there are 2 new logs created.

Under the covers this command runs dbcc errorlog.

Controlling the Size of the Error Log

To change the size that an error log file can be you need to make a change in the registry. The Microsoft documentation shows the key we are interested in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs

PROTIP: Always back up the registry BEFORE making any changes!

Understanding the Error Log

Some of these logs can get very big and appear hard to analyze. You need to know the important bits to examine and search. I like to do it this way:

• Find and open the Error Log in Notepad++ (or your favorite text editor)
• Ctrl + F to enter a search term
• Click “Find All in Current Document”

Now you can easily analyze the file. Double click on the line in the output to go straight to it in the file.

Notice you can use regular expressions – they are your friend when searching text.

I like to search out the following terms on the regular:

• severity:
• crash
• dump
• deadlock
• victim
• fatal

Searching on “severity:” tends to show useful output

Don’t just search for “error” because you will get a lot of false positives that you may not care about. I have the best luck with “severity:” because I can evaluate what is important and it reduces noise.

Well there you have it. Get your hands dirty in the log and you will level up your skills.

Thanks for reading!

If you liked this post then you might also like: Stop and Think Before You Shrink – Shrinking a SQL Server Database

Пересказ статьи Rajendra Gupta. How to manage SQL Server logs effectively

В статье дается обзор журналов SQL Server для управления и устранения неполадок на сервере.

Введение

Журналы являются лучшим средством администратора баз данных при решении любых проблем. Эти проблемы могут быть связаны с конфигурацией сервера, запуском, восстановлением, производительностью, флагами трассировки, тупиковыми ситуациями, вводом-выводом или задержками. Предположим, например, что ваш экземпляр SQL Server перезапускается по непонятным причинам, и после перезапуска службы SQL работают, однако ваше приложение не имеет доступа к базе данных. Таким образом, для исследования проблемы вам нужно заглянуть в последний журнал SQL Server, чтобы проконтролировать процесс восстановления базы данных и узнать оценку времени его завершения.

Администратор базы данных может также сконфигурировать SQL Server для выполнения дополнительных записей в журналы ошибок. Например, мы можем включить флаг трассировки для захвата информации о тупиковых ситуациях. DBA должен регулярно просматривать эти журналы в поисках потенциальных проблем. Вы можете обнаружить в журналах такую информацию, как сбой резервного копирования, ошибки входа, ошибки ввода-вывода. Эти журналы ошибок являются отличным средством для обнаружения существующих и потенциальных проблем в экземплярах SQL Server.

Журналы SQL Server известны как SQL Server Error logs. Журналы ошибок содержат информационные сообщения, предупреждения и сообщения о критичных ошибках. Вы можете просматривать некоторые из этих журналов также в просмотрщике событий Windows. Однако рекомендуется использовать журналы SQL Server для получения подробной информации.

Журналы SQL Server и их местонахождение

Если вы подключены к экземпляру SQL Server в SSMS, перейдите к Management -> SQL Server Logs. Как показано ниже, имеется текущий журнал и шесть архивных журналов (Archive#1 — Archive #6).

Метод 1: Использование расширенной процедуры xp_readerrorlog

Текущие журналы являются самыми последними файлами журнала ошибок, и вы можете использовать их для просмотра недавней деятельности с момента запуска SQL Server или ручного перезапуска файла журнала. Журнал ошибок SQL Server является текстовым файлом, хранящимся в каталоге журналов экземпляра SQL Server. Вы можете использовать расширенную процедуру xp_readerrorlog для нахождения текущего местоположения журнала ошибок.

USE master

GO

xp_readerrorlog 0, 1, N'Logging SQL Server messages', NULL, NULL,NULL

GO

Этот запрос имеет следующие параметры:

1. Файл журнала ошибок: значение 0 для текущего, 1 для Archive#1, 2 для Archive #2.
2. Тип файла журнала: значение 0 для журнала ошибок SQL Server, 1 для агента SQL Server.
3. Строка поиска 1
4. Строка поиска 2
5. Время от
6. Время до
7. Сортировка результатов — по возрастанию (N’ASC) или по убыванию (N’Desc)

Для моего демонстрационного экземпляра файл журнала ошибок находится в папке C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Метод 2: Использование функции SERVERPROPERTY()

Мы можем использовать в запросе функцию SERVERPROPERTY, и также определить местонахождение SQL Server ERRORLOG.

SELECT SERVERPROPERTY('ErrorLogFileName')  AS 'Error log location'

Метод 3: использование менеджера конфигурации SQL Server

Откройте SQL Server Configuration Manager и посмотрите параметры запуска. Местоположение файлов журнала указывается с помощью переключателя -e.

Вы можете развернуть каталог журналов и просмотреть текущий или архивные файлы журнала. Эти журналы ошибок можно открыть в текстовом редакторе, таком как Notepad или Visual Studio Code.

Конфигурирование числа файлов журнала SQL Server и их размеров

По умолчанию SQL Server поддерживает текущий и 6 архивных файлов журнала. Чтобы уточнить значение, выполните щелчок правой кнопкой на папке SQL Server Logs в SSMS и выберите Configure.

SQL Server записывает всю информацию в текущий файл журнала, независимо от размера файла журнала. В загруженной системе или в экземпляре с большим количеством ошибок вам может быть сложно просмотреть файл журнала в SSMS. SQL Server создает новый файл журнала и архивирует текущий файл в следующих случях.

1. При перезапуске службы SQL.
2. При перезагрузке журнала ошибок вручную.

Однако если вы часто перезапускаете серверы по неизвестным причинам, то можете потерять все исторические данные в архивных журналах, поскольку их поддерживается только шесть. Поскольку ошибки содержат ценную информацию, которая может помочь в решении проблем, вы можете не захотеть потерять эти важные данные. Тогда, возможно, вы захотите сохранять файлы журнала производственной системы в течение недели или даже месяца.

SQL Server позволяет сконфигурировать от 6 до 99 файлов журнала ошибок. Вы не можете указать значение меньше шести, поскольку в любом случае будет поддерживаться шесть архивных журналов ошибок.

Для изменения значения по умолчанию числа файлов журнала ошибок поставьте галочку в поле с названием “Limit the number of error log files before they are recycled”. Например, следующий скриншот показывает максимальное число файлов журнала ошибок, равное 30.

Это эквивалентно выполнению скрипта T-SQL, который использует расширенную хранимую процедуру и обновляет значение регистра.

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30

GO

Замечание. Следует перезапустить службу SQL, чтобы изменения вступили в силу.

Как утверждалось ранее, по умолчанию размер журнала ошибок не ограничен. Например, если вы не запускаете SQL Server в течение длительного периода и вручную не перегружаете файлы журнала, этот файл вырастет до громадных размеров. Поэтому в конфигурации журнала ошибок показано значение 0, соответствующее неограниченному размеру журнала.

Вы можете задать размер в Кб для ограничения размера журнала ошибок в соответствии с вашими требованиями. Например, здесь мы ограничиваем размер файла журнала в 1Гб.

Эквивалентный скрипт T-SQL обновляет ErrorLogSizeInKb в регистре SQL Server.

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 1048576

GO

Перезагрузка журналов ошибок вручную

SQL Server позволяет вручную перегружать журналы ошибок для эффективного управления ими. Например, предположим, что вы увеличили число файлов журнала ошибок до 30. Тогда мы можем создать задание для агента SQL Server, который перегружает журналы ошибок в полночь. Тем самым мы имеем файл журнала ошибок на каждый день, если SQL Server не будет перезапущен в этом промежутке. Для перезагрузки вручную выполните системную хранимую процедуру sp_cycle_errorlog. Эту процедуру может выполнить пользователь с фиксированной серверной ролью sysadmin.

EXEC sp_cycle_errorlog

GO

Файл журнала SQL Server Agent

Агент SQL Server также имеет отдельный журнал ошибок, подобный журналам SQL Server. Вы можете обнаружить его в папке SQL Server Agent – > Error logs.

Щелкните правой кнопкой на папке Error log и выберите команду Configure. Это даст местоположение журнала ошибок агента и уровень журнала агента.

Файл журнала агента имеет расширение *.OUT и хранится в папке log при конфигурации по умолчанию. Например, в моей системе файл журнала находится здесь: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT.

По умолчанию в файл журнала записываются ошибки и предупреждения; однако мы можем включить информационные сообщения:

• Предупреждения: Эти сообщения предоставляют информацию о потенциальных проблемах. Например, “Job X was deleted while it was running” (задание Х было удалено во время выполнения).
• Сообщение об ошибках: оно дает информацию, которая требует немедленного вмешательства администратора баз данных, например, невозможность почтового сеанса.

Чтобы добавить информационное сообщение, поставьте галочку в поле Information.

SQL Server использует до 9 файлов журнала агента SQL Server. Имя текущего файла SQLAGENT.OUT. Файл с расширением .1 указывает на первый архивный журнал ошибок агента. Аналогично расширение .9 указывает на 9-й (самый старый) архив журнала ошибок.

Файлы журнала агента SQL Server перегружаются всякий раз, когда перезапускается SQL Server Agent. Для того, чтобы сделать это вручную, выполните щелчок правой кнопкой на папке Error Logs folder и выберите Recycle.

Или используйте хранимую процедуру sp_cycle_agent_errorlog для перезагрузки файлов журнала агента SQL Server вручную.

USE msdb ;  

GO  

EXEC dbo.sp_cycle_agent_errorlog ;  

GO

Хранимая процедура архивирует текущий журнал ошибок агента, используя следующий процесс:

• Создается новый текущий журнал ошибок агента.
• Текущий журнал ошибок SQLAgent.out преобразуется в SQLAgent.1.
• SQLAgent.1 преобразуется в SQLAgent.2

Заключение

Файл журнала ошибок SQL Server содержит информацию, предупреждения и критические сообщения экземпляра. Это полезно для решения проблем, аудита входа (успешно, отказ). Администратор базы данных может сконфигурировать требуемое число архивных журналов ошибок и каталогов для хранения этих файлов.
Вы должны регулярно просматривать записи в журнале в качестве подготовки ежедневных или еженедельных отчетов о состоянии сервера.