MSSQL log record management

MSSQL log records may be both familiar and unfamiliar to you, because you may have been using them all the time to view and focus on some log records, for example, job history, because you may never care about the management of log information records. Here I keep using the word log information to record, instead of using the word log file.

Ms SQL log information/logging, may be for you, both familiar and unfamiliar, familiar is because you may have been using, view, follow some log information/records, for example, job history. unfamiliar because you may never pay attention to log information/Record Management. Here I have been using the word log information/record, rather than using the word log file.

Ms SQL log information/logging, may be for you, both familiar and unfamiliar, familiar is because you may have been using, view, follow some log information/records, for example, job history, because you may never care about log information/Record Management. Here, I have been using the word log information/record, rather than using the word log file, I want you to distinguish it from the transaction log file (ldf). The Hong Kong server is rented. on the Internet, you can use the log file for search keywords, and the transaction log-related information may be found. In fact, it is also called a log file, this article describes the log classification, how to view the log records, the location of the log records, how to set the log records, why the error logs increase rapidly, and how to clear the log records.

Log Record category

According to the log file viewer, errors are classified as SQL server, SQL SERVER proxy, Windows application logs, database emails, and other four types of error log records. If you also consider the maintenance plan, remote maintenance plan, job history log information, a total of seven types of log information files.

Windows Application log types include System, Security, Application, and PatchLink logs, I opened SSMS on the Server (Windows Server 2008 R2 Standard) and found that there were more log files, such as HardwareEvents, Internet Explorer, and Windows PowerShell. These are all system log files. You don't have to worry too much about the number of types.

Log Record location

The SQL server log records and SQL SERVER proxy records are stored in ERRORLOG. in the n (n is a number) file, the SQL SERVER Agent log record is located in SQLAGENT. n. Of course, this is also related to the database version:

Version

Path

SQL SERVER 2005

Program Files \ Microsoft SQL Server \ MSSQL. n \ MSSQL \ LOG

SQL SERVER 2008

Program Files \ Microsoft SQL Server \ MSSQL10. Instance name \ MSSQL \ LOG

SQL SERVER 2008 R2

Program Files \ Microsoft SQL Server \ MSSQL10_50. Instance name \ MSSQL \ LOG

SQL server 2005. By default, error logs are stored in the Program Files \ Microsoft SQL Server \ MSSQL. n \ MSSQL \ LOG \ ERRORLOG and ERRORLOG. n Files. The difference between MSSQL. n is:

MSSQL.1: SQLSERVER

MSSQL.2: SSAS

MSSQL.3: SQLExpress

MSSQL.4: SSRS

Therefore, in general, you only need to pay attention to the log files under the MSSSQL.1 directory

So where is the database Mail Log Record located? Where are the job history logs and Windows application logs? Have you ever considered this?

The database mail log record information can be queried from the msdb. dbo. sysmail_event_log view and saved in the [dbo]. [sysmail_log] table.

Sysmail_event_log

The job history log information is saved in the table msdb. dbo. sysjobhistory. The run_status field indicates the job execution status.

0 = failed

1 = successful

2 = retry

3 = canceled

4 = in progress

All Windows application logs are in the same location % SystemRoot % \ System32 \ Winevt \ Log. The Application log file is located at % SystemRoot % \ System32 \ Winevt \ Logs \ Application. evtx, as shown below,

View log records

Viewing logs ensures that processes (such as backup and restoration operations, batch commands, or other scripts and processes) are successfully completed. This feature helps detect any current or potential problem areas, including automatic message recovery (especially when the SQL Server instance is stopped and restarted) kernel messages or other server-level error messages.

Method 1: View Error Log Files

For SQL server and SQL SERVER AGENT log record information, you can directly go to the log directory to find the ERRORLOG and SQLAGENT log files and open them directly. For example, Windows Application log records, go to the % SystemRoot % \ System32 \ Winevt \ Log directory, find the corresponding Log file, and open it directly.

Method 2: view log records through SSMS

View logs related to regular SQL Server activities

In the object Resource Manager, expand "manage" and "SQL Server log", and then double-click "Current <日期 时间> SQL Server, SQL proxy, and Windows event logs are displayed.

View job-related logs

In the object Resource Manager, expand "SQL Server proxy", right-click "job", and then click "view history ", the "job history" and "SQL proxy" logs are displayed.

View logs related to the Maintenance Plan

In the object Resource Manager, expand "management", right-click "Maintenance Plan", and then click "view history ", the maintenance plan, job history, and SQL proxy logs are displayed.

Method 3: Use a script to view

3.1 for SQL SERVER log files, run the following script:

-- View the log file archive number

EXEC master. dbo. sp_enumerrorlogs

You can use this command to view the log file size, which is very useful. You can find out the files with abnormal size.

-- View the log Content Based on the archive number

EXEC master. dbo. xp_readerrorlog 1

-- View SQL SERVER log records based on job_id

SELECT * FROM msdb. dbo. sysjobhistory WHERE job_id = '36e9232b-CD5B-4646-9BED-B8242090FFF9'

3.2 for job history log information, you can view the following stored procedure or directly query the corresponding table.

For example, in the server space, I want to view the historical records of the "ServerDiskCapacityCheck" job.

Code Snippet

3.3 view database email records

SELECT * FROM msdb. dbo. sysmail_event_log;

Log Management

Set the maximum number of Error Log Files

1: In the object Resource Manager, connect to the SQL Server database engine instance and expand the instance.