Re: SQL Server error when attempting to delete a record

From: DHatheway (dlhatheway_at_mmm.com.nospam)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 09:55:10 -0500

What version of SQL Server have you got? What's the definition for the
table? What are the indices and constraints on the table?

These are the questions I'd be asking myself if I had your problem:

1. Is there anything interesting corresponding to this in the error log
on the server? Any message at all at the corresponding time? How about the
event log?
2. Do you see this if you remove the (rowlock) hint?
3. Do you only see this for TaskTimesheetID = 7775 or do you see this for
a range of TaskTimesheetIDs or for all of them?
4. If TaskTimesheetID is not the Primary Key, can you get the PKs for the
record(s) to be affected and execute this command based on the Primary Key
values?
5. What happens if you try to update a different field on that same
record? Is the field you're trying to update nullable? What happens if you
try to update a different field of the same row, especially a fixed lenght
fields?
6. Is there a Primary Key? Is the table clustered on that?

I'm guessing you have some sort of internal table or index corruption
problem. The error message suggests "can't deal with this row because
something unexpected is happening." It kinda, sorta looks like it has a
problem allocating space for the updated row in the page where it expects to
put the row after the update. If you can affort the time to do so, I'd
suggest you rebuild the table, maybe even with a different fillfactor(s) on
your index(es), especially any clustered index. You could try a dbcc
dbreindex or you could do something like this:

- BACKUP THE DATABASE.
- Find a way to lock out all updates to the table.
- bcp all the data out to a file (use /n mode)
- Script the table's definition to a file (including constraints, indices,
triggers and all)
- Drop the table.
- Recreate the table from the script (for best performance at this point,
don't recreate indices, constraints, triggers, etc).
- bcp all the data back in.
- Alter the table to include the constraints, indices, etc.

Exactly what I would do would depend on your uptime requirements, the size
of the database, factors like that.

"cwendover" <cwendover@hotmail.com> wrote in message
news:ezv%23PuSsEHA.3336@tk2msftngp13.phx.gbl...
> First, I am not sure if this is the correct location to post this and I
> probably do not have all the information you will need, but I hope you can
> get me pointed in the correct direction and ask questions as you need.
>
> I am not the SQL Server administrator, but do programming and database
> management in other products so I understand the ideas and terminology.
>
> We are using a Time*** program by Replicon which uses SQL Server as the
> database. Recently they have been doing updates to the servers and about
a
> week ago we started having a reproducible error involving the server.
When
> we attempt to delete a record from the database through the Replicon
> application we get the following error:
>
> [Microsoft][ODBC SQL Server Driver][SQL Server] - Statement follows:
UPDATE
> TSHistory WITH (ROWLOCK) SET IsValid=0 WHERE TaskTimesheetId = 7775 AND
> IsLastSubmit = 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Location:
> q:\SPHINX\NTDBMS\storeng\drs\include\record.inl:1447 Expression: m_SizeRec
>
> 0 && m_SizeRec <= MAXDATAROW SPID: 54 Process ID: 776
>
> We sent the database files to Replicon and they can not "replicate" the
> problem at their end.
>
> What settings/functions could we start looking at to track this down.
> Again, please ask questions or point me to other areas as appropriate.
>
> Thanks in advance,
> Carolyn
>
>