Database going into Restricted user mode after successfull job completion

Posted on 2015-01-28
Last Modified: 2015-02-02
Hi, we have a SQL Job and even when the job successfully runs , the database is going to restricted user mode for some reason which is a becoming a problem for the users as they cannot connect
to the DB.

Can anyone please let me know what might have gone wrong with the job ?

Many Thanks
Question by:gvamsimba
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40575421
What that job does?

Author Comment

ID: 40575448
Hi Vitor,

it is a Datawarehouse overnight Job. It basically has 8 steps where it connects to the transactional databases and
loads the datawarehouse overnight. in the first step, it creates snapshot of the DB, then locks the datwarehouse, then loads all the dimension and fact tables, and finally unlocks the datawarehouse.

In case if this job fails, there is one final step which actually restores the datawareouse from the snapshot created in step1.
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40575459
Seems like for guarantee that the process runs well it set the database to restricted mode so no one can connect to the database during the process but somehow it don't put back the database in the previous mode. The command is ALTER DATABASE DBNameHere SET MULTI_USER
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 40575614
Hi Vitor, yes I know the command , but I need to know why is the job not putting back the database in the previous mode ?
LVL 51

Accepted Solution

Vitor Montalvão earned 500 total points
ID: 40575616
The command is there? If so, you should review the job steps. Maybe is skipping something.
We can't help you without the job code.

Author Closing Comment

ID: 40583659
Yes there was a syntax error in the step which actually unlocks the DW

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question