Delete Job wants to lock table

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Delete Job wants to lock table

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2009-09-29 : 10:28:46

I setup what I thought would be a simple job to archive some data...

Step 1

INSERT INTO WorkTrack_Archive.dbo.t_spn_item_count_hist
Select * FROM WorkTrack.dbo.t_spn_item_count_hist
WHERE xCollectionDT < DATEADD(m,-1,GETDATE())

Step 2

DELETE FROM WorkTrack.dbo.t_spn_item_count_hist
WHERE xCollectionDT < DATEADD(m,-1,GETDATE())

Step 1 worked just fine, but Step 2 gave me a lock error:

“The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users.”

Is there a way where I can have step 2 not attempt to lock the table?

TIA,

Ken

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-16 : 17:28:27

You can delete in smaller chunks.

while 1 = 1
begin
DELETE top(10000) FROM WorkTrack.dbo.t_spn_item_count_hist
WHERE xCollectionDT < DATEADD(m,-1,GETDATE())
if @@rowcount = 0 break
end

N 56°04'39.26"
E 12°55'05.63"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-16 : 17:43:55

SQL Server has to obtain locks in order to make data modifications. Locks are there to protect your data.

You are getting that error because of the size of your batch and lack of resources to support it. Like Peter said, delete in smaller chunks to avoid this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2009-11-17 : 06:29:22

Thanks - Increasing the frequency of when this job was run did the trick, since there was less data to delete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources