Common logs for diagnosing SQL Server issues

Here are two main:

(1) Windows Event Log

(2) SQL Server errorlog

1. Windows Event Log

As a windows open and Managed service program, Windows logs in its own syslog system log

SQL Server This service start, normal shutdown, abnormal shutdown and other information.

SQL Server will also record some of its own profile information in the Windows Application Log application log

And the Windows log itself can reflect the health of the operating system, whether there are any software or hardware exceptions.

If Windows itself does not work properly, SQL Server operations must be affected.

The Windows Event log is a good tool for defining the nature of the problem when it comes to problems that need to be addressed by Microsoft's aftermarket engineers.

In Windows, click "Start"-"Run-" input:eventvwr Click OK to open Event Viewer

In Windows7, Windows2008 and WINDOWS2008R2, the interface will be different, but the main content is similar

There are three main types of logs in Windows: Application, Security, System (my system is Windows7)

The primary concern for SQL Server is the application log and system log. When dealing with some connection authentication issues, the security log may occasionally be used.

Each record in the log belongs to a category of information , warnings , and errors .

Each record is marked with the date, time, source, and event ID.

If the record generated from SQL Server is in the application log, its source name will be MSSQLSERVER

When you double-click a record, Windows pops up a dialog box that displays the details of the record

Here's a case where I'm running out of machine memory, causing SQL Server to swap memory out of the hard drive, causing SQL Server to react slowly

The message that the Event Viewer displays is the one above, in a nutshell: system memory is low

The condition of my machine:

8GB memory is not exhausted because of the 32-bit operating system relationship, a little later intends to replace the 64-bit Windows7

So usually look at the Event Viewer or when you encounter problems, you can see the Event Viewer, you will find some clues to the problem

Another, in the Event Viewer, can also save the log as a *.evt file or a *.txt file for the DBA to take to the other machine to open the analysis.

Open a *.evt file by right-clicking on the "Event Viewer (local)" Tree structure---"Open the saved log

In this way, DBAs can analyze log files that are saved from other machines, just like the log records on this computer.

You can save a single event or an entire category of events while saving

Finally, the log that is opened with the Event Log viewer has a time that is related to the timezone,

Machines with different time zones set to open a *.evt file, it will be displayed differently.

For example, if an error message occurs during the daytime in the United States, then the machine used in China is opened and its time is displayed in the evening

If you're looking for American time, you won't find it. However, this is not a problem if you save it in the *.txt format text file format

2. SQL Server Errorlog file

After you have checked the basic conditions of Windows, you can begin to check the health status of SQL Server.

No matter what your problem is, the first thing to check is SQL Server's errorlog file

When SQL Server starts, a "errorlog" file is generated under a fixed path

SQL Server retains 7 copies of the Errorlog file by default, in chronological order, followed by file name extension. 1,.2,.3,...,. 6.

Each time the service is restarted, the file extension will be added one, and the oldest copy will be deleted.

The default path to the log file is the C:\Program Files\Microsoft SQL Server\mssql.1\mssql\log\log subdirectory under the installation path.

C:\Program files is the installation path of my machine, this path is the one you chose when you installed SQL Server.

Of course DBAs can also modify their settings (in Configuration Manager, double-click SQL Service-"advanced-" Dump directory)

found that Windows to the error log or directory are called dumps, such as some software, such as QQ, Youdao dictionary seems to be a dump file in DMP format

Say back to the Chase O (∩_∩) o

If you are analyzing a strange server, you can find the errorlog path in a number of ways.

A simple way to do this is to select the SQL service in Server Configuration Manager and find a "startup parameter" Advanced property in its properties-"advanced"

In the property string, there will be an "-e" parameter. His back is the location of the errorlog file.

Or you can see the dump directory on top of that.

errorlog files are recorded as text and can be opened with any file editor, including Notepad, SSMs

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

However, errorlog itself is very important, and he records the entire opening, running, and terminating process of SQL.

If SQL Server encounters a more serious problem, it will show up in Errorlog.

The errorlog display includes the following:

(1) SQL version, as well as Windows and processor basic information

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

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

(4) Errors related to database corruption

(5) Database backup and recovery action record

(6) DBCC CHECKDB Records

(7) Memory-related errors and warnings

(8) Warning when an exception occurs in SQL Scheduler. General server Hang server death opportunities accompany these warnings

(9) SQL I/O operation has experienced long delay warning

(ten) Other high-level errors encountered by SQL during operation

(one) Access out-of-bounds error inside SQL (Access violation)

() SQL Service shutdown time

When checking SQL Server related issues, always start from errorlog, first confirm that errorlog is clean.

If there are some errors or warnings in the errorlog, it is time to confirm that these errors and warnings occurred and whether the front end felt the problem.

If time can be right, then we should focus on the analysis

If you turn on some settings, the useful information you can see in errorlog is:

(1) Successful or failed login for all users

(2) Deadlock and information about its participants: you need to turn on trace flag 1222 or 1204

DBCC TRACEON (1222)DBCC TRACEON (1204)   

Sometimes errorlog is not omnipotent, oh? The questions he could not reflect were:

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

(2) General performance problem, timeout problem. If the performance issue is not due to memory usage exceptions, thread dispatch exceptions, or the I/O subsystem reacts very slowly,

But because of the form or statement design, Errorlog will not reflect.

(3) Windows-level exceptions. If the Windows layer does not work properly, or if the server does not respond, it is difficult to self-judge

Above these three questions, errorlog in general does not have the embodiment. That's why we need to check the event log for the first step.

Here is a errorlog of the content to explain

You can click on the link below

SQL Server errorlog explained

Common logs for diagnosing SQL Server issues