I. Overview
Viewing SQL server error logs ensures that processes (such as backup and restoration operations, batch processing 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.
You can use SQL Server Management studio or any text editor to view SQL server error logs.
2. Directory and number of error logs
By default, the error log is locatedProgram Files \ Microsoft SQL Server \ MSSQL.
N\ MSSQL \ log \ errorlog
AndErrorlog.
NFile.
When you start an SQL server instance, a new error log is created, although System stored procedures can be used to recycle error log files without restarting the SQL server instance. Generally, the SQL Server Error Log maintains one current error log and six historical error logs by default. SQL Server retains the backup of the first six logs, and specifies the extension of the latest log backup to. 1, the extension of the next recent log backup to. 2, and so on. The current error log does not have an extension.
Basic management tasks
- Configure the number of retained SQL server error logs: Enterprise Manager; Exec xp_instance_regwrite n 'HKEY _ LOCAL_MACHINE ', N 'Software \ Microsoft \ MSSQLServer', N 'numerrorlogs ', REG_DWORD, n (6-99)
- recycle Error Log: exec sp_cycle_errorlog
- read error log information: Enterprise Manager; OpenRowSet; xp_mongoshell