locked
How long hold the DB connection? (SQL server memory usages problem) RRS feed

  • Question

  • Hi,

    I am developing an backup application where I update some stats to every 5 seconds. To update the stats I open connection at start of application and close it at end of application. Application is running as service so it will never end.  After few days SQL server process uses the huge memory (1 GB)

    Problem
    1] what is good logic  open connection at start of application and close at end? (Application is service will run fo at least 6 monts contiously)
    2] Close connection when DB operation is done (I am updating the DB every 5 seconds)

    any other solution is welcome



    • Moved by Chris Robinson- MSFT Wednesday, June 24, 2009 6:04 PM Sql Server Express is running slow (From:ADO.NET DataSet)
    Tuesday, June 23, 2009 6:34 PM

Answers

  • Okay I will move this to a Sql Server forum so that th can ask the right questions.

    Thanks
    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 24, 2009 5:56 PM

All replies

  • Can you please answer these questions do I can understand more about the issue:
       1) Are you using DataSet?
       2) In the service you created is the DataSet/DataTable held in memory?
       3) Do you construct a DataSet new each time when the service starts again?
       4) Do you get outOfMemoryExceptions?
       5) Do you fill the Data from Sql Server into the DataSet, how many rows?
       6) What operations are you using in DataSet, are you just inserting rows?

    Thanks
    Chris Robinson
    Program Manager - DataSet

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 23, 2009 7:32 PM
  • Thanks for reply
    Here are my answers and code snipshot

     1) Are you using DataSet?
    => Yes

       2) In the service you created is the DataSet/DataTable held in memory?
    =>Yes

       3) Do you construct a DataSet new each time when the service starts again?
    => yes

       4) Do you get outOfMemoryExceptions?
    => Not yet
       5) Do you fill the Data from Sql Server into the DataSet, how many rows?
    => we use SQL server Express edition

       6) What operations are you using in DataSet, are you just inserting rows?
    => mostly change the values of some fields

    Here is the function called for each job (generally a system have 20-25 jobs) after every 5 seconds


    public int SetJobStatisticsToDB(JobStatistics JobStats)
    
    {
    
    int iRetVal = Error.ERROR_FAIL;
    
    ArrayList ErrList = new ArrayList();
    
    try
    
    {
    
    Monitor.Enter(this); 
    
    
    // open the db connestion
    
    int iRet = this.OpenRTMDBConnection(ref ErrList);
    
    if (Error.ERROR_FAIL == iRet ||
    
    true == Error.ErrInList(ErrList))
    
    {
    
    Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_OPEN_DB);
    
    return iRetVal;
    
    }
    
    iRet = JobStats.ObjectToDB(this, true);
    
    if (Error.ERROR_SUCCESS != iRet)
    
    {
    
    Logger.Log(ELogLevel.ERROR, RTMError.OBJECT_TO_DB_FAILED);
    
    return iRetVal;
    
    }
    
    this.Update(Constants.JOBSTATISTICSDATATABLE);
    
    iRetVal = Error.ERROR_SUCCESS;
    
    }
    
    catch (Exception Exp)
    
    {
    
    Logger.Log(Exp);
    
    }
    
    finally
    
    {
    
    
    Monitor.Exit(this);
    
    }
    
    return iRetVal; 
    
    }
    
    
    
    public int OpenRTMDBConnection(ref ArrayList ErrList)
    
    {
    
    Logger.Log(ELogLevel.DEBUG, System.Reflection.MethodBase.GetCurrentMethod().Name + Constants.ENTRY);
    
    
    int iRetVal = Error.ERROR_FAIL; 
    
    try
    
    {
    
    Monitor.Enter(this);
    
    // check the db connestion state
    
    if (null != this.DBCon && 
    
    this.DBCon.State == ConnectionState.Open)
    
    {
    
    // DBConnection is already open. just get the tables
    
    iRetVal = this.GetTables();
    
    if (Error.ERROR_SUCCESS != iRetVal)
    
    {
    
    Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_GET_TABLES);
    
    }
    
    return iRetVal;
    
    }
    
    Logger.Log(ELogLevel.ERROR, "Opening DB new connection");
    
    // connection is not open. Open it
    
    // OpenConnection will create new DBConn instance
    
    int iRet = this.OpenConnection(ref ErrList);
    
    if (Error.ERROR_FAIL == iRet ||
    
    true == Error.ErrInList(ErrList))
    
    {
    
    Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_OPEN_DB);
    
    return iRetVal;
    
    }
    
     
    
    // DBConnection is already open. just get the tables
    
    iRetVal = this.GetTables();
    
    if (Error.ERROR_SUCCESS != iRetVal)
    
    {
    
    Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_GET_TABLES);
    
    }
    
    iRetVal = Error.ERROR_SUCCESS;
    
    }
    
    catch (Exception Exp)
    
    {
    
    Logger.Log(Exp);
    
    }
    
    finally
    
    { 
    
    Monitor.Exit(this);
    
    }
    
    
    
    /// <summary>
    
    /// 
    
    /// </summary>
    
    /// <param name="Success"></param>
    
    /// <param name="ErrList"></param>
    
    /// <returns></returns>
    
    public int OpenConnection( ref ArrayList ErrList)
    
    {
    
    int status = Error.ERROR_SUCCESS; 
    
    try
    
    {
    
    Logger.Log(ELogLevel.DEBUG, m_ConnectionString);
    
    if (DBCon == null)
    
    {
    
    mDBCon = new System.Data.SqlClient.SqlConnection();
    
    DBCon.ConnectionString = m_ConnectionString;
    
    DBCon.Open(); 
    
    }
    
    else
    
    {
    
    if (DBCon.State != ConnectionState.Open)
    
    {
    
    DBCon.ConnectionString = m_ConnectionString;
    
    DBCon.Open();
    
    }
    
    }
    
    
    }
    
    catch (Exception ex)
    
    {
    
    Logger.Log(ex);
    
    status = Error.ERROR_FAIL; 
    
    }
    
    return status;
    
    }
    
    
    
    /// <summary>
    
    /// 
    
    /// </summary>
    
    /// <param name="JobStats"></param>
    
    /// <returns></returns>
    
    public int SetJobStatisticsToDB(JobStatistics JobStats)
    
    {
    
    int iRetVal = Error.ERROR_FAIL;
    
    ArrayList ErrList = new ArrayList();
    
    try
    
    {
    
    Monitor.Enter(this); 
    
    
    // open the db connestion
    
    int iRet = this.OpenRTMDBConnection(ref ErrList);
    
    if (Error.ERROR_FAIL == iRet ||
    
    true == Error.ErrInList(ErrList))
    
    {
    
    Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_OPEN_DB);
    
    return iRetVal;
    
    }
    
    iRet = JobStats.ObjectToDB(this, true);
    
    if (Error.ERROR_SUCCESS != iRet)
    
    {
    
    Logger.Log(ELogLevel.ERROR, RTMError.OBJECT_TO_DB_FAILED);
    
    return iRetVal;
    
    }
    
    this.Update(Constants.JOBSTATISTICSDATATABLE);
    
    iRetVal = Error.ERROR_SUCCESS;
    
    }
    
    catch (Exception Exp)
    
    {
    
    Logger.Log(Exp);
    
    }
    
    finally
    
    {
    
    
    Monitor.Exit(this);
    
    }
    
    return iRetVal; 
    
    }
    
    
    
    // Export this Object to DB Row 
    
    public int ObjectToDB(DBManager DBMgr, bool bForce)
    
    {
    
    int status = Error.ERROR_SUCCESS;
    
    try
    
    {
    
    //Logger.Log(ELogLevel.INFO, System.Reflection.MethodBase.GetCurrentMethod().ToString());
    
    String Query = "JobID = " + "'" + this.JobID + "'";
    
    DataRow dRow = null;
    
    DBMgr.GetRow(DBMgr.ds.Tables[Constants.JOBSTATISTICSDATATABLE], Query, out dRow);
    
    if (dRow == null && bForce == true)
    
    {
    
    dRow = DBMgr.ds.Tables[Constants.JOBSTATISTICSDATATABLE].NewRow();
    
    FillRow(ref dRow);
    
    DBMgr.ds.Tables[Constants.JOBSTATISTICSDATATABLE].Rows.Add(dRow);
    
    
    }
    
    else if (dRow != null)
    
    {
    
    FillRow(ref dRow);
    
    
    }
    
     
    
    }
    
    catch (Exception ex)
    
    {
    
    Logger.Log(ELogLevel.ERROR, ex);
    
    status = Error.ERROR_FAIL;
    
    }
    
    
    return status;
    
    }
    
    Tuesday, June 23, 2009 8:36 PM
  • Hi,

    the function SetJobStatisticsToDB is called every 5seconds and it then calls open connection functions etc
    Tuesday, June 23, 2009 8:37 PM
  • Wow that is alot of code to digest, I'm more interested in the specific DataSet code. So where is it that you create a new DataSet or DataTable? I don't see that.

    One possible issue is in your use of NewRow(). THis allocates memory into the Record structure of DataSet. If you create a newRow and don't add it to the DataTable it will continue to consume memory. I would change this to .Add(object [] params) instead, so it always goes into the DataTable and doesn't preallocate memory.

    I would also if you could avoid a long running dataset and make it stateless instead. Pull the information when you need it rather than caching it in the DataSet. Do not have class level variables of DataSet/DataTable, make sure you dispose of them properly. You can also call GC.Collect as well to force a garbage collection.

    Its difficult to diagnosis the exact issue here but there are areas of DataSet that leak memory, this is likely what is happening.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 23, 2009 10:59 PM
  • This is how new dataset is created and filled

    if (ds == null)
                    {
                        Logger.Log(ELogLevel.ERROR, "Creating new data set");
                        mDS = new DataSet();
                    }
                   
                    String Query = "";
    
                    Query = "SELECT * From " + TableName;
                    System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(Query, DBCon);
                    da.SelectCommand = new System.Data.SqlClient.SqlCommand(Query, DBCon);
                    da.Fill(ds, TableName);
    this new dataset is created once but filled every 5 seconds

    Wednesday, June 24, 2009 12:35 PM
  • I added code to close the connection with SQL server after every 1 minute(Assuming that closing the connection will release all resources).  But still the SQL server memory usage is increasing. At one time SQL Server was  using  1.01 GB memory(virtual memory). When I stopped my service it dropped to 880 MB. but still it is very high. How can we force SQL server to release memory ?

    As there is a backup of data going on (3 TB data) the backup application is getting 10054 or 1450(Insufficient system resources exist to complete the requested service.) error. How to handle the case?

    Problems
    1] Why SQL server is incresing memory usages though connection is closed?
    2] The data updated in every 5 sec is 4-5KB. Is this a big amoutn of data to update in DB

    Please help ! ! ! !
    Wednesday, June 24, 2009 3:57 PM
  • I'm confused here, which process is hogging the memory. Is it the Sql Server process? Or is it the service you are running consuming the memory. If its Sql Server, I'm not sure this forum can help. If its your service itself then I might be able to offer assistance.

    Thanks
    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 24, 2009 5:15 PM
  • hi,

    SQL server is holding the memory not my service.
    Wednesday, June 24, 2009 5:40 PM
  • Okay I will move this to a Sql Server forum so that th can ask the right questions.

    Thanks
    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 24, 2009 5:56 PM