Sql-server – SQL Server WMI Event Alert and BROKER_TASK_STOP waits

Here is a slightly simpler approach that avoids the cursor and nested exec:

SET NOCOUNT ON;

CREATE TABLE #to
(
  DBName SYSNAME,
  FileCount INT
);

DECLARE @v INT;
SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), 
  SERVERPROPERTY('ProductVersion')), 4));

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #ti
  (
    ' + CASE WHEN @v >= 11 THEN 'RecoveryUnitId INT,' ELSE '' END + '    
    FileId int
    , FileSize nvarchar(255)
    , StartOffset nvarchar(255)
    , FSeqNo nvarchar(255)
    , Status int
    , Parity int
    , CreateLSN nvarchar(255)
);';

SELECT @sql = @sql + N'
  INSERT #ti EXEC ' + QUOTENAME(name) 
    + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
  INSERT #to(DBName,FileCount) SELECT N''' + name + ''', COUNT(*) FROM #ti;
  TRUNCATE TABLE #ti;'
FROM sys.databases
WHERE database_id > 4 AND [state] = 0;

EXEC sp_executesql @sql;

SELECT DBName, FileCount FROM #to -- WHERE FileCount > [some threshold];

DROP TABLE #to;

You can use a combination of tools. I'll give you an abridged list of the ones I use.

First, I assume you have database mail configured.

Second, you can trigger a lot of run-of-the-mill alerts using SQL Server Alerts. You can also run a stored procedure using a scheduled job (say every five minutes or some interval like that) and fire the user counters which will trigger SQL Server Alerts. When an alert happens, you can also execute a job in addition to notifying operators. If you know WMI, you can use Alerts to get to PerfMon counters.

Third, you can use SQL Server Extended Events, but they are more complicated and require a good understanding of the database engine and hand coding because there is very little in the way of GUI.

Finally, you can just schedule a stored procedure to run on a regular basis, allow it to collect metrics from the DMVs (database management views) and then send the email directly from the stored procedure.

Specifically for your situation, I might suggest you run sys.dm_os_performance_counters on a regular basis (scheduled job/stored procedure) and cache it in a metadata table, then use that for analysis.

Also, getting CPU usage information in SQL Server is not the easiest thing in the world because CPU stats can change on such a finely-granular timescale. Try this article for a good overview of how it works. Perhaps look at "free pages" to get you 70% RAM figure.