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 50

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 50

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


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 50

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

738 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