adding a new record takes longer and longer archive? (was "Table help")

Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
I don't know a lot about sql server so any help or suggestions would be greatThat's not really a lot of records

And 4,000 a day is not a lot of transactions

How do you "add" the rows?

And what the Table DDL look like...post that, and don't forget the indexes, keys, contraints and triggers if you have them|||Here's the info on the table that's causing the probs. We are updateing this table using an Access front end with odbc linked tables. Any help on how to maintain a table would be great.
Cheers

CREATE TABLE [dbo].[calls] (
[CallID] [int] IDENTITY (1, 1) NOT NULL ,
[ContactID] [int] NOT NULL ,
[CallDate] [datetime] NULL ,
[CallTime] [datetime] NULL ,
[Callername] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Notes] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[CallerCompany] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ActionTakenID] [int] NULL ,
[Confirmed] [bit] NULL ,
[EmployeesID] [int] NULL ,
[ShortMessage] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[CallerNumber] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Charged] [bit] NULL ,
[OperatorID] [int] NULL ,
[DDI] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[EmployeeName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OperatorName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[calls] WITH NOCHECK ADD
CONSTRAINT [PK_calls] PRIMARY KEY CLUSTERED
(
[CallID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[calls] ADD
CONSTRAINT [DF_calls_Confirmed] DEFAULT (0) FOR [Confirmed],
CONSTRAINT [DF_calls_Charged] DEFAULT (0) FOR [Charged]
GO|||My guess is that you're using a bound form to this table. And this is an MDB not an Access Data Project.

I would make it unbound, use sql to populate the unbound controls, then when they need to do an update or insert, perform them as regular sql calls using the values from the controls.

As for the table...there's not really much wrong.

I would do the following though

1. Combine the CallDate and CallTime Fields. It's redundant.
2. Whats the largest value in the ntext column. I'd use varchar if possible
3. A caller name of 255? This has got to be an Access default. Make it shorter. Better yet, make 2 columns. Last Name and First Name. Add a Third for Title, Mr/Mrs/Ms, ect
4. Bit...I never liked bit...I don't think it's ANSI and does not cross platforms well
5. varchar(4)...anything less than ...say 15 should be char...
6. Employee Name, Operator Name...same thing...you should use first and last
7. And identity as a PK? There's got to be a better one. CallerLastName, CallDate, CallerFirstName perhaps. Just plave a unique index un the id.

Now None of these things should be impacting your performance...maybe ntext (and why unicode, why not just text?). ALL of my money is on the Access ODBC nound form.|||As you guesed the sql database is an bit of a bodged upgrade from a access database, so needs cleaning up, am waiting for my boss to send me on some sort of course though.
As you also guesed we are using access bound forms, think I might have to bite the bullet a have been considering upgrading to a vb.net front end for a while, but don't think my programing skills are really up to it, is much more work than going to an access project, but may as well go the full hog and dump access.
Have just dome some testing and it updates like lightning from vb.net.

Any more general advice would be great (if people have nothing to says will mark of a resolved in a few days)

Thanks again

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to an
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
Jerry
Hi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:

> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to a
n
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
--
JerryHi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:

> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to
an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to an
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
--
JerryHi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:
> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry