Solved

Unable to delete record

Posted on 2008-06-19
39
473 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
  • 17
  • 12
  • 5
  • +3
39 Comments
 
LVL 60

Expert Comment

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

Author Comment

by:MarkRod
Comment Utility
I let it run for approx 5 mins
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
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
Comment Utility
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
Comment Utility
HOw many records are in the table?  Is the table HEAVILY indexed?
0
 
LVL 31

Expert Comment

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

Expert Comment

by:chapmandew
Comment Utility
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
Comment Utility
Not that I know of, how could I find out?
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No active open transactions
0
 
LVL 60

Expert Comment

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

Author Comment

by:MarkRod
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
There are only 3 locks on the table.

There are no results when I run the SELECT blocked, * ... code
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Run this procedure to see if there are any blocking processes:

sp_MSget_current_activity 64,1
0
 

Author Comment

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

Expert Comment

by:chapmandew
Comment Utility
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
Comment Utility
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
Comment Utility
You can just run the statements from that procedure....
0
 

Author Comment

by:MarkRod
Comment Utility
I am using SQL Server 2000 if that helps.
0
 
LVL 142

Expert Comment

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

Author Comment

by:MarkRod
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
I can guarantee that the PK is an int
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
Are you deleting through Access as well?
0
 

Author Comment

by:MarkRod
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
How about ruling out database corruption?  File corruption can do all sorts of nasty things.  DBCC CHECKDB
0
 

Author Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now