Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miranwar
Posting Yak Master
125 Posts |
 Posted - 2007-06-26 : 10:40:21
|
Hi I have the following SQL which i am thinking of runing over the course of the week. The SQL is intended to delete approx 100 million rows. I wrote the SQL so that the deletion takes place in small batches to avoid locking. The set rowcount has been set to 5000. However when i ran the sql via the sql agent after 20 mins or so I got a deadlock. I am thinking is there any way to detect locks when running the sql below. If a lock is detectected then kill the session and restart it. Is this Possible some how by querying the sysProcesses table, or is there a best practice to deal with issues like this.
Thanks,
set rowcount 5000
delete from dbo.cglobal where not exists (select 1 from dbo.cdata where dbo.cdata.cfid = dbo.cglobal.cfid)
while @@rowcount > 0
begin
delete from dbo.cglobal where not exists (select 1 from dbo.cdata where dbo.cdata.cfid = dbo.cglobal.cfid)
end
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-26 : 13:53:37
|
| Deadlocked with what kind of process? |
 |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2007-06-27 : 05:35:17
|
I always handle deadlocks through an application so do not know any way of handling them in SQL.
You may want to try something like the following terrible looking fiddle to see if you can avoid
deadloacks completely.
DELETE G
FROM dbo.cglobal G WITH (XLOCK READPAST)
WHERE NOT EXISTS (
SELECT *
FROM dbo.cdata D WITH (NOLOCK)
WHERE D.cfid = G.cfid
)
If deadlocks cannot be avoided the only options I can think of are either writing a small
application to handle the while loop or working out what is causing the contention on dbo.cglobal. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-27 : 06:52:57
|
"set rowcount 5000
delete from dbo.cglobal where not exists (select 1 from dbo.cdata where dbo.cdata.cfid = dbo.cglobal.cfid)
while @@rowcount > 0
begin
delete from dbo.cglobal where not exists (select 1 from dbo.cdata where dbo.cdata.cfid = dbo.cglobal.cfid)
end"
If that is your full code its going to be very inefficient.
You will need, at the least, a WAITFOR to allow other processes to run, and Log backup to get a chance to back some stuff up! Need to make sure its going to COMMIT too - no gain if everything is actually in a Transaction (implicit or otherwise).
And then the exists is potentially a pretty huge resource hog each time too. probably better to get all the PKs for everything to delete first (big hit) and then delete in batches from there (joining to that temp table of PKs)
See also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Bulk+Delete+of+Records
Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-27 : 07:44:29
|
-- might want to batch this first statement
-- and maybe use a permanent work table
-- so that you can restart the process without rerunning the query
select distinct g.cfid
into #a
from dbo.cglobal g
left join dbo.cdata d
on d.cfid = g.cfid
where d.cfid is null
create index ix on #a (cfid)
select * into #b from #a where 1=0
while exists (select * from #a)
begin
insert #b select top 5000 cfid from #a
delete dbo.cglobal where cfid in (select cfid from #b)
delete #a where cfid in (select cfid from #b)
truncate table #b
end
You can now stop and restart this at your leisure.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
DELETE Locking question