SQL Server 2000 - Timeout and memory problem

[mateos2]

Hello

I have a machine with SQL Server 2000 SP3a, Windows 2000 Server SP4, 512 MB RAM.

In the server I run two programs that inserts records to the DB, they do it through a Stored Procedure, here is a Stored Procedure that I use:

CREATE PROCEDURE AGREGA_MOV_PROCESOS @cod_n_pais smallint,
@cod_n_local smallint,
@cod_n_proceso int, @tms_c_mov_proc varchar(4), @sev_c_mov_proc varchar(10),
@nod_c_mov_proc varchar(10), @msg_c_mov_proc varchar(200), @mop_c_mov_proc varchar(200) AS
SET NOCOUNT ON

BEGIN TRAN

DECLARE @IDE_C_MARCA INT
EXEC GETMARCA @cod_n_local, @cod_n_pais, @IDE_C_MARCA OUTPUT

INSERT INTO MOV_PROCESOS (COD_N_PAIS, IDE_C_MARCA,
COD_N_LOCAL,
FEC_D_MOV_PROC, COD_N_PROCESO, TMS_C_MOV_PROC, SEV_C_MOV_PROC, NOD_C_MOV_PROC,
MSG_C_MOV_PROC, MOP_C_MOV_PROC)
VALUES (@cod_n_pais, CAST(@IDE_C_MARCA AS VARCHAR),
@cod_n_local, getdate(), @cod_n_proceso, NULL, @sev_c_mov_proc, @nod_c_mov_proc,
@msg_c_mov_proc, @mop_c_mov_proc)

COMMIT TRAN
GO

The last thing I added to this procedure was BEGIN TRAN and COMMIT TRAN, this procedure along two more are executed 200 or more times per second at peak times (the SPs are almost equal, only the affected tables changes)

The problem is that SQL Server begins to grow its memory use constantly in time, until it uses about 350 MB of memory. Then the machine gets so slow that the next SP calls fail with Timeout Error

The program is developed in VB 6.0, to execute the SPs I use an ADO Command, this ADO Command is created at program startup and then I just pass the parameters and execute it, the DB connection is closed when I don't need it, and I open it when is neccesary

'The parameters are already set

m_cnnConn.Close 'When it's done

m_cnnConn.Open 'When I need it

What do I need to do?

We are evaluating a memory upgrade, but it will only delay the problem

Somekind of DB maintenance?

Thanks

[MAK]

is there any blocking at the time?

How big is the database? is autogrow enabled? if it is VLDB disable autogrowth.

[mateos2]

There isn't any blocking,

The database is 4GB + and growing

Thanks