Feature Request: Maintenance Mode

[dv2287]

Hi David,

we have noticed that sometimes DBA Dash interferes with some of our maintenance actions and blocks DBA queries so we have to kill DBA Dash processes or even stop the service entirely from Service Configuration tool to continue our work.
In order to prevent such behaviour, I would suggest implementing a Maintenance Mode feature (similar to SCOM and other monitoring tools) which could allow users to temporarily pause the DBA Dash activity for selected instance(s) and suppress checks/alerts during a defined maintenance period.
To make this feature user-friendly, it would be great to implement it in DBA Dash GUI - for example, in instances tree view as a context menu option on root, group and instance level (for setting the MM on all instances, multiple by group/tag or single instance).
This functionality could be particularly useful in the context of the new alerting feature introduced in version 3.17.

Thank you for considering this suggestion.

[DavidWiseman]

Hi, I'd be interested to know which queries are causing the blocking and what actions they are blocking. If you could provide some more details that might be useful.

An option to pause/resume collections for an instance could probably be added in the GUI at some point. It would use the Messaging feature which allows the GUI to communicate with the service.

[dv2287]

Hi,

we have noticed it when setting the single user mode for databases. DbaDash user obtains that connection and prevents you from further actions. It happens on user databases and also on admin database to which DBA Dash service connects to (not master in our case).
This was the DBA Dash process/query when I managed to recreate the issue on the admin database:

SELECT ja.job_id,
DATEADD(mi,@UTCOffset,ja.run_requested_date) AS run_requested_date_utc,
ja.run_requested_source,
DATEADD(mi,@UTCOffset,ja.queued_date) AS queued_date_utc,
DATEADD(mi,@UTCOffset,ja.start_execution_date) AS start_execution_date_utc,
ja.last_executed_step_id,
DATEADD(mi,@UTCOffset,ja.last_executed_step_date) AS last_executed_step_date_utc,
GETUTCDATE() as SnapshotDate
FROM msdb.dbo.sysjobactivity ja
WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
AND ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL

EXEC msdb.dbo.sp_help_job @execution_status=0

If you try to change the properties of that database, SSMS throws an error:
Database 'DBA' is already open and can only have one user at a time. (Framework Microsoft SqlClient Data Provider)

I also noticed blocking caused by DBA Dash when setting service broker on admin database (ALTER DATABASE DBA SET ENABLE_BROKER), but I'cant find the blocking query anymore.

Hope this helps.

[DavidWiseman]

Thanks, If you are setting the DB as single user or enabling service broker (which requires exclusive access), any open connections to the DB from any app will be a problem. Most of the DBA Dash queries will be run under the context of the initial catalog set in the connection string in the config tool. If this is the Admin database it would explain the blocking there. A few collections execute per database which could also explain it.

It's possible that simply pausing the collections might not work - with connection pooling the connections might remain open until the process is shutdown. You might also have issues with the SSMS GUI and other apps holding connections to your DB. The connection just needs to be open - it doesn't need an active query to be an issue for single user.

To get exclusive access to a database and kick other sessions out, you can use the WITH ROLLBACK IMMEDIATE or ROLLBACK AFTER options. This should work more reliably than killing connections individually.

e.g.

USE DBNameHere
GO
ALTER DATABASE DBNameHere 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/* Do your thing here */

ALTER DATABASE DBNameHere
SET MULTI_USER

[dv2287]

Thanks for explanation. Maintenance mode functionality, however, could still be useful in such scenarios so it's worth considering it's implementation (earlier there were also few similar requests/issues posted here that could also be covered with this).