dbWarden for SQL Server / Blog: Recent posts

This update adds support for Case Sensitive servers, which had been requested by quite a few people.

Also added an Orpahned Users section to the Health Report. This can be turned on/off in the AlertSettings table.

This update fixes a big bug for users who upgraded from a previous version of dbWarden. There were missing entries for LogFile and TempDB alerts for MinFileSizeMB. This was preventing the usp_CheckFiles from reporting any Log file or TempDB growth!

Running this update will make sure these records exist in the AlertSettings table (There are also defaults in place in case these records don't exist, the alerts will still work).... read more

A few big features and many bug fixes in this release. Refer to the Wiki for the complete changelog.

The biggest part of this release is the addition of sp_Sessions, replacing sp_Query. sp_Sessions is a complete rewrite from sp_Query (which was a temporary workaround after removing integration with another utility).
The QueryHistory table has been expanded to collect the new data that sp_sessions gathers.... read more

There are a bunch of bug fixes and a couple big changes are in this release.

Blank HealthReports and errors in the Deadlock section are hopefully a thing of the past!

The biggest change was new triggers on the DatabaseSettings table.
When you INSERT a record into this table and SchemaTracking is set to 1, the SchemaChangeLog table and Database trigger will automatically be installed.
When you UPDATE the SchemaTracking column to 1, the SchemaChangeLog table and Database trigger will automatically be installed if they don't already exist.
When you UPDATE the SchemaTracking column to 0, the Database trigger will automatically be dropped if it exists. The SchemaChangeLog table will remain for historical data preservation.
When you DELETE a record in DatabaseSettings, it will drop the Database trigger.... read more

A lot of fixes and a few enhancements are in this release.
Read through the change log, and be sure to note that all existing jobs named "dba_*" will be dropped in this script!

Thanks to everyone for their help in finding bugs/fixes and for your suggestions!!

Changelog

• 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
• rpt_HealthReport - New variables added to AlertSettings to turn sections on/off or show reduced data. Use the [Enabled] BIT in AlertSettings.
• 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

dbWarden 2.3.9 has been released! A lot of fixes in the health report and reporting of tempDB and Log file growth. Thanks to Volker.Bachmann and Matt Monroe for their work!

Note - There is a new proc, usp_CheckFilesWork. It helps simplify reporting on tempDB and log file growth

Changelog:

• 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.
• 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)
• usp_CheckFilesWork - New proc - Re-factored code out of usp_CheckFiles
• usp_CheckFiles - Factored out duplicate code into usp_CheckFilesWork

dbWarden 2.3.8 is available for download

Please note, you do not need to drop your existing database to run updated versions. The script will make any necessary schema changes to your existing database.

Chanagelog

• QueryHistory table - added Formatted_SQL_Text column. (There are now two columns storing the SQL_Text.
  One will show the raw text as it's stored, the new column will show it formatted. For example, If someone is executing a stored procedure, you will see the parameters passed to the proc instead of the CREATE proc syntax like you would in the raw text.)
• usp_LongRunningQueries - Adjusted INSERT based on schema changes to QueryHistory, Added Formatted_SQL_Text.
• rpt_HealthReport - Modified FileName length in #BACKUPS from NVARCHAR(128) to NVARCHAR(255)

The arithmetic overflow issue is resolved. dbWarden 2.3.7 is available for download.

• Fixed issue with usp_FileStats and rpt_HealthReport that caused arithmetic error (added CAST to BIGINT on growth)

Changelog:

• 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

A release with bug fixes will be along shortly. We are currently addressing the arithmetic error encountered in the usp_FileStats proc which affects usp_Checkfiles and rpt_HealthReport.

dbWarden 2.3.5 is now available! Please note, we changed the default database name from "dba" to "dbWarden"

There are also a few table changes, but the script contains safe alters to correct existing installations.

Please let me know if you encounter any issues. We tested on 2005, 2008R2 and 2012 SP1 with databases up to 1TB.

Changelog:

• 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
• 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
• usp_CheckFiles - Added database names "[model]" and "[tempdb]"

We released a script to drop all objects created by dbWarden. This script will NOT drop any databases.

We've published an updated version of dbWarden which address critical issues that people found today. The updated script is available now for download, which is version 2.3.2