Common log Overview and use _MSSQL for diagnosing SQL Server problems

Common logs for diagnosing SQL Server problems

There are two main:

(1) Windows Event Log

(2) SQL Server ErrorLog

1, Windows Event Log event logs

As a windows open and Managed service program, Windows logs information about the startup, normal shutdown, and shutdown of SQL Server for this service in its own syslog system log.

SQL Server also records some of its own profiles in the Windows Application Log application log and the Windows log itself can reflect the health of the operating system and whether there are any software or hardware exceptions.

If Windows itself does not work correctly, SQL Server operations are bound to be affected.

The Windows Event log is a good tool to define the nature of the problem when there are problems that need to be addressed by Microsoft's aftermarket engineers.

In Windows, click on "Start"-"Run-" Input: EVENTVWR Click OK to open Event Viewer

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

Windows has three main logs: application, security, System (my system is Windows7)

For SQL Server, the primary concern is the application log and the system log. You may occasionally use the security log when handling some connection authentication issues.

Every record in a log is a category of information , warnings , and errors .

Each record will indicate the date, time, source, and event ID.

If in the application log, the source name of the record generated from SQL Server is MSSQLServer

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

In this case, I encountered a lack of machine memory, resulting in SQL Server need to swap memory out of the hard drive, resulting in frequent SQL Server slow response

The information displayed by the Event Viewer is the screenshot above, which is summed up in the following sentence: system memory is low

My machine Condition:

8GB of memory is not exhausted because of the 32-bit operating system relationship, it is intended to be replaced with 64-bit Windows7

So when you look at the Event Viewer more often or when you encounter a problem, look at the Event Viewer first, and you will find some clues to the problem.

Alternatively, in Event Viewer, you can save the log as a *.evt file or a *.txt file for the DBA to take to another machine to open the analysis.

The way to open a *.evt file is to right-click the Event Viewer (local) tree structure---to open a saved log

In this way, the DBA will be able to analyze the log files saved from other machines, just as you can see on the local log records.

You can save a single event or an entire category of events when you save it

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

A machine with a different time zone setting opens a *.evt file, which is displayed differently.

For example, if an error message occurs during the daytime in the United States, then the machine is opened in China and its time will be displayed at night

If you look at American time, you won't find it. But saving in *.txt format text file format will not have this problem

2. SQL Server errorlog files

Once you have checked the basics of Windows, you can start checking the health of SQL Server.

No matter what your problem is, it is recommended that the first check is SQL Server's ErrorLog file

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

SQL Server will retain 7 copies of errorlog files by default, in chronological order, in sequence with file extensions. 1,.2,.3,...,. 6.

Every time the service is restarted, the file name extension is added, and the earliest part is deleted.

The default path for log files 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 for my machine, the path you chose when you installed SQL Server

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

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

Back to the Point O (∩_∩) o

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

A simpler approach is to select the SQL service in SQL Server Configuration Manager and find a "startup parameter" Advanced property in its properties-"advanced"

In the property string, there will be an "-e" argument. Behind him is the location of the errorlog file.

Or you can see it in the dump directory above.

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 demand

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 appear in the errorlog.

The errorlog display includes the following contents:

(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) Database corruption-related errors

(5) Database backup and recovery action record

(6) DBCC CHECKDB Records

(7) Memory-related errors and warnings

(8) A warning for SQL dispatch when an exception occurs. General server HANG server dead chance with these warnings

(9) SQL I/O operation has encountered a warning for a long time delay

(a) Other higher-level errors encountered by SQL in the course of running

(one) intra-SQL access error (Access violation)

() SQL Service shutdown time

When checking SQL Server related issues, always start with errorlog and make sure errorlog is clean.

If there are any errors or warnings in the errorlog, verify that the errors and warnings occur, and whether the front-end feels the time of 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 unsuccessful login for all users

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

Copy Code code as follows:

DBCC Traceon (1222)
DBCC Traceon (1204)

sometimes errorlog is not a panacea, oh? The questions he could not reflect were :

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

(2) General performance problem, timeout problem. If performance problems are not due to memory usage exceptions, thread scheduling exceptions, or the I/O subsystem reacting very slowly,

But because of the form or the design of the sentence, errorlog will not reflect

(3) Windows level exception. If the Windows level is not working properly, or the server is not responding, SQL Server can hardly judge itself

The above three questions, errorlog in general will not be reflected. That's why we need to check the event log for the first step.

The following gives a errorlog content to explain