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
  • 3
  • 3
LVL 45

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 45

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline


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 45

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 Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 43
SQL Query Syntax Error 9 33
Search for strings with a white space in sql 9 28
Calculating Business Hours 19 62
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now