Releases: rentadba/dbaTDPMon
dbaTDPMon - v2023.10
October 2023 new features & fixes
14.10.2023
- change (un)install and update from batch files to using a single powershell script
18.10.2023
- health-check: fixed isues when running on linux
September 2023 new features & fixes
16.09.2023
- maintenance-plan: add support for columnstore indexes maintenance (pending on deleted records and deleted segments "fragmentation")
19.09.2023
- maintenance-plan: fix issue related to rebuilding a compressed index which led to removing the data compression option
- maintenance-plan: fix issue related to rebuilding dependent indexes for a primary key - non-clustered indexes were rebuilt twice
- maintenance-plan: fix issue related to running dbcc checktable with (data_purity) even that is was not needed (run only when dbi_dbccFlags=0)
24.09.2023
- maintenance-plan: fix issue related to online index rebuild (in some scenarios offline mode was forced)
July 2023 new features & fixes
26.07.2023
- health-check: collect database file(s) growth events from default trace files into [health-check].[statsDatabaseGrowth]
May 2023 new features & fixes
10.05.2023
- maintenance: add support for remote maintenance of system databases
15.05.2023
- maintenance: fixed issues related to alerts when running remote maintenance against a read-only secondary replica
dbaTDPMon - v2022.02
February 2022 new features & fixes
20.02.2021
- health-check: add AlwaysOn Availability Group details into the health check report
- health-check: collect database backup information taken with other tools then dbaTDPMon (enhance Backup Size details in the HTML report)
- health-check: fixed minor bugs on data collection
January 2022 new features & fixes
31.01.2021
- health-check: add more details for AlwaysOn Availability Group collected information
February 2021 new features & fixes
14.02.2021
- fix deadlock occurring when generating queues for running multiple parallel jobs
- fix issue related to replication monitoring when running in single thread mode
16.02.2021
- fix issue with "Invalid object name '##tdp_xp_cmdshell_requests'" when running in high concurrency / parallel jobs
- create a new log table "[dbo].[logInternalConfigurationChanges]" to list temporary changes to instance options, when performed
19.02.2021
- fix error 3906: Failed to update database "%" because the database is read-only"; do not allow DBCC CHECKCATALOG to be executed on secondary AG replicas
22.02.2021
- health-check: fix small issues on information displayed within the HTML report
dbaTDPMon - v2021.01
January 2021 new features & fixes
06.01.2021
- fix bug on cleansing/deleting intermediate SQL Agent jobs created for parallel execution
10.01.2021
- allow installation/objects creation into an existing database / do not force "own" database (reported by Alexey Sviridov)
- fix minor bugs related to upgrade from previous versions
- fix issues when destination database collation was different from the server one
- health-check: fix table format on backup size details report section
- fix "Warning: Null value is eliminated by an aggregate or other SET operation." messages in the execution logs
14.01.2021
- maintenance-plan: fix backup directory creation when running under Linux
21.01.2021
- release of dbaTDPMon - v2021.01
December 2020 new features & fixes
14.12.2020
- fix bug on processing database details, health-check module (reported by George Talaba)
June 2020 new features & fixes
17.06.2020
- reduce tempdb metadata contention (replace truncate on temp tables with delete)
19.06.2020
- fix infinit loop execution in dbo.usp_jobQueueGetStatus (reported by Razvan Puscasu)
February 2020 new features & fixes
28.02.2020
- fix issue on checking existence / creating network folder
January 2020 new features & fixes
22.01.2020
- fix installation issue when placing project_code parameter between quotes (reported by Razvan Puscasu)
- health-check: fix small bug on replacing the html report file path space with underscore (reported by Razvan Puscasu)
dbaTDPMon_v2021_1.zip
dbaTDPMon - v2020.01
January 2020 new features & fixes
14.01.2020
- change license type from GPL-v3 to MIT. My gift to the community. Enjoy it!
December 2019 new features & fixes
14.12.2019
- maintenance-plan: stored procedure for defining default parallel maintenance jobs for a project (dbo.usp_mpJobProjectDefaultPlanCreate)
- fix update failures when running on a different database collate vs. the server one
- maintenance-plan: enhance mechanism for defining internal jobs execution order
15.12.2019
- add a non zero retry value for own SQL Agent jobs steps
16.12.2019
- maintenance-plan: fix job execution statistics view (issue reported by Handerson Dutra)
20.12.2019
- small fixes on health-check, maintenance-plan and integration modules
dbaTDPMon - v2019.12
December 2019 new features & fixes
03.12.2019
- project removal when removing the last instance assigned
- health-check: collect also internal job execution history from "monitored" instances (dbo.jobExecutionHistory)
05.12.2019
- add check for SQL Agent service before starting executing jobs in parallel mode
10.12.2019
- add code for detecting databases not managed by dbaTDPMon utility
November 2019 new features & fixes
03.11.2019
- monitoring: fix small bug when detecting active/running sessions
- maintenance-plan: fix small bugs when displaying messages during maintenance run time
- health-check: add option to run the daily report for a single instance
- health-check: add database(s) growth information for the last X days to the daily report
05.11.2019
- health-check: add database(s) backup size information for the last X days to the daily report
14.11.2019
- fix small performance issue on saving internal job execution statistics
29.11.2019
- maintenance-plan: do not create jobs for databases on an AlwaysOn Availability Group Non-Readable Secondary Replica setup if denied by dbo.appConfigurations
October 2019 new features & fixes
07.10.2019
- improved performance for internal log messaging by running some heavy queries only if an email must be sent
- maintenance-plan: fix issue when calling maintenance code for objects having . (dot) in their name (reported by George Talaba)
13.10.2019
- monitoring: improved performance for transaction status; include the actual SQL code in the collected data / alerts sent
- option to add/remove databases from a project using name filters
22.10.2019
- health-check: incremental read for errorlog files (read only new events)
September 2019 new features & fixes
07.09.2019
- add support for Azure SQL Managed Instances
- clean update scripts: allow only update from v2019.7 onwards
19.09.2019
- maintenance-plan: fix small bugs when running backup against Azure MI
29.09.2019
- maintenance-plan: add code for data copy between database / tables (dbo.usp_mpTableDataSynchronizeInsert). Add support for on-premise to Azure data copy
August 2019 new features & fixes
20.08.2019
- health-check: fix issue when computing growth for databases in AG and switching nodes
July 2019 new features & fixes
27.09.2019
- monitoring: enhance performance for failed jobs detection (dbo.usp_monGetSQLAgentFailedJobs)
dbaTDPMon - v2019.7
July 2019 new features & fixes
12.07.2019
- code tested and ready for Azure SQL Database
June 2019 new features & fixes
03.06.2019
- optimize the calls to msdb system tables when running an internal job queue
07.06.2019
- health-check: option not to display outdated backups/dbcc for database snapshot(s)
09.06.2019
- health-check: add stored procedure for reporting database(s) and backup file(s) storage capacity
10.06.2019
- health-check: add stored procedure for reporting database(s) growth
14.06.2019
- health-check: enhancements to the daily health-check report: add Capacity Analysis category
17.06.2019
- maintenance-plan: option to allow/block maintenance operations on a non-readable AlwaysOn secondary replica
- maintenance-plan: add time limit option for the consistency tasks in maintenance plan
- health-check: collect secondary replica readable state in [health-check].[statsDatabaseAlwaysOnDetails] table
- add time limit for executing internal jobs (dbo.usp_jobQueueExecute)
18.06.2019
- integrations: stored procedure for running dbcc checkdb in a time limit mode / for all databases on all servers
- fix issue which allowed running more jobs in parallel than the @parallelJobs parameter value
19.06.2019
- integrations: optimize the dbcc checkdb queue priority to take into account the run time, max dop and previously execution times
- allow executing a job queue across multiple projects, not project by project
22.06.2019
- health-check: remove unnecessary calls to sys.dm_os_volume_stats
May 2019 new features & fixes
10.05.2019
- health-check: add option to limit the reported reclaimable data and log space; default beyond 10 GB
28.05.2019
- health-check: improve performance for fetching AlwaysOn details data
31.05.2019
- add SQL Agent job_id information to dbo.jobExecutionQueue table
March 2019 new features & fixes
01.03.2019
- health-check: add more messages to be filtered out from daily HTML report, errorlog section
05.03.2019
- health-check: add failed login attempts information on daily HTML report
- maintenance-plan: add option to trigger a log backup and log shrink (truncate only) after index maintenance
11.03.2019
- maintenance-plan: remove indexes no longer in use
- maintenance-plan: add option to wait for estimated recovery time to be 0 before shrink if a database is part of AlwaysOn AG
15.03.2019
- monitoring: fix "Arithmetic overflow error converting expression to data type int" for transaction status monitoring
18.03.2019
- maintenance-plan: fix issue on smart differential backup triggering at transaction log backup
29.03.2019
- health-check: enhance dbo.usp_hcCollectOSEventLogs stored procedure (add @configEventsInLastHours parameter)
February 2019 new features & fixes
12.02.2019
- health-check: add option to the daily health check report not to filter out databases per assigned project
- health-check: have database count listed in daily report
January 2019 new features & fixes
09.01.2019
- maintenance-plan: implement smart differential backup; default threshold at 50% (appConfigurations and stored procedure option)
- maintenance-plan: force update statistics when the number of changes >= sqrt(1000 * rowcount)
11.01.2019
- maintenance-plan: do not run maintenance for indexes not used in the last X days (appConfigurations option)
29.01.2019
- maintenance-plan: fix small bug on redo mechanism for internal foreign key changes
- maintenance-plan: fix bug on mechanism for detecting heavy fragmented indexes
31.01.2019
- maintenance-plan: fix small bug when generating internal jobs on systems with rotating database snapshots
December 2018 new features & fixes
10.12.2018
- monitoring: add support for replication monitoring when distributor is on a remote instance
17.12.2018
- enhance caching for getting SQL Server version and edition
26.12.2016
- maintenance-plan: do not allow database backup while shrink is in progress
- maintenance-plan: do not allow index maintenance operations while shrink is in progress
- maintenance-plan: add option to create statistics on all table columns, not only on the indexed ones
27.12.2016
- maintenance-plan: fix defect with internal job scheduler and update statistics task
November 2018 new features & fixes
13.11.2018
- maintenance-plan: fix a bug when updating statistics with fullscan on SQL Server 2016 onwards
- remove all SQL Server 2000 related code
dbaTDPMon - v2018.11
November 2018 new features & fixes
01.11.2018
- discontinued support for SQL Server 2000 (latest version supporting SQL Server 2000 is 2017.12)
- health-check: fixed small bugs when running on SQL Server 2008
October 2018 new features & fixes
10.10.2018
- allow definition of more than 32k internal jobs in jobExecutionQueue table (smallint to int change)
17.10.2018
- reduce CPU usage for the dbo.usp_sqlAgentJobCheckStatus stored procedure
24.10.2018
- fix a small bug while getting the host domain name
27.10.2018
- health-check: use powershell to get the volume disk space as 1st option; use volume_stats as 2nd
30.10.2018
- implement caching for getting SQL Server version and edition
- health-check: add default 367 days retention for historical and log tables
- maintenance-plan: avoid a deadlock scenario when performing system databases maintenance
- maintenance-plan: reduce the number of calls to AlwaysOn / cluster related dvms
31.10.2018
- implement caching for getting database properties information
- maintenance-plan: allow index fragmentation analysis for read_only databases
September 2018 new features & fixes
17.09.2018
- monitoring: fix defect - analyzing replication status for an offline database
22.09.2018
- maintenance-plan: skip read-only databases from index, statistics or heap maintenance
- maintenance-plan: skip backups on database snapshots as they are not allowed
27.09.2018
- health-check: fix small issue on daily HTML report data layout
- for internal jobs, save database_name in dbo.jobExecutionHistory
August 2018 new features & fixes
15.08.2018
- maintenance-plan: fix defect when triggering full database backup after an Availability Group fail-over
- maintenance-plan: fix error 976 when performing backups or consistency checks against a non-readable secondary database
18.08.2018
- maintenance-plan: add active column/flag to the objectSkipList table
20.08.2018
- health-check: enhance the Availability Group databases information collector
21.08.2018
- maintenance-plan: improve performance for [dbo].[usp_mpCheckAvailabilityGroupLimitations] stored procedure; less calls to system dmvs
28.08.2018
- maintenance-plan: mark Allocation Consistency Checks (DBCC CHECKCATALOG / DBCC CHECKALLOC) as heavy operation to lower the pressure when running in parallel
July 2018 new features & fixes
03.07.2018
- health-check: save volume/disk mount point for data/log file physical location (SQL Server 2008 onwards)
- add is_resource_intensive flag for appInternalTasks
06.07.2018
- maintenance-plan: fix deadlock on concurrent projects defining their internal queues
09.07.2018
- fix defect related to duplicate jobs entries in jobExecutionQueue table
- when refreshing projects data, add only databases not allocated to other projects
10.07.2018
- fix XML parsing error when maximum jobs limit alert is triggered
11.07.2018
- health-check: fix disk space details table formatting in the HTML report
- monitoring: auto-detect project code when sending alert emails
16.07.2018
- add new option for job execution: Maximum SQL Agent jobs running on the same physical volume (0=unlimited)
June 2018 new features & fixes
05.06.2018
- add dbo.vw_jobSchedulerDetails view with details on SQL Agent jobs scheduler definition for dbaTDPMon jobs
07.06.2018
- maintenance-plan: fix clusterName path when running backup for a databases not in an AG but on a AlwaysOn setup
- removed obsolete objects from install & update utility
11.06.2018
- monitoring: add option for Transaction Status to exclude alerts based on login_name (monitoring.alertSkipRules skip_value2 column)
13.06.2018
- health-check: fix small defects on health-check report generation for projects having multiple instances
- maintenance-plan: add priority for internal maintenance tasks jobs
13.06.2018
- implemented a workaround for internal jobs left in "in progress" state
20.06.2018
- health-check: convert local time for OS events, errorlog messages and SQL Agent jobs to UTC
- health-check: if possible, the last X hours in health-check report will be filtered on the UTC columns
- add new option for job execution: Maximum job queue execution time (hours) (0=unlimited)
28.06.2018
- send custom alert when the number of concurrent running internal jobs reached the limit set
- maintenance-plan: fix deadlock on concurrent projects defining their internal queues
May 2018 new features & fixes
30.05.2018
- replace EXEC() with EXEC sp_executesql to reduce the CPU usage for the dbaTDPMon utility
31.05.2018
- maintenance-plan: in an AlwaysOn Availability Group environment, save the database backups in ClusterName folder
dbaTDPMon - v2018.5
May 2018 new features & fixes
23.05.2018
- add MAXDOP option for update statistics on SQL Server 2016 SP2
16.05.2018
- do not allow maintenance tasks to run against objects in "offline" filegroups (Msg 1931, Level 16, State 3, Line 7: The SQL statement cannot be executed because filegroup '...' is offline.)
02.05.2018
- fix defect when forcing to stop a running SQL Agent job
01.05.2018
- maintenance-plan: fix defect when generating job queue on a a very large number of databases
- add maximum value for how many internal jobs can be started on a system (Maximum SQL Agent jobs running property)
April 2018 new features & fixes
30.04.2018
- maintenance-plan: fix date/time value used for file backup when running against a remote server
- monitoring: allow SQL Agent jobs to be excluded from transaction status alerts
27.04.2018
- fix defects on SQL Agent job handling: start/stop under high concurency
26.04.2018
- fix small defects related to easy of use on large deploymnets (health-check, maintenance-plan, job queueing)
24.04.2018
- maintenance plan: fix defect when rebuilding heaps with ONLINE=ON
23.04.2018
- include dbo.jobExecutionStatisticsHistory in internal retention policy
- health check: do not show saved detailed information in the daily report for inactive instances
18.04.2018
- add mechanism for additional recipients on monitoring alerts
17.04.2018
- enhance internal job queue execution mechanism (add serial mode and option to skip the execution using SQL Agent jobs) (developed by Razvan Puscasu)
16.04.2018
- added new option to database transaction log backup: 8192 - use tail log backup - NORECOVERY (developed by Razvan Puscasu)
- enhance project specific scheduler for maintenance-plan internal tasks
- enhance [maintenance-plan].[vw_objectSkipList] table: add instance/database information
- add MAXDOP option for update statistics (starting with SQL Server 2017 CU3)
13.04.2018
- added database_id in the name of the internal maintenance sql agent jobs
11.04.2018
- add jobExecutionStatistics, live and history, mechanism for internal parallelism and jobs execution stats (developed by Razvan Puscasu)
10.04.2018
- add function [dbo].[ufn_getProjectCode](@sqlServerName, @dbname) (merging code with a 3rd party client)
- dbo.usp_refreshMachineCatalogs will make use of db_filter property from dbo.catalogProjects table
05.04.2018
- merging code with a 3rd party client: dbo.catalogSolutions, dbo.catalogProjects, dbo.jobExecutionQueue, dbo.jobExecutionHistory
March 2018 new features & fixes
01.03.2018
- add support for ignoring error code 1927: There are already statistics on table [...] (reported by Stefan Iancu)
- disable shrink internal jobs in parallel maintenance plan (reported by Stefan Iancu)
February 2018 new features & fixes
22.02.2018
- maintenance plan: add support for incremental statistics (SQL Server 2014+)
21.02.2018
- maintenance plan: extend support for heap/index partition maintenance (rebuild/reorganize)
20.02.2018
- maintenance plan: fix small bug when building SQL statement for rebuilding index partition
- maintenance plan: add option to rebuild only a heap table partition
15.02.2018
- modify active start date for all jobs to be utility installation day (reported by George Talaba)
13.02.2018
- monitoring: fix long waits for monitoring disk space job when only monitoring module was installed (reported by George Talaba)
01.02.2018
- maintenance-plan: allow updating statistics with default sample
January 2018 new features & fixes
31.01.2018
- monitoring: sql_handle can be used to filter out unwanted alerts ([monitoring].[alertSkipRules])
- maintenance-plan: fix small bug when detecting existence of a full database backup
- health-check: refactoring of daily report header
28.01.2018
- uninstall utility: add user confirmation
- health-check: fix possible bcp out issue when xp_cmdshell is not enabled
- monitoring: fix possible multiple reporting for the same uncommitted transaction
- monitoring: add long session request monitoring alert (long running SQL statements) (requested by Razvan Puscasu)
26.01.2018
- add support for updating from version 15.12, 16.6, 16.9, 16.11 and 17.4 to 17.12
25.01.2018
- update support enhancement: auto detection for installed modules at run-time
14.01.2018
- add utility for update support from version 2017.6 to 2017.12
12.01.2018
- fix dbo.ufn_hcGetIndexesFrequentlyFragmented function when using parallel index maintenance
dbaTDPMon - v2017.12
/----------------------------------------------------------------------/
/* December 2017 new features & fixes
/----------------------------------------------------------------------/
/* 22.12.2017 */
- fix issue when a job failed and it was reported as "in progress" instead of "failed" (parallel maintenance-plan)
- full testing cycle on SQL Server 2000 up to SQL Server 2017
/* 21.12.2017 */
- check for missing single column histograms before running sp_createstats 'indexonly'
/* 20.12.2017 */
- tested maintenance-plan module under SQL Server 2017 on Linux; add support for Linux OS
- custom backup path creation and backup cleanup/retention policy features are disabled on Linux OS
- fix maintenance-plan log backup issue, when switching recovery model from simple to full and back
- fix bug when running dbo.usp_hcCollectDatabaseDetails for databases over 2 TB in size (reported by Razvan Puscasu)
/* 14.12.2017 */
- code review: use a custom logging stored procedure: [dbo].[usp_logPrintMessage]
/* 09.12.2017 */
- threat special characters for all object names, excluding instance name: '!@#$%^&()-={};:`"<>.,/[[ ]~ by enclose all object names using a custom quote function: [dbo].[ufn_getObjectQuoteName]
/----------------------------------------------------------------------/
/* November 2017 new features & fixes
/----------------------------------------------------------------------/
/* 27.11.2017 */
- threat special characters in database name, for backup operation: '!@#$%^&()-={};:`"<>.,/[[ ]
/----------------------------------------------------------------------/
/* October 2017 new features & fixes
/----------------------------------------------------------------------/
/* 02.10.2017 */
- minimize the number of server configuration option xp_cmdshell enable/disable calls
/----------------------------------------------------------------------/
/* August 2017 new features & fixes
/----------------------------------------------------------------------/
/* 17.08.2017 */
- fix a small bug when checking for an existing full database backup and "SQL Server VSS Writer" service was running
/* 15.08.2017 */
- when computing elapsed transaction time, if sys.dm_tran_active_snapshot_database_transactions.elapsed_time_seconds is null, will compute duration based on sys.dm_tran_active_transactions.transaction_begin_time (reported by Razvan Puscasu)
/* 07.08.2017 */
- fix "Unable to post notification to SQLServerAgent (reason: The maximum number of pending SQLServerAgent notifications has been exceeded. The notification will be ignored.)" (reported by Razvan Puscasu)
- fix a small bug when performing orphan log backup files cleanup (reported by Mihail Grebencio)
/----------------------------------------------------------------------/
/* July 2017 new features & fixes
/----------------------------------------------------------------------/
/* 10.07.2017 */
- raise error when backup file name & path are exceeding 259 characters (reported by Mihail Grebencio)
/* 06.07.2017 */
- change procedure parameter names to mixed case
/* 04.07.2017 */
- allow database consistency checks for non-readable secondary replicas in an AlwaysOn environment (reported by Mihail Grebencio)
/----------------------------------------------------------------------/
/* June 2017 new features & fixes
/----------------------------------------------------------------------/
/* 15.06.2017 */
- add [maintenance-plan].[objectSkipList] table, to be used the same as @skipObjectsList or @skipDatabasesList - objects to be excluded from the maintenance / per task (reported by Razvan Puscasu)
/* 14.06.2017 */
- add new parameter to dbo.usp_mpDatabaseConsistencyCheck stored procedure: @skipObjectsList - comma separated list of the objects (tables, index name or stats name) to be excluded from maintenance.
dbaTDPMon - v2017.6
/----------------------------------------------------------------------/
/* May 2017 new features & fixes
/----------------------------------------------------------------------/
/* 26.05.2017 */
- collect AlwaysOn Availability Groups details when gathering health-check databases info (includes data loss in seconds)
- fix health-check false reporting of outdated backups in AlwaysOn Availability Group configuration
/* 25.05.2017 */
- add MaxDOP option when performing dbcc checkdb/table, default 1 (dbo.usp_mpDatabaseConsistencyCheck) (SQL Server 2014 SP2 onwards)
- fix event message XML formatting bug, when object name constain reserved chars (dbo.vw_logEventMessages)
/* 24.05.2017 */
- add new parameter to dbo.usp_mpJobQueueCreate stored procedure: @skipDatabasesList - comma separated list of the databases to be excluded from maintenance.
/* 18.05.2017 */
- add new parameter to dbo.usp_mpDatabaseOptimize stored procedure: @skipObjectsList - comma separated list of the objects (tables, index name or stats name) to be excluded from maintenance.
- add @recreateMode option when generating parallel maintenance SQL Server agent jobs. Old/custom job definitions may be kept
- add MaxDOP option when rebuilding heap tables, default 1 (dbo.usp_mpAlterTableRebuildHeap)
/* 17.05.2017 */
- perform online table/heap rebuild using WAIT_AT_LOW_PRIORITY (SQL Server 2014 onwards)
/* 16.05.2017 */
- update check/limitations when doing online index rebuild based on version/edition
- starting with SQL Server 2014, when doing online index rebuild, use WAIT_AT_LOW_PRIORITY(MAX_DURATION = [..] MINUTES, ABORT_AFTER_WAIT=SELF) option
/* 09.05.2017 */
- add email alerting flood control: allowing maximum 50 messages (default) in a 5 minutes time-frame
/* 08.05.2017 */
- fix bugs on maintenance-plan module when running on an AlwaysOn configuration with multiple groups (mix primary/secondary) (reported by Stefan Iancu)
/* 04.05.2017 */
- fix database shrink error & alert when another database is in a middle of a restore (reported by Razvan Puscasu)
- fix minor bugs on maintenance-plan module
/----------------------------------------------------------------------/
/* April 2017 new features & fixes
/----------------------------------------------------------------------/
/* 28.04.2017 */
- add support for ignoring error code 15281: SQL Server blocked access to procedure
- fix update statistics error when index name contains brackets (reported by Razvan Puscasu)
/* 19.04.2017 */
- fix "change backup type check" from differential to full when running on AlwaysOn AvaulabilityGroups
/----------------------------------------------------------------------/
/* March 2017 new features & fixes
/----------------------------------------------------------------------/
/* 26.03.2017 */
- allow dbcc checks to be made on secondary replicas / AlwaysOn