Inserting row fails - timeout

[Stuartp]

Hi

I have an SQL 2000 database being used by a classic ASP web site. I have a simple form on a page collecting data which has happily been entering data into the database for years.

All of a sudden I am getting timeout messages on the web site even though nothing has been altered in the code.

Tried recreating the table and got an error about max row length exceeding 8000 or so bytes, so I trimmed some varchar fields so I didn't get the error. This seemed to work for a while but now the timeouts are appearing again.

I've backed up the data in the table, deleted and recreated a blank table and it works. However if I put the old data back in it does not.

Is there any logging I can check for more info on why it is failing? Or is there a way to make SQL "clear" whatever lock it has put on this table to allow it to be used?

Thanks in advance

Stuart

 

[pjw001]

I have seen situations where deleting a single record from a not particularly large table can take minutes. This was caused by foreign key constraints in a very large table that meant the delete required a scan of the large table. I can't see how that type of situation would apply here, but I suppose it might.
With the data back in the table, try pasting the insert statement into Query Analyser and look at the Query Execution Plan. If there is a definite problem this should show it. Also, just try executing the insert in Query Analyser and see how long it takes.

 

[Stuartp]

Thanks for your suggestion. To be honest Query Analyser hadn't occurred to me!

I didn't say in the original post but I wasn't using an insert statement. I was using rs.addnew and rs.update in the code. I rewrite this to send an insert statement instead and that appears to be working.

Cheers

Stuart