Solved

Disable Single user mode SQL

  • 19 January 2023
  • 3 replies
  • 215 views

Badge +2

Hello,

 

To prevent access to the CommServe DB in single user mode we might want to enable the SQL option AUTO_UPDATE_STATISTICS_ASYNC .

Now the big issue/question is will this break anything in Commvault, as we are running CommSeve LiveSync and in BOL I cannot find any reference to this setting.

 

Anyone have any experience with this setting?

 

 

BR,

Luís Constantino

icon

Best answer by Orazan 19 January 2023, 19:12

View original

3 replies

Userlevel 6
Badge +15

Good afternoon.  You must have the database in Full Recovery Mode with CommServe Live Sync.  We are basically doing log shipping with monitoring for availability.  As you can see here, Full is required:

https://documentation.commvault.com/2022e/essential/129979_replication_of_sql_databases.html

“Note: You can use the replication feature only with SQL databases that use the full recovery model.”

Userlevel 7
Badge +23

Hello,

 

To prevent access to the CommServe DB in single user mode we might want to enable the SQL option AUTO_UPDATE_STATISTICS_ASYNC .

Now the big issue/question is will this break anything in Commvault, as we are running CommSeve LiveSync and in BOL I cannot find any reference to this setting.

 

Anyone have any experience with this setting?

 

 

BR,

Luís Constantino

 

I very much doubt this has been tested from our perspective since there isn't any need to. Be interesting to see if anyone in the community has done this (and interesting find that this is an inadvertent way to disable single user mode).

Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:

  1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

  2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.

If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

Badge +2

I assumed that LiveSync would prevent the usage of the setting but asI could not find any specific mention of the setting I though lets ask here.

 

Still are there any alternative ways known to secure the CSDB from single user mode known?

Reply