dbWarden for SQL Server Reviews

Well done, thank you. With little customization it will easily substitute expansive monitoring solutions.

Would give this five stars, but the usp_filestats procedure in version 2.5.2 is throwing a "Msg 8115, Level 16, State 2, Line 40 Arithmetic overflow error converting expression to data type int." which is causing the health report job to fail. I am running this on SQL Server 2012, SP3.

SQL Server 2012 SP3, error in Replication monitor section, need to add another column name of publisher NVARCHAR(128) to the end of the table definition.

I think I found a small bug in the procedure usp_FileStats. When inserting into #FILESTATS you are putting brackets around @dbname. For me this causes the flags in alertsettings table not to work because of the checks in FileStatsHistory table.

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.

There are some issues. The first is the frequent failing of the Long Running Queries job. The second, is that the RunTime calculations are off. If I compare the datestamp fields between two records in a five minute interval, I'll get all kinds of different increases in the RunTime value. For example, the first record was created at 11:37 PM and the second at 11:42 PM, but it's showing the RunTime value increase from 11,225.48 - 16,276.966 seconds. Same SPID, same statement executing. That's a difference of approx 1.4 hrs. The next 5 minute interval (11:47 PM) shows the jump to 18,725.235 seconds. That's an increase of around 41 minutes. I considered that the time was in something other than seconds, or maybe time actually running on CPU, but that doesn't seem to add up either. If I've missed something or am incorrect, please let me know.

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 !!!

Really great work. Thank you for building this. The thing I love most is that I can climb into the SQL and change/add things as I like. Its really comprehensive and a great starting point for any DBA who is stuck with zero budget for sql server monitoring software.

I really like this project. Can you help me out with these errors? [usp_CheckFiles] Arithmetic overflow error converting expression to data type int. [usp_FileStats] Arithmetic overflow error converting expression to data type int.

Question- Any possibility of using something other than sp_WhoIsActive to gather info about long-running queries? Usage of Adam Machanic's code may restrict ability of some people to use your code, due to his licensing model.

First of all I'd like to thank you for posting this. It is truly a very useful tool and it will be a great asset to many DBAs in the community. I wanted to add to the report of 2012 issues. In addition to dm_os_sysinfo, the DBCC LOGINFO has changed also. The RecoveryUnitID column in the result set needs to be handled in the temp table insert. I was also wondering, however, is there an uninstall script at all? It appears like to uninstall I'd have to drop the database, sql agent jobs, and go through each of my databases removing the DDL triggers. This is not a terrible inconvenience but I just wanted to know if you had planned for the ability to easily remove the system. Once again, thank you very much for providing this work for free.

I'm giving this a thumbs up, but it's very conditional. This doesn't seem to work with SQL Server 2012. The column names in a dmv have changed (dm_os_sys_info), and rpt_HealthReport and usp_CheckFiles fail. I've updated rpt_healthReport to have the correct column name from dm_os_sys_info, but there is still an error that I don't have time to look into right now ("Column name or number of supplied values does not match table definition."). Have you tested on SQL Server 2012? I was hoping to get this up and running on 2012 before putting it on 2008, as my 2012 box is less mission critical.

Hi, Thank you for posting dbwarden script for monitoring SQL server live trasaction and health status. please confirm as below, 1. SP name is rpt_HealthReport, here your using TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.. does this ISOLATION level affect DBA database or whole instance? Thanks ananda