Set SINGLE_USER in a scheduled job

Question

Post reply

Set SINGLE_USER in a scheduled job

bbron

SSC Eights!

Points: 936
More actions
February 18, 2004 at 4:48 am

#107275

Hi,
As part of our DayEnd procedure, we have written a procedure that will include all the relevant steps to be done. This procedure is schedules in a job on the actual server. We just start the job.
I wanted to use
Alter Database XXXX Set SINGLE_USER With Rollback Immediate
To ensure that no one else is accessing the system.
When I execute the Day End Procedure on Query Analyser, on another PC, it works perfectly fine, nobody can access until the whole procedure is finished, after which I ised the MULTI_USER command.
However, when I schedule it, it does not work? Has it got to do with permissions?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

If you look at the "step details" of your job history, does it indicate any errors?

Once you understand the BITs, all the pieces come together

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Agree can you post the error?

Permissions

ALTER DATABASE permissions default to members of the sysadmin and dbcreator fixed server roles, and to members of the db_owner fixed database roles. These permissions are not transferable.

Under which security account is your SQL Agent running or is the user who scheduled the job?

bbron SSC Eights! Points: 936 More actions · Answer 3

Hum... I've created a simple procedure with

Alter Database XXXXX Set SINGLE_USER With Rollback Immediate

Waitfor delay '00:00:30'

Alter Database XXXXX Set MULTI_USER With Rollback Immediate

And it works when I scheduled it in the job. Hum... maybe the last time there was another error....

I've not tried on the main procedure cos I'm worried about one thing... what if there's some error in the middle of the execution? Will the database still be set to SINGLE USER and how to change that one cos no connection will go through!!!

jgroseth SSC Eights! Points: 847 More actions · Answer 4

We use the usp_KillUsers stored procedure found here at SSC.com. (This procedure kills all user processes except the calling one.) From a job, we run the Kill procedure, have the job wait a minute or 2 to allow the connections to be killed (using WAITFOR) , in the next step we set the single_user option and then do our processing steps. Has worked exceptionally well.