Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

Unable to delete record

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
MarkRod
Asked:
MarkRod
  • 17
  • 12
  • 5
  • +3
1 Solution
 
chapmandewCommented:
How long do you wait for it to delete before stopping?
0
 
MarkRodAuthor Commented:
I let it run for approx 5 mins
0
 
chapmandewCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
James MurrellProduct SpecialistCommented:
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
 
MarkRodAuthor Commented:
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
 
chapmandewCommented:
HOw many records are in the table?  Is the table HEAVILY indexed?
0
 
James MurrellProduct SpecialistCommented:
does record appear when you do
select FROM <TABLENAME>WHERE <ROWETC>= 13  
0
 
chapmandewCommented:
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
 
MarkRodAuthor Commented:
Not that I know of, how could I find out?
0
 
chapmandewCommented:
run sp_lock and see if there are a lot of records for a certain object..that object could be your table.
0
 
MarkRodAuthor Commented:
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
 
MarkRodAuthor Commented:
Most of the 450 locks are for ObjID: 0. How would I tell if thats my table or not? The type is DB.
0
 
chapmandewCommented:
Its not...but the db might be locked for some reason.  Run this:

dbcc opentran

see if there are any outstanding open transactions.
0
 
MarkRodAuthor Commented:
No active open transactions
0
 
chapmandewCommented:
And you're sure that there were not locks of type 'TAB' returned from sp_lock?
0
 
MarkRodAuthor Commented:
I ran it again and found locks with type TAB. How can I get the ObjID for my table to check?
0
 
chapmandewCommented:
The objectid is returned by sp_lock.  You can determine if it is your table by this:

select * from sysobjects
where name = 'tablename'
0
 
Scott PletcherSenior DBACommented:
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
 
MarkRodAuthor Commented:
There are only 3 locks on the table.

There are no results when I run the SELECT blocked, * ... code
0
 
chapmandewCommented:
Run this procedure to see if there are any blocking processes:

sp_MSget_current_activity 64,1
0
 
MarkRodAuthor Commented:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '##procinfo64'.
0
 
chapmandewCommented:
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
 
MarkRodAuthor Commented:
I doubt that I have the permission to alter a system stored procedure. Another vendor is in charge of administering the sql server
0
 
chapmandewCommented:
You can just run the statements from that procedure....
0
 
MarkRodAuthor Commented:
I am using SQL Server 2000 if that helps.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
question: does your table have any triggers (on delete)?
0
 
MarkRodAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the full table structure?
and the sample data?

my guess is that your primary key is some float field ...
0
 
MarkRodAuthor Commented:
I can guarantee that the PK is an int
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MarkRodAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MarkRodAuthor Commented:
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
 
chapmandewCommented:
Are you deleting through Access as well?
0
 
MarkRodAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
reswobslcCommented:
How about ruling out database corruption?  File corruption can do all sorts of nasty things.  DBCC CHECKDB
0
 
MarkRodAuthor Commented:
Rebooting the SQL Server did the trick. Thanks to all who replied.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 17
  • 12
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now