Solved

Unable to delete record

Posted on 2008-06-19
39
486 Views
Last Modified: 2010-08-05
Experts,

I have a record in a database that I need to delete. I can select and update that record with no problems. When I try to delete it, however, it just grinds away and does nothing. It does not return any error messages or anything. I checked my referential integrity and there are no records depending on the one I am trying to delete. Any suggestions?
0
Comment
Question by:MarkRod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 12
  • 5
  • +3
39 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823360
How long do you wait for it to delete before stopping?
0
 

Author Comment

by:MarkRod
ID: 21823379
I let it run for approx 5 mins
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823413
Wow...how many columns does your table have in it?  Are you sure you're not trying to delete the entire table's data?  Post your delete statement.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 31

Expert Comment

by:James Murrell
ID: 21823420
DELETE FROM <TABLENAME>WHERE <ROWETC>= 13  

is that what your are doing?

or are you executing your delete query from an application, does whatever login the application is using have delete privileges on the databases and tables you are trying to delete from?
0
 

Author Comment

by:MarkRod
ID: 21823460
My delete statement is DELETE FROM <Table> WHERE <Primary Key> = 21863

The original query was submitted from a front end application. When that timed out, the client called me. I connected via vpn to the database and attempted to run the delete query from QA. When that didn't work, I called the SQL Admin and had him try to delete the record. Would not work for him either.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823508
HOw many records are in the table?  Is the table HEAVILY indexed?
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 21823509
does record appear when you do
select FROM <TABLENAME>WHERE <ROWETC>= 13  
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823523
quick idea...are there any open long-running transactions in your database?  Your delete statement could be blocked by any of these errant live transactions.
0
 

Author Comment

by:MarkRod
ID: 21823535
Not that I know of, how could I find out?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823550
run sp_lock and see if there are a lot of records for a certain object..that object could be your table.
0
 

Author Comment

by:MarkRod
ID: 21823560
Currently, there are 21,314 records in this table. I have 4 indexes counting the primary key. The field in my where clause is one of the indexed fields.

When I select that record out it appears instantly
0
 

Author Comment

by:MarkRod
ID: 21823589
Most of the 450 locks are for ObjID: 0. How would I tell if thats my table or not? The type is DB.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823594
Its not...but the db might be locked for some reason.  Run this:

dbcc opentran

see if there are any outstanding open transactions.
0
 

Author Comment

by:MarkRod
ID: 21823615
No active open transactions
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823667
And you're sure that there were not locks of type 'TAB' returned from sp_lock?
0
 

Author Comment

by:MarkRod
ID: 21823790
I ran it again and found locks with type TAB. How can I get the ObjID for my table to check?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823802
The objectid is returned by sp_lock.  You can determine if it is your table by this:

select * from sysobjects
where name = 'tablename'
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21824102
Sounds you are being blocked by someone else.

To verify, run this query:

SELECT blocked, *
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE blocked > 0

Hopefully there won't be too many, and you can figure out which spid is blocking you.  Then to get info on that spid, run this:

EXEC sp_who <blocking_spid_number_from_query_above>

After you determine what is blocking you, you can decide the best course to take:
kill the blocker | free up other blocks so the spid blocking you can complete | ...
0
 

Author Comment

by:MarkRod
ID: 21825345
There are only 3 locks on the table.

There are no results when I run the SELECT blocked, * ... code
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21825379
Run this procedure to see if there are any blocking processes:

sp_MSget_current_activity 64,1
0
 

Author Comment

by:MarkRod
ID: 21825486
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '##procinfo64'.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21825513
Must be the version....run this in your master db and then try to run what I gave you above again?
-- =============================================  
-- sp_MSget_current_activity  
-- =============================================  
alter procedure dbo.sp_MSget_current_activity @id int = 0, @option int = 0, @obj nvarchar(386) = null, @spid int = 0  
as  
  
if (@id = 0)  
begin  
    raiserror(N'No SPID specified (spid = %d)', 1, 1, @id)  
    return(-1)  
end  
  
if (@option <= 0 or @option > 5)  
begin  
    raiserror(N'Invalid option %d', 1, 1, @option)  
    return(-1)  
end  
  
declare @stmt as nvarchar(4000)  
  
-- =============================================  
-- make tables SPID depended  
-- =============================================  
declare @locktab as sysname  
declare @proctab as sysname  
  
set @locktab = N'##lockinfo' + rtrim(convert(nvarchar(5), @id))  
set @proctab = N'##procinfo' + rtrim(convert(nvarchar(5), @id))  
  
if (@option = 1)  
begin  
    -- process info (overview of all processes by SPID)  
    set @stmt = N'select [Process ID], [User], [Database], [Status], [Open Transactions], [Command], [Application], [Wait Time], [Wait Type], [Wait Resource], [CPU], [Physical IO], [Memory Usage], [Login Time], [Last Batch], [Host], [Net Library], [Net Ad
dress], [Blocked By], [Blocking], [Execution Context ID] from ' + @proctab + ' order by [Process ID],[Execution Context ID]'  
end  
else if (@option = 2)  
begin  
    -- distinct spid list (old)  
    -- set @stmt = N'select [Process ID], [Blocking], [Blocked By] from ' @proctab + ' order by [Process ID]'  
  
    -- distinct spid list, only spids with locks  
    set @stmt = N'select distinct L.[Process ID], P.[Blocking], P.[Blocked By] from ' + @locktab + ' L, ' + @proctab + ' P where L.[Process ID] = P.[Process ID] order by L.[Process ID]'  
end  
else if (@option = 3)  
begin  
    -- distinct object list  
    set @stmt = N'select distinct [Object] from ' + @locktab + ' order by [Object]'  
end  
else if (@option = 4)  
begin  
    -- locks per spid  
    if (@spid = 0)  
    begin  
        raiserror(N'Error @spid parameter not specified (option %d)', 1, 1, @option)  
        return(-1)  
    end  
    set @stmt = N'select [Object], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Process ID] = ' + rtrim(convert(nvarchar(10), @spid)) + ' order by [Object]'  
end  
else if (@option = 5)  
begin  
    -- locks per object  
    if (@obj is null)  
    begin  
        raiserror(N'Error @obj parameter not specified (option %d)', 1, 1, @option)  
        return(-1)  
    end  
    -- locked object is db  
    if parsename(@obj,3) is null  
    begin  
        set @stmt = N'select [Process ID], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Object] = ''' + @obj + ''' and [ObjID] = 0'  
    end  
    -- locked object is table  
    else  
    begin  
        set @stmt = N'select [Process ID], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Object] = ''' + parsename(@obj,3) + '.' + parsename(@obj,2) + '.' + parsename(@obj,1) + ''''  
    end  
end  
exec (@stmt)  
return(0)  
-- =============================================  
-- end sp_MSget_current_activity  
-- =============================================  

Open in new window

0
 

Author Comment

by:MarkRod
ID: 21825593
I doubt that I have the permission to alter a system stored procedure. Another vendor is in charge of administering the sql server
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21825601
You can just run the statements from that procedure....
0
 

Author Comment

by:MarkRod
ID: 21825672
I am using SQL Server 2000 if that helps.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21826731
question: does your table have any triggers (on delete)?
0
 

Author Comment

by:MarkRod
ID: 21831968
No triggers whatsoever.

On a hunch, I added a new record to that table and then tried to delete it. The INSERT went flawlessly, but now I have two records I cannot delete!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21832018
can you show the full table structure?
and the sample data?

my guess is that your primary key is some float field ...
0
 

Author Comment

by:MarkRod
ID: 21832466
I can guarantee that the PK is an int
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21832959
please try to restart sql server... just to make sure any locks are away.
then select the row to see if it's still there, then try to delete it.

I understand that your DELETE just starts, and then sits waiting ? ...

could you send me a backup of the database?
0
 

Author Comment

by:MarkRod
ID: 21833723
I know I won't be able to get a db backup to you. I can see about having them restart the SQL Server.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21834026
ok... in the meantime, could you post the full table creation script, as well as the insert statements of the rows you can't delete?
0
 

Author Comment

by:MarkRod
ID: 21834495
As far as the insert statement goes, I created the record through an MS Access front end, so I didn't have a query. Now, about posting the table create script, I will have to get authorization to do that.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21834525
Are you deleting through Access as well?
0
 

Author Comment

by:MarkRod
ID: 21834636
The merge duplicates process runs in Access with the final query being a delete query to remove the duplicated record. Once the client told me that they were getting a timeout error from within access, I decided to manually remove that file directly from SQL. SO normally the delete would run from within access, but for what I have been doing, I have bypassed the front-end to deal directly with the tables.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21834705
could it be that there is some instance of MS Access open, pointing to that record in "edit" mode, hence locking it?
I am aware that above it was check for locks, but ...
also, does the access interface usually work with pass-through queries, or is this a ADP database, or is the table just a linked table (i latter case, is the key definition on the linked table made correctly?)
0
 
LVL 5

Expert Comment

by:reswobslc
ID: 21950986
How about ruling out database corruption?  File corruption can do all sorts of nasty things.  DBCC CHECKDB
0
 

Author Comment

by:MarkRod
ID: 22710625
Rebooting the SQL Server did the trick. Thanks to all who replied.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question