SQL Server Monitoring – Instance status

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To get a quick overview of the instance status.

Report

The report shows 4 tables where the upper one shows content of the error logs collected, the middle one the log messages collected, the lower left one shows the dump files reported on the instance, and the lower right one reports about detected suspect pages on the instance.

By default it is SQL Server log records with severity level equal to 17 or above that are collected as error logs.

Sources

This output here is based on the DMV sys.dm_server_memory_dumps which returns information about the dump files existing on the SQL Server, the DMV msdb.suspect_pages which returns information about suspect pages on databases reporting the file and page id that is suspect and the master.dbo.xp_readerrorlog which returns the content of the SQL Server log files where here it is only used the current file (Id 0).

Model

The model for the report uses the facts [Error logs], [Instance dump files], [Instance suspect pages], [Log messages], and the dimension [Date].

It is the same model that are used by the Instance configuration and Resource Governor configuration reports.

Configuration

The report uses the same data as used by the Instance configuration report.

The default range for rows returned by fact [Error logs] and [Log messages] are 1.000 rows and rows up to 30 days old.

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'list',
	@Key = 'View.ErrorLogs';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'list',
	@Key = 'View.LogMessages';

Set range of rows returned to custom values:
EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.ErrorLogs.Days',
	@Value = '7';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.ErrorLogs.Rows',
	@Value = '500';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.LogMessages.Days',
	@Value = '14';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.LogMessages.Rows',
	@Value = '100';


Reset range of rows returned to default values:
EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.ErrorLogs.Days',
	@Value = NULL;

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.ErrorLogs.Rows',
	@Value = NULL;

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.LogMessages.Days',
	@Value = NULL;

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.LogMessages.Rows',
	@Value = NULL;