July 30, 2004 at 1:06 pm
Hi All,
I have a DTS package that runs and kills the process that trys to connect to the database during claims load for certain period of time.
The problem is that the job runs but does not stop.
Can anyone help please? This is urgent.
declare @donotdisturb1 varchar(20), @donotdisturb2 varchar(20), @donotdisturb3 varchar(20)
declare @donotdisturb4 varchar(20), @donotdisturb5 varchar(20)
declare @dbglutton varchar(20), @bedtime varchar(20), @feedingtime varchar(20)
declare @criminalspid int
declare @cmd nvarchar(20)
-- This process may be started anytime, it will wait for bedtime
-- to start enforcing the curfew, or start enforcing it immediately
-- if it is started after bedtime. To automate this, schedule it to
-- start whenever the server restarts, and also once a day at bedtime.
-- During the curfew, it will make its rounds every minute.
-- (Schedule via SQL Server Agent, to start before bedtime)...
-- Set the curfew using 24 hour time.
-- Standard Hours:
-- -------------------------------
set @bedtime = '11:54:00'
set @feedingtime = '11:57:00'
-- Specify ethernet mac address (use ping, then arp -a to find it)
-- of any servers that should be allowed access after the curfew.
-- It is assumed that there will be some other control on those
-- machines preventing rogue SQL spids during the curfew. This
-- only works if the machine is on the same network as this DB
-- server - otherwise the mac address won't show up using arp -a
-- and the mac address SQL sees will be of the nearest router.
-- set @donotdisturb1 = '00d0b7bce7c0' -- slice mac address
-- set @donotdisturb2 = '0008c74b66e4' -- dice mac address
-- set @donotdisturb2 = '00306e015429' -- BTSQL1 mac address
set @donotdisturb1 = '000bcd3f6e4f' -- BTCOGNOS mac address
set @donotdisturb2 = '000802e6b367' -- BTSQL10 mac address
set @donotdisturb3 = '00101822230a' -- BTSQLGW mac address
set @donotdisturb4 = '000bcd9b3469' -- BTCOGBI mac address
-- set @donotdisturb5 = '0008c74b66e4' -- BTBSG mac address
-- MAC Addresses as of 20040616
set @dbglutton = 'cognos' --ultimate database glutton
declare curs1 cursor for
select spid
from master..sysprocesses
where status <> 'background'
and spid <> @@spid
and net_address not in (@donotdisturb1, @donotdisturb2, @donotdisturb3, @donotdisturb4)
-- and loginame = @dbglutton
and dbid = 13
/* Switched from loginame filter to database ID
(dbid 13 = BT_Storage, on BTSQL10); this should
kill any process not originating from the
'donotdisturb' servers, but only for the BT_Storage
database... rt/20040616
*/
--Make sure we don't start killing off feeders too early...
waitfor time @bedtime
while convert(varchar(8), current_timestamp, 108) < @feedingtime
--If convert(varchar(40), current_timestamp, 108) < @feedingtime Then
begin
loop
open curs1
while 1 = 1
begin
fetch curs1 into @criminalspid
if @@fetch_status <> 0
break
-- print 'gotcha ' + CAST(@criminalspid as varchar(50))
-- <Use this line for interactive trouble-shooting (run in Query Window)>
set @cmd = 'kill ' + convert(varchar(20), @criminalspid)
exec sp_executesql @cmd
end
close curs1
waitfor delay '00:01:00'
end
deallocate curs1
August 2, 2004 at 8:00 am
This was removed by the editor as SPAM
August 12, 2004 at 5:50 am
what about increasing your RAM amount? SQL Server is monister in dealing with RAM .. and when you increase your Memory then the performance of SQL Server is increased
also do u have SP3a installed ? it solve many problems with SQL Server
also install MDAC 2.7 SP1 on all clients and cerver machines
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply