Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of the error log errorlog in sql server. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

One. Overview

SQL Server logs certain system events and user-defined events to the SQL Server error log and the Microsoft Windows application log. Both logs automatically time-stamp all recorded events. Using the information in the SQL Server error log can solve problems related to SQL Server.

Viewing the SQL Server error log ensures that processes, such as backup and restore operations, batch commands, or other scripts and processes, complete successfully. This feature can be used to help detect any current or potential problem areas, including automatic recovery messages (especially if the SQL Server instance has been stopped and restarted), kernel messages, or other server-level error messages.

You can view the SQL Server Management Studio error log using SQL Server or any text editor. By default, the error log is located in the Program Files\ Microsoft SQL Server\ MSSQL.n\ MSSQL\ LOG\ ERRORLOG and ERRORLOG.n files. For example, the sql server 2008 R2 database is installed on my computer win7 in D:\ Program Files directory, and the error log path is D:\ Program Files\ Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\ Log

Whenever a SQL Server instance is started, a new error log message is created, and the sqlserver system automatically calls the exec sp_cycle_errorlog system stored procedure, which closes the current error log file and circulates the error log extension number to recycle the error log file without having to restart the SQL Server instance. (there are four log files under mysql, and the files will grow all the time and need to be cleared by the administrator Avoid affecting disk space). Normally, SQL Server keeps backups of the first six logs, in chronological order, with the file extension. 1. 2... .6 means. Each time the service is restarted, the file extension is incremented, and the earliest one is deleted.

two。 Errorlog log content

2.1 the information automatically recorded in the log is as follows:

(1) the startup parameters of SQL SERVER, as well as authentication mode and memory allocation mode.

(2) whether each database can be opened normally. If not, what is the reason?

(3) errors related to database corruption

(4) Database backup and recovery action records

(5) DBCC CHECKDB record

(6) memory-related errors and warnings

(7) warning when an exception occurs in SQL scheduling. Generally speaking, SERVER Hang server death is accompanied by these warnings.

(8) warning of a long delay in the SQL Istroke O operation

(9) other high-level errors encountered by SQL during operation

(10) access out of bounds error within SQL (Access Violation)

(11) SQL service shutdown time

(12) SQL SERVER version, as well as basic information of windows and processor.

2.2 Log enables information that can be seen by tracking

(1) successful or failed login of all users

(2) deadlock and the information of its participants. Tracking flag 1222 or 1204

2.3 problems that cannot be recorded in the log

(1) blocking problem. As long as the blocking is not serious enough to affect thread scheduling, it will not be reflected in the log.

(2) General performance problem, timeout problem.

(3) abnormality at windows level.

Therefore, when checking sqlserver-related problems, always start with error log. If there are some errors or warnings in error log, you should confirm and troubleshoot. If the time to record the problem matches the time when the software system went wrong, you need to focus on analysis.

Three. Tracking mark

Use DBCC TRACEON to specify the number of trace tags to open, which are used to customize certain characteristics by controlling how SQL Server works. Enabled trace tags remain enabled on the server until the DBCC TRACEOFF statement is executed to disable them. In SQL Server, there are two kinds of trace flags: session and global. The session tracking flag is valid for a connection and is visible only for that connection. The global trace flag is set at the server level and is visible to every connection on the server. To determine the status of the trace tag, use DBCC TRACESTATUS. To disable trace tags, use DBCC TRACEOFF.

-- the following example is a record deadlock, trace flag 1222 or 1204, and the script is as follows:-- specify the trace flag 1222 or 1204DBCC TRACEON (1222 ~ 1204) that opens the current session-- open the trace flag 1222 or 1204DBCC TRACEON (1222 ~ 1204,-1) globally;-- check the status of the trace flag, as shown in the DBCC TRACESTATUS below.

-- turn off the trace flag status DBCC TRACEOFF (1222221204,-1) globally

For other tracking signs, please refer to the official documentation:

Https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

four。 Configure errorlog

