Common logs used to diagnose SQLSERVER Problems

Commonly used logs for SQL Server diagnosis: (1) Windows event log (2) SQLSERVERErrorLog1. Windows event log EventLog is used as a service program enabled and managed by Windows, windows records the startup, normal shutdown, and abnormal shutdown of the SQLSERVER service in its system log systemlog.

Common logs used to diagnose SQLSERVER problems include: (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 of the SQLSERVER service in its system log.

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 the SQLSERVER service.

SQLSERVER also records some of its summary information in the Application Log of Windows.

Windows logs can reflect the health status of the operating system and whether there are any software or hardware exceptions.

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)

For SQLSERVER, you are mainly concerned with application logs and system logs. When dealing with connection authentication problems, security logs may occasionally be used.

Each record in a log is classified into information, warnings, and errors.

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

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

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 above. In a word, the system memory is insufficient.

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 the ErrorLog file of SQLSERVER for the first time.

When SQLSERVER is started, an "errorlog" file is generated in a fixed path.

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, the website space and errorlog are very important. They record 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, Hong Kong server, Authentication mode, 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 checking SQL Server-related problems, always start with errorlog, Hong Kong server, first confirm that 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.

1 dbcc traceon (1222) TRACEON (1204)