dbWarden is a comprehensive database monitoring and alerting solution for SQL Server 2005. 2008 and now 2012. It features email and text notifications with customizable metrics for alerts such as Blocking, Long Running Queries and SQL Jobs, CPU %, Log file and TempDB growth. Another main feature is a detailed HTML based daily health report which provides a thorough overview of your database server.
dbWarden is written completely in SQL and requires sysadmin level permissions to run xp_instance_regread and xp_fixeddrives. Utilizes SQL Agent, DBMail and Operators.
- Completely Free
- SQL-based for easy and quick installation and setup
- Compatible with all editions of SQL Server 2005, 2008 and 2012 except for Express (requires SQL Agent and DBMail)
- Receive a comprehensive and configurable daily "Health Report" that provides a thorough overview of your SQL Server
- Sends alerts via email or text using DBMail
- Includes sp_Sessions to monitor session and query performance
- Monitors your database servers for:
- - Long running queries
- - Long running Jobs
- - Blocking
- - Log file and TempDB growth
- - CPU utilization
- - Schema Change Tracking
- - Reporting on deadlocks
A terrific tool that I have deployed on many servers, definitely recommended. thank you very much for your hard work and making it available to everyone. There is a request I would like to make if you don't mind: if you have the time what would be very useful would be a way to collate all the data generated into a central location so aggregate reports could be created for all the servers monitored for MI reporting. I have started working on that myself using your existing code but I am sure you would do a better job. Thanks again.
Very good work. This is amazing at what it is able to do and what it can monitor.
Wonderful tool. Would you consider 2 items: 1) displaying the Collation of the databases on 'Databases' or 'Database Settings' section. 2) not having the Last Backup Date red(or in color) for read only or databases that are labeled as secondary in a mirror relationship Thanks for all the hard work.
Hi, this is a really useful tool. I have some ideas to make it even more useful or simply be easier to manage : - schema selection for installation - Creation of a function which returns the exact same infos as reports like rpt_HealthReport as String or as a row set instead of sending an email. The aim of this : incorporating those reports into a centralized website from which I can get a report on demand. - graphs By the way, if you need, I can provide some help.
This is a awesome tool. But, I modified Disks section of report. First, I create a new DiskSpaceAlert setting in AlertSetting table, and assign a 20 to value column, then replace in script and work fine. Then, I prefering add the Total Space and Free Percentage columns, Free Percent is marking with Red when Free Percent is under a new DiskSpaceAlert setting. 1. First I aggregate DiskSpaceAlert setting INSERT INTO dbo.AlertSettings (AlertName,VariableName,Value,[Description],[Enabled]) SELECT 'DiskSpaceAlert','QueryValue','30','Value is in percentage',1 2. Under the following instrucction: INSERT INTO #DRIVES (DriveLetter,FreeSpace) EXEC master..xp_fixeddrives I add the next code block: ALTER TABLE #DRIVES ADD [TotalSpace] BIGINT ALTER TABLE #DRIVES ADD [FreePercent] DECIMAL(10, 2) DECLARE @hr int DECLARE @fso int DECLARE @drive char(1) DECLARE @odrive int DECLARE @TotalSize varchar(20) DECLARE @MB NUMERIC SET @MB = 1048576 EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT DriveLetter from #DRIVES ORDER by DriveLetter OPEN dcur FETCH NEXT FROM dcur INTO @drive WHILE @@FETCH_STATUS=0 BEGIN EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive UPDATE #DRIVES SET TotalSpace=@TotalSize/@MB WHERE DriveLetter=@drive FETCH NEXT FROM dcur INTO @drive End Close dcur DEALLOCATE dcur EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso UPDATE #DRIVES SET FreePercent = (FreeSpace * 100.0) / TotalSpace DECLARE @DiskSpaceQueryValue INT SELECT @DiskSpaceQueryValue = COALESCE(CAST(Value AS INT),0) FROM [dbWarden].dbo.AlertSettings WHERE VariableName = 'QueryValue' AND AlertName = 'DiskSpaceAlert' Note: You will need permissions to execute the sp_OACreate, sp_OAGetErrorInfo, sp_OAMethod, sp_OAGetProperty and sp_OADestroy stored procedures. 3. In rpt_HealthReport stored procedure i do the next changes, you must replace the #drives query with: SELECT @HTML = @HTML + '<div><table width="250"> <tr><th class="header" width="250">Disks</th></tr></table></div><div> <table width="250"> <tr> <th width="50">Drive</th> <th width="100">Total Space (GB)</th> <th width="100">Free Space (GB)</th> <th width="100">Free Percent (%)</th> <th width="100">Cluster Share</th> </tr>' SELECT @HTML = @HTML + '<tr><td width="50" class="c1">' + DriveLetter + ':' +'</td>' + '<td width="100" class="c2">' + COALESCE(CONVERT(NVARCHAR(50), COALESCE(CAST(CAST(TotalSpace AS DECIMAL(10,2))/1024 AS DECIMAL(10,2)), 0)),'') +'</td>' + '<td width="100" class="c1">' + COALESCE(CONVERT(NVARCHAR(50), COALESCE(CAST(CAST(FreeSpace AS DECIMAL(10,2))/1024 AS DECIMAL(10,2)), 0)),'') +'</td>' + CASE WHEN (COALESCE(CAST(CAST(FreePercent AS DECIMAL(10,2)) AS DECIMAL(10,2)), 0) <= @DiskSpaceQueryValue ) --20) THEN '<td width="100" bgcolor="#FF0000"><b>' + COALESCE(CONVERT(NVARCHAR(50), COALESCE(CAST(CAST(FreePercent AS DECIMAL(10,2)) AS DECIMAL(10,2)), 0)),'') +' %</b></td>' ELSE '<td width="100" class="c2">' + COALESCE(CONVERT(NVARCHAR(50), COALESCE(CAST(CAST(FreePercent AS DECIMAL(10,2)) AS DECIMAL(10,2)), 0)),'') +' %</td>' END + CASE ClusterShare WHEN 1 THEN '<td width="100" class="c1">Yes</td></tr>' WHEN 0 THEN '<td width="100" class="c1">No</td></tr>' ELSE '<td width="100" class="c1">N/A</td></tr>' END FROM #DRIVES Thanks you !!!