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 codeCode: 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.