Overview and usage of common logs for diagnosing SQLSERVER Problems

Common logs used to diagnose SQLSERVER Problems

There are two main points:

(1) Windows Event Log

(2) SQLSERVER ErrorLog

1. Windows Event Log

As a service program enabled and managed by Windows, Windows records the startup, normal shutdown, and abnormal shutdown information of SQLSERVER in its system log.

SQLSERVER will also record some of its summary information in the Application Log of Windows, while the Windows Log itself can reflect the health status of the operating system, is there any software or hardware exception.

If Windows itself cannot work normally, SQL Server's operation will be affected.

When Microsoft's after-sales engineers need to solve some problems, Windows event logs are a good tool to define the nature of the problem.

In Windows, click Start-run-input: eventvwr Click OK to open the Event Viewer.

In Windows7, Windows2008, and Windows2008R2, the interface is different, but the main content is similar.

Windows mainly has three types of logs: application, security, and system (my system is Windows 7)

The main concern for SQLSERVERApplicationsLogs andSystemLogs. When dealing with some connection authentication problems, you may occasionally useSecurityLogs.

Each record in the log belongsInformation,Warning,Error.

Each record indicates the date, time, source, and event ID.

If the source name of the records generated from SQLSERVER in the application log isMSSQLSERVER

Double-click a record. In Windows, a dialog box is displayed, showing the specific record content.

 

The memory I encountered is insufficient, which causes SQLSERVER to replace the memory with the hard disk, leading to slow response of SQLSERVER.

The information displayed in the Event Viewer is the one shown above:Insufficient system memory

My machine status:

 

8 GB memory is not used up. Because of the 32-bit operating system, I plan to change it to 64-bit Windows 7 later.

 

So when you look at the event viewer or when you encounter problems, you should first look at the event viewer to find some clues

In the event viewer, you can save logs as *. evt files or *. txt files for DBA to open analysis on other machines.

Open an *. evt file by right-clicking the "Event Viewer (local)" tree structure --- "to open the saved log.

In this way, DBA can analyze the log files stored from other machines just like the logs on the local machine.

You can save a single event or an event of the entire category.

Finally, the time of the log opened in the Event Log Viewer is related to the time zone,

When a *. evt file is opened on a machine set in different time zones, the display time varies.

For example, if an error message occurs in the daytime in the United States, it will be displayed at night when the machine in China is enabled.

If you search for data by U.S. time, you will not be able to find the data. However, this problem does not occur if the file is saved as a *. txt text file.

 

2. SQLSERVER ErrorLog File

After checking the basic status of Windows, you can start to check the health status of SQLSERVER.

No matter what problems you encounter, we recommend that you check SQLSERVER's firstErrorLogFile

When SQLSERVER is started, a"Errorlog"File

By default, SQLSERVER retains 7 errorlog files, which are sequentially named. 1,. 2,. 3,...,. 6 in chronological order.

Each time the service is restarted, the file extension is added and the earliest one is deleted.

 

The default LOG file path is the C: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ LOG subdirectory under the installation path.

C: \ Program Files is the installation path of my machine. This path is selected when you install SQLSERVER.

Of course, DBA can also modify its settings (in the Configuration Manager, double-click SQL Service-> advanced-> dump directory)

I found that Windows is called a dump for error logs or directories, such as some software, such as QQ. youdao dictionary also seems to be a dump file in dmp format.

 

Answer question o (answer _ Answer) o

If you want to analyze a strange server, you can find the errorlog path in many ways.

One simple method is to select SQL service in the SQLSERVER Configuration Manager and find an advanced attribute of "Startup parameter" in its properties-> advanced.

The attribute string contains a "-e" parameter. It is followed by the location of the errorlog file.

 

Or you can simply see the dump directory above.

The errorlog file is recorded in text and can be opened in any file editor, including notepad and SSMS.

Generally, the size of the errorlog file is not large. Use these tools to fully meet your needs

However, errorlog is very important. It records the entire SQL enabling, running, and terminating process.

If SQLSERVER encounters a serious problem, it will be displayed in errorlog.

ErrorLog displays the following content:

(1) SQL version and basic information about Windows and Processor

(2) SQL startup parameters, Authentication mode, and memory allocation mode

(3) Whether each database can be opened normally. If not, why?

(4) database corruption errors

(5) database backup and recovery action records

(6) dbcc checkdb records

(7) memory-related errors and warnings

(8) warning when an exception occurs during SQL scheduling. General server hang server death opportunities are accompanied by these warnings

(9) Warning of long latency in SQL I/O operations

(10) other high-level SQL errors encountered during running

(11) Access Violation)

(12) SQL Service Shutdown Time

When you check SQL server problems, you always start with errorlog and check whether the errorlog is clean.

If there are some errors or warnings in the errorlog, check the time when these errors and warnings occur and whether the front-end feels the problem.

If the time is correct, analyze it.

If you enable some settings, you can also see the following useful information in errorlog:

(1) logon successful or failed by all users

(2) deadlock and participant information: the tracking mark 1222 or 1204 must be enabled.
Copy codeThe Code is as follows:
Dbcc traceon (1222)
Dbcc traceon (1204)

Sometimes errorlog is not omnipotent? He cannot reflect the following problems::

(1) blocking problem. As long as blocking has not seriously affected SQL Server thread scheduling, errorlog will not be reflected

(2) common performance problems and timeout problems. If the performance problem is not caused by memory usage exceptions, thread scheduling exceptions, or the I/O subsystem is very slow,

It is caused by table or statement design that is not reflected in errorlog.

(3) Windows exceptions. If the job is abnormal on Windows or the server does not respond, it is difficult for SQLSERVER to determine its own

The above three problems are generally not reflected in errorlog. This is why we need to check the Event Log in the first step.

The following is an explanation of errorlog.