DBREINDEX problems

[petertickler]

I have a Diary table structure like this.

[Diary_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Address_ID] [int] NOT NULL ,
[Date] [datetime] NULL ,
[UserName] [varchar] (20) NULL ,
[ContactName] [varchar] (40) NULL ,
[ContactType] [varchar] (20) NULL ,
[Notes] [text] NULL ,
[CreationDate] [datetime] NULL ,
[DeletionDate] [datetime] NULL ,
[ResponseDate] [datetime] NULL ,
[ClosureDate] [datetime] NULL ,
[ReferredTo] [varchar] (30) NULL ,
[Status] [char] (1) NULL ,
[FollowupDate] [datetime] NULL

When I first went Live with it, I regularly had trouble with it suddenly refusing to be updated by my applications. They would hang until I reindexed the table.

I got round this by setting up a job to do a reindex every three hours.

USE ContactsLive
Go
DBCC DBREINDEX (Diary)
Go

This has worked fine for a year, but now that I have added one more field to the structure ([LinkedFile] [varchar] (300) NULL), suddenly I have got the same old problem again. I have amended my ReIndex job to running every hour, but still the problem recurs almost daily.

Can anyone suggest what things I might look into fix this problem?

 

[jgoodman00]

Have you checked to see if any locks are being held against the table?

You should not need to reindex every three hours...



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[petertickler]

I will do next time I have the problem.

This is the only table I ever have this problem with. My assumption was that it was something to with the fact that it is handling a Text field. I see now that in SQL Server 7.0 I can have a Varchar or Char field up to 8000 bytes long. In view of the fact that the longest piece of data in the Notes field is just under 3000 characters, I wonder if it wouldn't be a good idea to change back to either a Char or VarChar.

What do you think?

Peter

 

[jgoodman00]

In all honesty yes. I try to avoid the Text/NText etc data types as much as possible.


James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures