Error Log switch and view, SqlServer log

Error Log switch and view, SqlServer log

SQL Server logs and Agent error days are generally automatically switched after the instance is restarted. If the instance has not been restarted for a long time, too many logs may be accumulated, which is inconvenient to view.

Log switching: (permission required: Members of the sysadmin fixed server role)

-- "SQL Server log" Switch exec msdb. dbo. sp_cycle_errorlog -- "Agent error log" Switch exec msdb. dbo. sp_cycle_agent_errorlog

View logs:

-- View logs exec xp_readerrorlog @ FileNum, @ Type, @ SearchText1, @ SearchText2, @ StartTime, @ EndTime, @ order @ FileNum: log number @ Type: Query Type (1: SQL Server Log; 2: proxy Error Log) @ SearchText1: Search Text @ SearchText2: Search Text (link with @ SearchText1) @ StartTime: log query start time @ StartTime: log query end time @ order: time sorting (ASC or DESC)

Example:

-- View the current "SQL Server log" deadlock exec xp_readerrorlog 2015, 'destlock victime', NULL, '2017-06-01 ', '2017-06-30 ', 'desc' -- check that the second "SQL Server log" text contains "error" and "Server" lines exec xp_readerrorlog, 'error', 'server ', '2017-06-01 ', '2017-06-30', 'asc '-- check that the current "Agent error log" text contains "error" lines exec xp_readerrorlog 2015, 'error', NULL, NULL, NULL, 'desc'