Solved

Database going into Restricted user mode after successfull job completion

Posted on 2015-01-28
6
90 Views
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
0
Comment
Question by:gvamsimba
  • 3
  • 3
6 Comments
 
LVL 48

Expert Comment

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

Author Comment

by:gvamsimba
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.
0
 
LVL 48

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
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:gvamsimba
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 ?
0
 
LVL 48

Accepted Solution

by:
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.
0
 

Author Closing Comment

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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