Weekend Task needs to run without users logged in
-
I need to run a scheduled task that requires all users to be out of the system.
I need to run it say from a Saturday afternoon until the Sunday night.
Quite often users are in until late Friday and Saturday morning but forget to logout.
What methods are there to make sure they are logged out and stay out until after the Sunday night.
I know I can make the db dbo only but need the existings one out.
thanks
-
Forcibly terminate all user processes as step 1 of your scheduled task - here's a stored proc that I got from somewhere that does this, for a named db:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--Type procedure
--author mak mak_999@yahoo.com
--date written 4/19/2000
--project maintenance
--objective procedure to kill process for a given database
ALTER procedure usp_killprocess @dbname varchar(128) as
set nocount on
set quoted_identifier off
declare @kill_id int
declare @query varchar(320)
declare killprocess_cursor cursor for
select a.spid from sysprocesses a join
sysdatabases b on a.dbid=b.dbid where b.name=@dbname
open killprocess_cursor
fetch next from killprocess_cursor into @kill_id
while(@@fetch_status =0)
begin
set @query = 'kill '+ convert(varchar,@kill_id)
exec (@query)
fetch next from killprocess_cursor into @kill_id
end
close killprocess_cursor
deallocate killprocess_cursor
--usage
--exec usp_killprocess "mydatabasename"
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I use this as part of a scheduled backup live/restore to development (kill users before performing the restore) task and it works fine.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
That's a bit brutal; just kicking them out without any warning is a little unfair and not going to do much for your popularity.
I have a system which runs a batch process daily and we wanted our users to be out of it during the run. So, we have a job which starts 30 minutes before the actual job runs, which updates a table, with "Closing in 15 minutes", "10", "5", "Closed". The client program interrogates this table every few minutes and displays a message box to the user. The user then has 15 minutes warning of the system going down. When the status hits closed, the client program terminates. Yes, I know that is also brutal, but at least they've had 15 minutes notice.
I also allow a 15 minute cooling off period, so that in case of a very urgent update required, we can get in and do it.
Then the batch process runs, and the final step in the job is to re-open the system.
We've had it in place for 8 months now without any difficulties.
-
It's only brutal if the users are actually working at the time. If you reread the original post, the suggestion is that the users who are still logged in on a Saturday afternoon have stopped work, yet forgotten to log out.
I agree that your method is more polite in the event that there may be users still working when it runs.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
yes, we use a similar script, but previous to the script running we issue a net send <domain> or net send to a specific list of users who are logged in warning that there connection will be severed at xx:xx:xx
a second message is issued 5 minutes before the kill command is processed.
although the idea of a cooling off period is kinda neat! i'll be implementing that shortly.
-- Alex
-
brutforce way :
ALTER DATABASE yourdb Set READ_only WITH ROLLBACK IMMEDIATE
ALTER DATABASE yourdb Set READ_WRITE WITH ROLLBACK IMMEDIATE
if you want to have a criteria : sp_lock
create table #tmp_splock (
spid int not null
, dbid int not null
, ObjId int not null
, IndId int not null
, Type char(3) not null
, Resource varchar(128) not null
, Mode varchar(8) not null
, Status char(6) not null
)
insert into #tmp_splock
exec sp_lock
if exists (select *
from #tmp_splock
where mode in ('IX','U','X','IU')
and dbid = db_id('yourdb'))
begin
print 'use brute force'
alter database YOURDB Set READ_only WITH ROLLBACK IMMEDIATE
ALTER DATABASE YOURDB Set READ_WRITE WITH ROLLBACK IMMEDIATE
end
else
begin
print 'no problem'
end
drop table #tmp_splock
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for the response.
I will take the suggestions on board.
Alan
-
can this be done for a specific table????
-
The only way to get exclusive use of a tabe is to use a tablockX locking hint.
You will not be granted the lock until all other users have finished accessing the table.
-
Thanks for this, SSCrazy. I just implemented your solution because of one problem user who never shuts his queries. Old post but very helpful.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply