When there is maintenance performed on the OperationsManagerDW, it may be necesarry to put the DW in single-user or multi-user mode.  Here’s a quick tip on how to get the DW into single-user mode and back to multi-user mode. 

I have a GUI method 1st, then a SQL query below the screenshot.

In order to get the DW out of single-user, just perform the steps below in reverse and select mulit-user. 

  1. Open SQL Server Management Studio and connect to the Instance of SQL where the DW resides
  2. Open the OperationsManagerDW properties by right clicking the DW and select properties
  3. Scroll to the bottom of the options until you reach ‘Restrict Access’
  4. Change the field to SINGLE_USER or MULTI_USER depending on what operations is being performed.

sql_dw_single_user.png

You can also use the query below to remove single-user restriction back to multi-user. Ensure that you are running the query against the ‘master’ db.

1st – Run this query to check for open logins to the DW 

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = ‘OperationsManagerDW’
GO
 
2nd – Run this query to switch from single-user mode
 
exec sp_dboption ‘OperationsManagerDW’, ‘single user’, ‘FALSE’
GO