I am trying to delete a record.

I am trying to delete a record.

The transaction is hanging.

I have check and no blocking appears to be happening.

Can anybody help
Mr_ShawAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kobe_LenjouCommented:
Well, something must be going on ;-)

What's the wait state of the transaction that is deleting the record?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please post the table structure, the indexes on the table, and the delete statement you try to run.
presumable you don't have a good index for the delete.
0
RiteshShahCommented:
if you are deleting many records, I would suggest stop transaction backup, if any, take full backup of database than change database recovery mode to "Simple" and try delete, it will not affect your log file. after finishing this operation, you can start trasaction log backup if you are taking and change the original recovery mode.

if you want this option, than kindly right click on database and go to property, more follow the screen shot.

1.jpg
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Mr_ShawAuthor Commented:
I am not able to post to exact schema... but here is a pseudo schema.. there are also too many indexes to post.

CREATE TABLE [dbo].[test1](
      [ID] [int] IDENTITY(17235698,1) NOT FOR REPLICATION NOT NULL,
      [Text] [nvarchar](200) NULL,
      Area INT
      
 CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FG_Standard]
) ON [FG_Standard]

GO

ALTER TABLE [dbo].[test1]  WITH CHECK ADD  CONSTRAINT [FK_test1_Area] FOREIGN KEY([Area])
REFERENCES [dbo].[Area] ([ID])
GO

0
Kobe_LenjouCommented:
Have you checked the estimated query plan of the delete statement for any anomalies?
0
Mr_ShawAuthor Commented:
Hi Kobe_Lenjou:,

there is no wait state:

I am using spotlight and nothing is showing up as blocking or locking,
0
Mr_ShawAuthor Commented:
Hi Kobe_Lenjou,

I just checked estimated query plan and there are about 20 index scans.

I am not sure what to look for with regards to delete statement anomalies.
0
Umesh_MadapCommented:
please check the blocking with the below code
Check Blocking process
SELECT * FROM master..sysprocesses
    WHERE spid IN (SELECT blocked FROM master..sysprocesses)
    SELECT spid, status, loginame=SUBSTRING(loginame,1,12),
        hostname=substring(hostname, 1, 12),
            blk=CONVERT(char(3), blocked),
        dbname=SUBSTRING(DB_NAME(dbid), 1, 10), cmd, waittype
        FROM master..sysprocesses
        WHERE spid IN (SELECT blocked FROM master..sysprocesses)
            AND blocked=0

Step2
--find main culprit
select       blocked as "Blocker"
      ,count(*) as "Victim Count"
from       master..sysprocesses
where blocked > 0
group by blocked
dbcc inputbuffer (spid)

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the "delete" statement?
can you show the estimated explain plan?
0
Kobe_LenjouCommented:
A wait state and locking/blocking are two different things and not always related.

The point is, what is the delete statement trying to do. Beause if it's not finishing it's waiting onsome kind of resource to finish (could be buffer IO, Latch, Lock, ...)

You can see this in the Activity Monitor of SSMS (probably also in Spotlight, but it has been many years since I used that)

Two columns are of interest:
- Task state
- Wait type
0
Kobe_LenjouCommented:
You have 20 index scans for a detele statement? What's the estimated total cost and estimated rows of this delete statement? (both can be found in the estimated plan)
0
Mr_ShawAuthor Commented:
Hi angelIII,

the delete is a standar:

delete from table1
where ID =123.

I can estimate the plan......It seems massive.. with loads of index scan.
0
Mr_ShawAuthor Commented:
Hi Kobe_Lenjou,

Which part of the plan should I look at?

The final delete operator cost is 0..176462.

I just ran it again I finally deleted one record after 39 seconds.
0
Kobe_LenjouCommented:
I suppose you mean 0.17 for cost.

You say you have over 20 index scans on the table. You you (just for testing) disable all the non-clustered indexes on the table and try the delete again?

Don't do this on a production system during business hours ;-)
0
Alpesh PatelAssistant ConsultantCommented:
May be other select * statement is in Transaction, so it will hang
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have other tables with foreign keys to your table?
0
sventhanCommented:
Check also if there are any trigger involved and dependencies.
0
Mr_ShawAuthor Commented:
yes there are foreign keys to the table.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>yes there are foreign keys to the table.
that explains the plenty of other index scans etc.

please ensure that all the related tables have good indexes on the FK fields in their tables.
0
Mr_ShawAuthor Commented:
thanks
0
Mr_ShawAuthor Commented:
I just added indexes on the largest tables with foreign keys to the table which I am deleting from.

I took it down to 0 seconds for a delete.

I justed to tool SQL Sentry Plan to analyses the execution plan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.