I have a simple procedure that I want to run to trim off a
tables records that are older than 180 days. I set the
rowcount to 25,000 in the proc. Lets say there are 54,000
rows older than 180 days. When I execute the proc the first
2 times it runs fine and reports back 25000 rows affected
each time. But when I run it the 3rd time to get the last
4000 rows it hangs. My experience has been that if there
are less rows than what rowcount is set to it just finished
up and reports back what actually was affected. Anybody
have any ideas why it would hang? I have tried this on
multiple servers with the same results.
proc code below:
set rowcount 25000
where event_dt < dateadd(dy,-180,getdate())
print "number of rows affected = '%1!'",@@rowcount
set rowcount 0
Subject: SQL delete problem
X-Mailer: WebNews to Mail Gateway v1.1t
Date: 28 Nov 2012 12:46:53 -0800
X-Trace: forums-1-dub 1354135613 172.20.134.41 (28 Nov 2012 12:46:53 -0800)
X-Original-Trace: 28 Nov 2012 12:46:53 -0800, 172.20.134.41
Xref: forums-1-dub sybase.public.ase.general:31549
Article PK: 74437
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:188.8.131.52) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
Subject: Re: SQL delete problem
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
Date: 28 Nov 2012 15:17:50 -0800
X-Trace: forums-1-dub 1354144670 172.20.134.152 (28 Nov 2012 15:17:50 -0800)
X-Original-Trace: 28 Nov 2012 15:17:50 -0800, vip152.sybase.com
Xref: forums-1-dub sybase.public.ase.general:31550
Article PK: 74439
I would expect the query to delete the last 4000 and return.
Just the obvious (?) questions ...
- have you verified it's not being blocked?
- have you verified it's not doing a table scan or expensive deferred update?
- have you taken a few snapshots of monProcessWaits to see where it's spending most of its time while 'hung'?
- is a trigger being fired and if so, are there any performance/blocking issues with the trigger's query(s)?
- what else does this proc (or parent process) do besides the delete? [could the 'hanging' be occurring elsewhere?]