• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 117
  • Last Modified:

Database going into Restricted user mode after successfull job completion

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
0
gvamsimba
Asked:
gvamsimba
  • 3
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What that job does?
0
 
gvamsimbaAuthor Commented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
gvamsimbaAuthor Commented:
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 ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
gvamsimbaAuthor Commented:
Yes there was a syntax error in the step which actually unlocks the DW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now