SQL Server doesn't responses! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server doesn't responses!

Author

Topic

mateos2
Starting Member

4 Posts

Posted - 2004-04-19 : 10:07:43

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-19 : 10:21:42

You'll have to post the SP...and also possibly (part of) the VB code to move this along (fast)....(and also possibly the DDL of the tables affected by the SQL SP)

Otherwise, with such a brief description, all you'll be able to get from us is bland/generic statements...like use SQL Profiler...(which isn't bad advice in itself)

You'll also have to quantify how many records get inserted before failure....and how many 'concurrent' users you have, transactions per second, etc....

Also give the exact error message you get....

mateos2
Starting Member

4 Posts

Posted - 2004-04-19 : 10:56:26

Thanks.

Here is all you asked

The stored procedure code:

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
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)
GO

and the GETMARCA procedure

CREATE PROCEDURE GETMARCA @cod_local as int, @cod_pais as int,
@marca as int output AS
SELECT @marca = IDE_C_MARCA FROM NUCLEO_BASE..LOCALES
WHERE COD_N_LOCAL = @cod_local AND COD_N_PAIS = @cod_pais
GO

the VB code

This function justs prepares the Stored Procedure, it uses some module level variables to hold the Connection and the Command

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

This code is to create the connection

Public Function GetConexion(ByVal Programa As String) As ADODB.Connection

Dim objConexion As ConexionBD
Dim strConn As String
Dim cnnConnTemp As ADODB.Connection

On Error GoTo GetConexion_Error

Set objConexion = New ConexionBD

strConn = objConexion.GetConnectionString(Programa)

If strConn = "" Then
Set GetConexion = Nothing
Exit Function
End If

Set cnnConnTemp = New ADODB.Connection

With cnnConnTemp

.CursorLocation = adUseClient
.ConnectionTimeout = 10
.CommandTimeout = 10

.ConnectionString = strConn

.Open

End With

Set GetConexion = cnnConnTemp

Exit Function

GetConexion_Error:

Set GetConexion = Nothing

End Function

The program runs all the time, the server doesn't shutdown because it need be working 24x7, there are 8 concurrent users for select and only 2 concurrent programs for inserts

In peak times there are 200 inserts per second (or more), and the error starts at the third day after restart, and the error is Timeout Error, the SQL Server at the third day uses about 350 MB of memory or more

Thanks in advance

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-20 : 02:20:30

Are you using transactions anywhere in your code? I've seen this behaviour with having multiple BEGIN TRAN statements and no COMMIT or ROLLBACK statements. Also it might be worthwile auditing your application to see if it there are any instances where it can exit without closing the connection or commiting a transaction. With the sort of scale you describe, even minor lapses can add up.

OS

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-20 : 07:49:30

the SQL Server at the third day uses about 350 MB of memory or more

From this statement, I would assume this is "not" a dedicated SQL Server and the server is sharing space with the application. This is not a recommended best practice, but if you don't have the budget it's understandable.

You need to go into SQL Server properties (right click on the server in Enterprise Manager and choose Properties). You then need to set your max memory size. Make sure you leave plenty of room for your application and OS. If you don't do this, the SQL Server will consume as much memory as it can find. This is great on a dedicated box. It's a disaster on a mixed box.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mateos2
Starting Member

4 Posts

Posted - 2004-04-20 : 10:33:41

Hello

In the server there is only two apps that inserts records to the database, that's the only aditional work that the server has.

I don't use BEGIN TRAN inside my code

If I put 1GB RAM and configure that SQL Server will use 512MB.

The OS will work well?, I have Windows 2000 Server

This will prevent that SQL Server eats all the memory?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources