Re: Timeout expired when add new record

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 12:23:18 +0530

Hi,

Login to query analyzer and execute the below command ,

Use dbname
go
DBCC SHOWCONTIG(Table_Name)

Replace the dbname and Table name with your actual. If the SCAN dencity is
less than 80% or so then probably do a Reindexing or INDEX Defrag.

DBCC DBREINDEX(Table_Name)

or use

DBCC INDEXDEFRAG(dbname,table_name,index_name)

After executing the above , run DBCC SHOWCONTIG(Table_Name) to check the
fragmentation is removed.

Thanks
Hari
MCDBA

<lip_fu@yahoo.com> wrote in message
news:140d01c42686$ccdf5cc0$a601280a@phx.gbl...
> Thank you Uri Dimant for your reply.
> Yesterday I have timeout expired problem with other table
> which only have 7000 records. I can not add new record
> although using Query Analyzer. Several days ago I also
> have the same problem with the other table which only have
> 100k records. To solve the problem, I copy the table to
> another name, drop the table and rename the copied table.
> My tables didn't use primary key, because I have several
> indexes.
> How to do the DBCC SHOWCONTIG ? I can not use that command
> with Query Analyzer.
> Can SQL Server 2000 solve the problem if I changed it ?
> Thx.
>
> >-----Original Message-----
> >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.
> >.
> >

Relevant Pages
Loading