Re: Timeout expired when add new record

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Uri Dimant (urid_at_iscar.co.il)
Date: 04/19/04 

Date: Mon, 19 Apr 2004 07:44:55 +0200

Lip
I assume you have a primary key defined on these tables.
Look , when a new row is inserted it will be added to the end of the table.
But I think you have external fragmentation in your case. When a new row is
inserted it may cause a new page to be created and number of data to be
moved to the new page in order to make room for the new one on the original
page. This creation will cause the index pages to be out of logical order.

Please run DBCC SHOWCONTIG to make sure you don't have fragmentation on
these tables.

"Lip Fu" <lip_fu@yahoo.com> wrote in message
news:0c4901c425c1$996f98c0$a001280a@phx.gbl...
> I am using SQL Server 7.0 with Windows Server 2000.
> The Server have dual Intel Xeon and RAM 2 GB.
> The database only one with the size 2,826 GB.
> The database have 17 tables, 2 of them have big records :
> - Table A have 2,899 million records with 23 fields and
> the total length per record is 203 bytes.
> - Table B have 1,074 million records with 23 fields and
> the total length per record is 203 bytes.
> The record addition of table A is about 22000 records per
> day, and for table B is about 5000 records per day.
> The problem is table A and B often timeout expired when
> adding new record.
> Table A more often than table B in timeout expired.
> I am using the Insert Into Command using adodb.command
> with VB application to add the new record.
> I already changed to SQL Server store procedure, still
> have the same problem.
> I set the timeout to 5 seconds because practically the
> addition time is < 1 seconds when I saw from the log.
> And I have try to set the timeout to 30 seconds, still
> have the same problem.
> Please help me to solve the problem.
> If I changed the SQL Server from 7.0 to 2000, will it
> solve the problem ?
> Thx.