In object Explorer, expand the instance of SQL Server, expand Administration, right-click SQL Server Log, and then click configure as shown in the following figure:

4.1 limit the number of error log files before collection

If you select this option, you will limit the number of error logs that can be created before the error logs are recycled. A new error log is created each time the SQL Server instance is started. SQL Server will keep backups of the first six logs unless you select this option and specify a different maximum number of error log files below.

4.2 maximum number of error log files

Specifies the maximum number of error log files created before error log files are recycled. The default value is 6, which is the number of previous backup logs that SQL Server retains before reclaiming the backup logs.

five。 SQL Server Agent error Log

SQL Server by default, the agent creates error logs to log warnings and errors. SQL Server can maintain up to nine SQL Server agent error logs. Each archived log has an extension that indicates the relative age of the log. For example, the figure above currently represents the latest archived error log, while number 1 represents the oldest archived error log.

By default, execution trace messages are not written to the SQL Server agent log errors because they fill the log. If the error log is full, it reduces the ability to select and analyze more serious errors. Because logging increases the processing load on the server, it is important to carefully consider whether it is worthwhile to capture execution trace messages into the error log. In general, it is best to capture all messages only when debugging a particular problem.

six。 View errorlog

Errorlog files are recorded in text and can be opened with any file editor. Here are two stored procedures to filter and view log files.

6.1 xp_enumerrorlogs

Xp_enumerrorlogs allows you to view the archive and footprint of the error log file. The default parameter is 1, which means to view the sql server log. Parameter 2 indicates to view a list of sql server agent error logs. The script is as follows:

-- View sql server log list EXEC xp_enumerrorlogs

-- View Agent error Log EXEC xp_enumerrorlogs 2

6.2 xp_readerrorlog

Through the system stored procedure: xp_readerrorlog, you can conditionally filter the contents of the log to view. It has a total of seven parameters, which are:

(1)。 File number (0099)

(2)。 Log type (1 is SQL Server log, 2 is SQL Server Agent log)

(3)。 The string contained in the query

(4)。 The string contained in the query

(5)。 LogDate start time

(6)。 LogDate end time

(7)。 Sort the results by LogDate (Desc, Asc)

Example 1: view the contents of the current sql server error log file. The default value of the archive number is 0 and the default log type is 1. If you want to view the contents of the current sql server error log file, there are three ways to write it. The script is as follows:

EXEC xp_readerrorlogEXEC xp_readerrorlog 0EXEC xp_readerrorlog 0,1

Example 2: view the file No. 1 in the SQL Server log history archive. The error occurred between 2018-10-27 19: 00:00 and 2018-10-27 20:00, sorted in reverse order of time. To meet the above requirements, the script is as follows:

EXEC xp_readerrorlog 1meme 1rem nullrem nullrem 2018-10-27 1914 1914 001pl 2018-10-27 2020Mol 00cr reel Desci

Example 3: check the current SQL Server log file. The error contains the string "Login failed for user 'sa'". The script is as follows:

EXEC xp_readerrorlog 0pr 1 sa''' login failed for user''

Shrink Errorlog file

The ErrorLog file on the production server sometimes encounters a large file, especially if the login authentication is recorded in the error log. In this case, the speed of viewing the error log using SQL Server Management Studio or text editor will be a problem. In this case, you can generate a new log file through the stored procedure sp_cycle_errorlog without restarting the server. And cycle through the error log extension number, just as when the service is restarted. In addition to Execute sp_cycle_errorlog, you can also use DBCC ERRORLOG to achieve the same functionality. In practice, the stored procedure can also be executed by establishing a Job timing to control the log file size within a reasonable range.

Note: the data in the old ErrorLog file will be overwritten! If you must save data from old ErrorLog files, you can copy these old ErrorLog files to an external storage medium.

Exec ('DBCC ErrorLog') or exec sp_cycle_errorlog, or you can put sp in Job to execute periodically with the following command.

This is the end of the article on "sample Analysis of error Log errorlog in sql server". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.