Revision History

Please refer to this link for documentation: http://www.sqlservercentral.com/articles/Monitoring/98106/

All issues reported in the Wiki have been removed. If you are having issues with dbWarden, please submit a bug in the Ticket section.

Project Admins:

• stevierounds
• Michael Rounds

Revision History:

Date	Author	Version	Revision
7/23/2013	Michael Rounds	2.5.2	----> Added support for CaSe SeNsitiVity
07/09/2013	Michael Rounds		----> rpt_HealthReport - Added Orphaned Users section
			----> Added ShowOrphanedUsers to the AlertSettings table
6/24/2013	Michael Rounds	2.5.1	----> Fixed bug that prevented usp_CheckFiles and rpt_HealthReport from functioning when a Single user DB was actively being used
6/21/2013	Michael Rounds		----> rpt_HealthReport - Added ability to turn off databases from showing up in the HealthReport (turn on/off via DatabaseSettings table)
			----> Added DEFAULTs to DatabaseSettings table.
06/13/2013	Michael Rounds		----> Added SET NOCOUNT ON where needed
			----> Fixed Critical bug in usp_CheckFiles - Added missing entries into AlertSettings for MinFileSizMB for LogFiles and TempDB. This was preventing these alerts from working for users upgrading from a previous version. New installs of dbWarden would not have been affected.
			----> Added AlertSettings Enabled column to queries to determine if the alert is enabled.
05/28/2013	Michael Rounds	2.5	----> New proc sp_Sessions, replaced (and drops) sp_Query - many improvements have been made over the old sp_Query.
			----> QueryHistory table - added new columns as a result of sp_Sessions - Blocking_Session_id,Status, Open_Transaction_Count, Percent_Complete,Client_Net_Address,Wait_Time,Last_Wait_Type
			----> usp_LongRunningQueries - Changed to use sp_Sessions and modified SELECT to look at temp table before INSERTing into QueryHistory, improving performance and overhead.
			----> rpt_HealthReport - Fixed bug that caused failure when a database has been deleted, but records still exist in DatabaseSettings table and SchemaTracking was Enabled (Schema Change section would error out)
			----> rpt_HealthReport - Added section to DatabaseSettings section to show databases that no longer exist on the server, but still contain records in the DatabaseSettings table
			----> rpt_HealthReport - Changed Blocking History to pull historical data the same as the Long Running Queries section
			----> rpt_HealthReport - Added current version of dbWarden to the footer of the report
			----> rpt_HealthReport - Fixed Long running queries section, changed to use RunTime instead of trying to calculate it based on Start_Time and DateStamp.
05/16/2013	Michael Rounds		----> Changed all objects that used master..sysdatabases to use sys.databases instead. (usp_checkblocking, usp_filestats, rpt_healthreport, sp_blocking)
			----> rpt_HealthReport - Added CompatibilityLevel to Database list
			----> rpt_HealthReport - Changed Database section to show Last Backup Date, with red/yellow highlighting for old or missing backups
			----> rpt_HealthReport - Added Owner to SQL Agent Jobs section
			----> rpt_HealthReport - Moved Compatility level to Database Settings section and added Owner to Database Settings
			----> rpt_HealthReport - Added ShowLogBackups to show/hide TLog's from the Backup section
			----> rpt_HealthReport - Added ShowErrorLog to show/hide the Error Log section
			----> usp_JobStats - Added Owner to output
			----> JobStatsHistory - Added Owner column
			----> rpt_HealthReport - Added new sections Database Settings and SQL Server Config, turned On/Off via new AlertSetting, ShowDatabaseSettings and ShowServerConfigSettings
5/15/2013	Matthew Monroe from SSC	2.4.1	----> rpt_HealthReport - removed all SUM() potentially causing a conversion failure
			----> usp_TodaysDeadlocks - removed all SUM() potentially causing a conversion failure
5/14/2013	Michael Rounds		----> Tweaked population of DatabaseSettings table. INSERTS ALL databases into table now, with everything defaulting to 0.
			----> Added INSERT/UPDATE and DELETE Triggers on DatabaseSettings table to ADD or DROP the Database trigger and SchemaChangeLog on INSERT/UPDATE/DELETE.
			----> SchemaChangeLog table will always persist for data preservation purposes
			----> AlertSettings table - Only drop table if the schema is the old version
			----> rpt_HealthReport - Added AlertSettings and DatabaseSettings sections and new @ShowdbWardenSettings to turn On/Off
			----> rpt_HealthReport - IF @ShowdbWardenSettings is enabled, will also display databases NOT listed in DatabaseSettings table
			----> rpt_HealthReport - Added a couple more COALESCE() to avoid NULL HTML reports
			----> sp_query - Added COALESCE() to SQL_text columns to display stalled sessions
