Certain records in a table cannot be edited, but others are OK

Hi,
I am using SQL Server 2000 as part of SBS2003.
I have a table containing approx. 25,000 records. It's a fairly simple
table with just 30 fields. There are no triggers on the table.
The table has existed in SQL for at least 12 months and has worked
fine. However, today I have a problem. When I try to edit certain
records in the table, the whole SQL system hangs until I eventually
get an ODBC time out error. Then the system works again, but I cannot
edit these records.
Strangely, the majority of the other records are fine and I can edit
them. This includes records which come before and after the "bad"
records. I have compared two records which fail, with two which are
ok, but I can't see any noticable difference, at least nothing which
might cause this problem.
Anybody got any ideas what might be happening? Presumably some kind of
lock on the records. If so, how do I identify which records are
effected and how do I clear the locks?
Thanks for any help,
ColinBobby
Don't edit the data throu EM .
Do you succed to run UPDATE .... from QA?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191928927.027341.243860@.v3g2000hsg.googlegroups.com...
> Hi,
> I am using SQL Server 2000 as part of SBS2003.
> I have a table containing approx. 25,000 records. It's a fairly simple
> table with just 30 fields. There are no triggers on the table.
> The table has existed in SQL for at least 12 months and has worked
> fine. However, today I have a problem. When I try to edit certain
> records in the table, the whole SQL system hangs until I eventually
> get an ODBC time out error. Then the system works again, but I cannot
> edit these records.
> Strangely, the majority of the other records are fine and I can edit
> them. This includes records which come before and after the "bad"
> records. I have compared two records which fail, with two which are
> ok, but I can't see any noticable difference, at least nothing which
> might cause this problem.
> Anybody got any ideas what might be happening? Presumably some kind of
> lock on the records. If so, how do I identify which records are
> effected and how do I clear the locks?
> Thanks for any help,
> Colin
>|||On 9 Oct, 13:08, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Bobby
> Don't edit the data throu EM .
> Do you succed to run UPDATE .... from QA?
>
No, it won't edit from anywhere, including my application.
A bit more information which I have just discovered. On Monday morning
I had to restore my database to dinner time Friday due to a problem we
had. It appears that all of the records which are "bad" are in the
range which were originally entered on Friday afternoon.
Is this possible. Does this indicate an indexing error. If so, how do
I refresh my indexes?
Thanks
Colin|||Bobby
Howe many rows are affrectyed from your UPDATE? rUN DBCC CHECKDB to see what
is going on?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191933223.214949.93940@.g4g2000hsf.googlegroups.com...
> On 9 Oct, 13:08, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Bobby
>> Don't edit the data throu EM .
>> Do you succed to run UPDATE .... from QA?
> No, it won't edit from anywhere, including my application.
> A bit more information which I have just discovered. On Monday morning
> I had to restore my database to dinner time Friday due to a problem we
> had. It appears that all of the records which are "bad" are in the
> range which were originally entered on Friday afternoon.
> Is this possible. Does this indicate an indexing error. If so, how do
> I refresh my indexes?
> Thanks
> Colin
>|||On 9 Oct, 13:49, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Bobby
> Howe many rows are affrectyed from your UPDATE? rUN DBCC CHECKDB to see what
> is going on?
> "Bobby" <bob...@.blueyonder.co.uk> wrote in message
> news:1191933223.214949.93940@.g4g2000hsf.googlegroups.com...
>
> > On 9 Oct, 13:08, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Bobby
> >> Don't edit the data throu EM .
> >> Do you succed to run UPDATE .... from QA?
> > No, it won't edit from anywhere, including my application.
> > A bit more information which I have just discovered. On Monday morning
> > I had to restore my database to dinner time Friday due to a problem we
> > had. It appears that all of the records which are "bad" are in the
> > range which were originally entered on Friday afternoon.
> > Is this possible. Does this indicate an indexing error. If so, how do
> > I refresh my indexes?
> > Thanks
> > Colin- Hide quoted text -
> - Show quoted text -
15 rows are effected. DBCC CHECKDB shows no errors.|||Bobby
Ok , so when you issue an UDDATE against a "bad" row on TEST server (you
said that you restotred the db) , what error do you get?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191936289.762941.280800@.22g2000hsm.googlegroups.com...
> On 9 Oct, 13:49, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Bobby
>> Howe many rows are affrectyed from your UPDATE? rUN DBCC CHECKDB to see
>> what
>> is going on?
>> "Bobby" <bob...@.blueyonder.co.uk> wrote in message
>> news:1191933223.214949.93940@.g4g2000hsf.googlegroups.com...
>>
>> > On 9 Oct, 13:08, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> >> Bobby
>> >> Don't edit the data throu EM .
>> >> Do you succed to run UPDATE .... from QA?
>> > No, it won't edit from anywhere, including my application.
>> > A bit more information which I have just discovered. On Monday morning
>> > I had to restore my database to dinner time Friday due to a problem we
>> > had. It appears that all of the records which are "bad" are in the
>> > range which were originally entered on Friday afternoon.
>> > Is this possible. Does this indicate an indexing error. If so, how do
>> > I refresh my indexes?
>> > Thanks
>> > Colin- Hide quoted text -
>> - Show quoted text -
> 15 rows are effected. DBCC CHECKDB shows no errors.
>|||On 9 Oct, 14:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Bobby
> Ok , so when you issue an UDDATE against a "bad" row on TEST server (you
> said that you restotred the db) , what error do you get?
>
The error doesn't occur on the test database, only on "live". If I run
the update on live I get,
[Microsoft][ODBC SQL Server Driver]Timeout expired
If I turn off the timeout it hangs indefinately. While it is hung,
nobody can do anything with the table. This only happens for the 15
"bad" records. All of the rest are fine.
Thanks for your help|||What happens if you create a duplicate table (different name) and insert all
rows into that table?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191938900.611210.83780@.r29g2000hsg.googlegroups.com...
> On 9 Oct, 14:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Bobby
>> Ok , so when you issue an UDDATE against a "bad" row on TEST server (you
>> said that you restotred the db) , what error do you get?
> The error doesn't occur on the test database, only on "live". If I run
> the update on live I get,
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> If I turn off the timeout it hangs indefinately. While it is hung,
> nobody can do anything with the table. This only happens for the 15
> "bad" records. All of the rest are fine.
> Thanks for your help
>
>|||On 9 Oct, 15:25, "Jay" <s...@.nospam.org> wrote:
> What happens if you create a duplicate table (different name) and insert all
> rows into that table?
> "Bobby" <bob...@.blueyonder.co.uk> wrote in message
> news:1191938900.611210.83780@.r29g2000hsg.googlegroups.com...
>
> > On 9 Oct, 14:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Bobby
> >> Ok , so when you issue an UDDATE against a "bad" row on TEST server (you
> >> said that you restotred the db) , what error do you get?
> > The error doesn't occur on the test database, only on "live". If I run
> > the update on live I get,
> > [Microsoft][ODBC SQL Server Driver]Timeout expired
> > If I turn off the timeout it hangs indefinately. While it is hung,
> > nobody can do anything with the table. This only happens for the 15
> > "bad" records. All of the rest are fine.
> > Thanks for your help- Hide quoted text -
> - Show quoted text -|||On 9 Oct, 15:25, "Jay" <s...@.nospam.org> wrote:
> What happens if you create a duplicate table (different name) and insert all
> rows into that table?
>
That seems to have fixed the problem. Only trouble is I can't do it
for real right now due to users on the system. I'll have to wait until
tonight, and login remotely.
Thanks|||Glad it helped.
This goes back to a very old DBA joke:
What part of "dump and reload" don't you understand?
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1191941917.222229.228010@.d55g2000hsg.googlegroups.com...
> On 9 Oct, 15:25, "Jay" <s...@.nospam.org> wrote:
>> What happens if you create a duplicate table (different name) and insert
>> all
>> rows into that table?
> That seems to have fixed the problem. Only trouble is I can't do it
> for real right now due to users on the system. I'll have to wait until
> tonight, and login remotely.
> Thanks
>|||On 9 Oct, 15:58, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 9 Oct, 15:25, "Jay" <s...@.nospam.org> wrote:
> > What happens if you create a duplicate table (different name) and insert all
> > rows into that table?
> That seems to have fixed the problem. Only trouble is I can't do it
> for real right now due to users on the system. I'll have to wait until
> tonight, and login remotely.
> Thanks
Unfortunately the problem is not resolved. Last night (with nobody
else on the system) I exported my table, deleted the original, created
a new table with the same name and fields as the original and used SQL
Analyser to copy the data from the export to the new table.
Everything was fine, I could modify the "bad" records no problem. This
morning I have just arrived in. Same as yesterday, there are 15
records which I cannot modify. Every other record in the table is ok.
If I look at the data, I can see the changes that I made last night,
but I can no longer modify them.
I'm not sure what to do now, apart from try to forget that the bad
records exist. Anybody got any better ideas?
Thanks for your help,
Confused
Colin