SQL Server doesn't responses!

[mateos2]

Hello

I have a program that inserts records to a SQL Server 2000 Database, it inserts the records through a Stored Procedure, the program calls the Stored Procedure a lot of times.

The problem is that the memory that SQL Server uses begins to grow constantly while the program is running until it uses all the system memory !!!

Then SQL Server doesn't response and the next insert calls fail.

The Stored Procedure is created only one time at program startup, and when I need to call it I pass only the parameters and execute it, I do it with an ADO Command

The program is developed in VB 6.0 with ADO 2.5+

How I can fix this?

or Do I have to change something in the way I do the process?

Thanks

 

[bastienk]

What does the sp do?

What are the machine configs (os, ram etc)?

Is the sql tuned?

Is the db tuned?


Bastien

Cat, the other other white meat

 

[SQLSister]

SQL is supposed to grow until it uses all the memory, that's why you should never run other programs on the same server.

If you are calling the same thing repeatedly, you may have run out of connections not memory.

If you are inserting multiple records, it is far better to do that in one stored procedure called once. Don't use cursors for inserting or updating records. Use set-based SQL statements instead.

 

[JayKusch]

I do not understand why you are recreating the SP each time the application runs ... The Stored Procedure is created only one time at program startup??

Why not create it as a permenant stored procedure and call it from you app when it is needed. This in itself will reduce some of the overhead in that the SP will be cached after the first execution.

Thanks

J. Kusch

 

[mateos2]

Sorry

I write bad the question.

I create the "Command" to hold the Stored Procedure I do that at program Startup

Here is the code for the Command preparation

Private Function Prepare_Procesos_Command() As Boolean

Set m_comProcesos = New ADODB.Command

With m_comProcesos

Set .ActiveConnection = m_cnnConexionVigiaII

.CommandTimeout = 10
.CommandType = adCmdStoredProc

.CommandText = "AGREGA_MOV_PROCESOS"
.Parameters.Append .CreateParameter("cod_n_pais", adSmallInt)
.Parameters.Append .CreateParameter("cod_n_local", adSmallInt)
.Parameters.Append .CreateParameter("cod_n_proceso", adInteger)
.Parameters.Append .CreateParameter("tms_c_mov_proc", adVarChar, , 4)
.Parameters.Append .CreateParameter("sev_c_mov_proc", adVarChar, , 10)
.Parameters.Append .CreateParameter("nod_c_mov_proc", adVarChar, , 10)
.Parameters.Append .CreateParameter("msg_c_mov_proc", adVarChar, , 200)
.Parameters.Append .CreateParameter("mop_c_mov_proc", adVarChar, , 200)

End With

Prepare_Procesos_Command = True

End Function

I only call this function at program Startup, then I use this command to insert the records, also I create the connection at startup, so I don't connect to the database more than once

The machine is a Pentium VI 1GHz, 256 MB, Windows 2000 Server

I don't know if the database is tuned or the db, I am not an expert in administrative SQL Server

 

[bastienk]

get more ram! you are running at the bare minimum (1 gb would be a goo starting point)


Bastien

Cat, the other other white meat

 

[jmaddone]

Make sure you close the active connection, while you are not using it. Also make sure you are not recreating the connection anyplace.

 

[mateos2]

Thanks

I thought that mantaining the Connection alive was more efficient than reconnect to DB, this will help me?, if yes then, what do I do with the ADO Command?, I will need to recreate it too?

This is because I create the ADO Command at program Startup, then I just pass the parameters and execute it

Thanks

 

[jmaddone]

As long as you do not "Set m_comProcesos = Nothing", it should keep the ADO Command variable alive. Closing the connection frees up memory while you are waiting for user responses.

 

[mateos2]

I will follow all your advices

Thanks for all