5/10/2013	Michael Rounds		----> Added sp_Query and sp_Blocking procs to the Master DB. sp_Query is now used by the LongRunningQueries process.
			----> usp_LongRunningQueries - Changed INSERT into QueryHistory to use EXEC sp_Query
			----> rpt_HealthReport - Added many COALESCE() to the HTML output to avoid producing a blank report
5/2/2013	Michael Rounds	2.4	----> usp_JobStats - Creating temp tables instead of inserting INTO
			----> usp_JobStats - Removed COALESCE's from previous change on 4/24. Causing dates to read 1/1/1900 when NULL. Would rather have NULL.
			----> rpt_HealthReport - Fixed HTML formatting in Job Stats section
			----> rpt_HealthReport - Changed Job Stats section - CREATE #TEMPJOB instead of INSERT INTO
			----> rpt_HealthReport - Changed LongRunningQueries section to use Formatted_SQL_Text instead of SQL_Text
			----> usp_LongRunningQueries - Change TEMP table to use Formatted_SQL_Text instead of SQL_Text
			----> rpt_Queries- Changed to use Formatted_SQL_Text instead of SQL_Text
			----> SchemaChangeLog - SQLCmd is now NULLABLE (added ALTER TABLE for existing installations)
			----> AlertSettings table has changed. AlertContacts table added, separating out email addresses from the AlertSettings table
			----> The following procs were modified to work with the updated schema
			=- usp_CheckFiles
			=- usp_CheckFilesWork
			=- ti_blockinghistory
			=- usp_CPUProcessAlert
			=- usp_LongRunningQueries
			=- usp_LongRunningJobs
			=- rpt_Queries
			=- rpt_HealthReport
	Matthew Monroe from SSC/Michael Rounds		----> rpt_HealthReport - New variables added to AlertSettings to turn sections on/off or show reduced data. Use the [Enabled] BIT in AlertSettings.
	Volker.Bachmann from SSC		----> Changed all SQL Job names from "dba_" to "dbWarden_" ALL OLD JOBS WILL BE DROPPED
			----> Added "[dbWarden]" to the start of all email subject lines
4/26/2013	Michael Rounds	2.3.10	----> usp_CheckFilesWork - Removed "t2" from DELETE to #TEMP3, causing the error
			----> The multi-part identifier t2.FilePercentEmpty" could not be found"
4/25/2013	Michael Rounds		----> rpt_HealthReport - Added MIN() to MinFileDateStamp in FileStats section
			----> rpt_HealthReport - Fixed JOIN in UPDATE to only show last 24 hours of Read/Write FileStats
			----> rpt_HealthReport - Fixed negative file stats showing up when a server restart happened within the last 24 hours.
			----> rpt_HealthReport - Expanded WitnessServer in #MIRRORING to NVARCHAR(128) FROM NVARCHAR(5)
	Matthew Monroe from SSC		----> usp_CheckFilesWork - New proc - Re-factored code out of usp_CheckFiles
			----> usp_CheckFiles - Factored out duplicate code into usp_CheckFilesWork
4/24/2013	Volker.Bachmann from SSC	2.3.9	----> usp_JobStats and rpt_HealthReport - Added COALESCE to MAX(ja.start_execution_date) and MAX(ja.stop_execution_date)
			----> rpt_HealthReport - Added COALESCE to columns in Replication Publisher section of HTML generation.
4/23/2013	Michael Rounds	2.3.8	----> usp_LongRunningQueries - Adjusted INSERT based on schema changes to QueryHistory, Added Formatted_SQL_Text.
	T_Peters from SSC		----> rpt_HealthReport - Modified FileName length in #BACKUPS from NVARCHAR(128) to NVARCHAR(255)
4/22/2013	T_Peters from SSC	2.3.7	----> Fixed issue with usp_FileStats and rpt_HealthReport that caused arithmetic error (added CAST to BIGINT on growth)
4/22/2013	Michael Rounds	2.3.6	----> Simplified usp_LongRunningQueries to use DMV's to gather session information.
			----> Altered QueryHistory table to accomodate changes in usp_LongRuningQueries
			----> Altered rpt_HealthReport to use new QueryHistory schema
			----> Altered rpt_Queries to use new QueryHistory schema
4/17/2013	Michael Rounds	2.3.5	----> Updated Instructions at the top
			----> usp_MemoryUsageStats - Fixed Buffer Hit Cache and Buffer Page Life showing 0 for SQL Server 2012
			----> dbo.FileStatsHistory table, usp_FileStats and rpt_HealthReport procs- Changed NVARCHAR(30) to BIGINT for Read/Write columns, FileMBSize, FileMBUsed, FileMBEmpty in #FILESTATS
			----> rpt_HealthReport - hopefully fixed the "File Stats - Last 24 hours" section to show accurate data
	Matthew Monroe from SSC		----> usp_CheckFiles - Added database names "[model]" and "[tempdb]"
4/16/2013	Michael Rounds	2.3.4	----> Renamed created database from dba to dbWarden
			----> Changed defaults of DatabaseSettings table to OFF for everything. REPLACE CHANGEME in Update to DatabaseSettings to enable
			----> databases you wish to track (this also fixes issues when trying to track a database that is OFFLINE
4/14/2013	Michael Rounds	2.3.3	----> Expanded Cum_IO_GB in FileStatsHistory, usp_FileStats and rpt_HealthReport to NUMERIC(20,2) FROM NUMERIC(12,2)
			----> REMOVED gen_GetHealthReport stored procs for now. BCP has different behaviour in 2012 that needs tweaking
			----> Fixed update in rpt_HealthReport, CASTing as INT by mistake
4/12/2013	Michael Rounds	2.3.2	----> Modified usp_MemoryUsageStats, usp_FileStats and rpt_HealthReport to be SQL Server 2012 compatible.
			----> Fixed bug in rpt_HealthReport - Changed #TEMPDATES from SELECT INTO - > CREATE, INSERT INTO
4/11/2013	Michael Rounds	2.3.1	----> Changed Health Report to only show last 24 hours worth of File Stats instead of since server restart
4/7/2013	Michael Rounds	2.3.1	----> Expanded KBytesRead and KBytesWritten from NUMERIC 12,2 to 20,2 in table FileStatsHistory
			----> Expanded lengths in temp table in usp_FileStats and rpt_HealthReport
3/19/2013	Michael Rounds	2.3	----> Added new proc, usp_TodaysDeadlocks to display current days deadlocks (if tracelog 1222 is on)
2/20/2013	Michael Rounds	2.2.3	----> Fixed a bug in the Deadlock section where some deadlocks weren't included in the report
1/16/2013	Michael Rounds	2.2.2	----> Fixed a bug in usp_LongRunningJobs where the LongRunningJobs proc would show up in the alert
1/7/2013	Michael Rounds	2.2.1	----> Fixed Divide by zero bug in file stats section
12/31/2012	Michael Rounds	2.2	----> Added Deadlock section when trace flag 1222 is On.
12/27/2012	Michael Rounds	2.1.2	----> Fixed a bug in usp_filestats and rpt_healthreport gathering data on db's with different coallation
12/17/2012	Michael Rounds	2.1.1	----> Changed usp_filestats and rpt_HealthReport so use new logic for gathering file stats (no longer using sysaltfiles)
11/27/2012	Michael Rounds	2.1	----> Tweaked Health Report to show certain elements even if there is no data (eg Trace flags)
11/5/2012	Michael Rounds	2	----> New database trigger, many HealthReport changes, small bug fixes, added data dictionary
9/11/2012	Michael Rounds	1.4.1	----> Updated HealthReport, merged Long Running Jobs into Jobs section
8/31/2012	Michael Rounds	1.4	----> NVARCHAR now used everywhere. Updated HealthReport to be stand-alone
2/29/2012	Michael Rounds	1.3	----> Added CPU stats gathering and Alerting
2/20/2012	Michael Rounds	1.2.2	----> Fixed Blocking alert trigger bug when cell list is null
2/16/2012	Michael Rounds	1.2.1	----> Added separate values for Email and Cell notifications; Display Server Uptime; bug fixes
2/9/2012	Michael Rounds	1.2	----> New sections to the HealthReport; more compatibility bug fixes
1/17/2012	Michael Rounds	1.1.1	----> Replaced CURSORS with WHILE LOOPS
1/12/2012	Michael Rounds	1.1	----> Cleanup,many bugfixes
6/1/2011	Michael Rounds	1	----> Original Version

[Michael Rounds]

Hello,
This bug should be resolved in the latest file posted on this site. Please download the latest version and your issue should be fixed.

[Andy]

Hello --

I had a follow-up. I saved the output of the file to an rpt file, but I am hoping to generate a file similar to that which is shown on your site. What steps are required to accomplish this?

[Michael Rounds]

The HealthReport will email an HTML file. You can also find the HTML blob in the HealthReport table. There are stored procedures for extracting the HTML to a file, or to resend it via email.

Hope this helps.

[Michael Rounds]

These errors you experienced have been resolved. The new version is available to download. Thanks! If you have DBMail setup properly, you will receive the health report via email.

[Michael Rounds]

This issue is fixed in the next release. It's because of a NULL value encountered during the HTML generation. It causes the entire HTML blob to blank out.

The new version, 2.4.1 will be released tomorrow.