One of the most important things that a DBA could monitor is the SQL Server Error Log. SQL Server will log almost every event of interest to a DBA for early problem identification to the Error Log. This includes failed logins, Backup completion by database, and backup failures by database, the end of recovery after a restart, DBCC Events, reconfiguration of server or database options, non-yielding schedulers, stack dumps, autogrowth failures, delayed IO events, and trace flag output if the flags have been turned on. Developers can also have errors in code execution be logged to the Error Log by using the WITH LOG option of RAISERROR. The amount of information that a DBA can get from the Error Log can be quite overwhelming. For this reason, non-critical events and events of non-interest should be filtered from the log data when generating automated notifications.
To limit the size of the SQL Server error log, the sp_cycle_errorlog system stored procedure can be issued to start a new error log. Depending on the growth rate of the SQL Server error log dictates when sp_cycle_errorlog should be issued. Reference the code below as an example.
EXEC master.sys.sp_cycle_errorlog;
-- Expected successful output
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
To limit the size of the SQL Server error log, the sp_cycle_errorlog system stored procedure can be issued to start a new error log. Depending on the growth rate of the SQL Server error log dictates when sp_cycle_errorlog should be issued. Reference the code below as an example.
EXEC master.sys.sp_cycle_errorlog;
-- Expected successful output
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No comments:
Post a Comment