none
Handle leak with SqlCommand RRS feed

  • Question

  • I have a C# application which keeps inserting a row of data into a table in an infinite loop with a delay of 1 second per iteration. When I execute the application, the insertion in the database table works fine, but after say 5 minutes of operation, the application handle count keeps increasing(Monitored using task manager). Any one has idea about what has gone wrong here? Interestingly, the handle leak is not occurring when I remove the Sleep(1000) call. Below is the code I used:

    int I64Counter = 1; SqlConnection objConnection; SqlConnectionStringBuilder objBuilder;

    objBuilder = new SqlConnectionStringBuilder(); objBuilder.DataSource = sDatabaseServer; objBuilder.IntegratedSecurity = true; objBuilder.InitialCatalog = sDatabaseName; objBuilder.ConnectTimeout = iConnectionTimeoutSeconds; objConnection = new SqlConnection(objBuilder.ConnectionString); objConnection.Open(); string sQuery = @" IF NOT EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'TEST_TABLE') CREATE TABLE TEST_TABLE(UUID VARCHAR(36), SEQUENCE BIGINT) "; using (SqlCommand objCommand = new SqlCommand(sQuery, objConnection)) { objCommand.ExecuteNonQuery(); } DB_OPERATION: sQuery = string.Format(INSERT INTO TEST_TABLE VALUES('{0}', {1}), sUUID, I64Counter); using (SqlCommand objCommand = new SqlCommand(sQuery, objConnection)) { objCommand.ExecuteNonQuery(); } I64Counter = I64Counter + 1; Thread.Sleep(1000); goto DB_OPERATION;

    • Edited by SQLNeophyte Thursday, June 7, 2018 12:12 PM
    Thursday, June 7, 2018 12:11 PM

All replies

  • If the memory usage in task manager is increasing, then it is not a memory leak. 

    A memory leak is when it caused the computer to go down. 

    But you're using Goto. Since 1957 is this statement seen as bad programming.

    https://en.wikipedia.org/wiki/Goto#Criticism

    You can of course disagree about this, But what you tell is one of the most obvious problems which occur by using GoTo


    Success
    Cor



    Thursday, June 7, 2018 12:22 PM
  • I would suggest not using a label/goto and instead use a for statement as shown in this code sample.

    Another idea is to use a WHILE statement. In this case we get Hospital id from 16 to 99

    CREATE PROCEDURE populateHospitals
    AS
    DECLARE @hid INT;
    SET @hid=16;
    WHILE @hid < 100
    BEGIN 
        INSERT hospitals ([Hospital ID], Email, Description) 
        VALUES(@hid, 'user' + LTRIM(STR(@hid)) + '@mail.com', 'Sample Description' + LTRIM(STR(@hid))); 
        SET @hid = @hid + 1;
    END


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, June 7, 2018 12:53 PM
    Moderator
  • Hi  Cor Ligthert,

    Thanks for your quick reply. I am not concerned with the memory, but it is the handle count that keeps increasing(It doesn't come down after going up to a particular value). Also I have replaced the goto with a while loop, but the issue is still occurring. Any other thoughts?

    Thursday, June 7, 2018 12:55 PM
  • Hi Karen,

    Thanks for the quick reply. Regarding your option1, I had tested my application after commenting the ExecuteNonQuery() function also as shown below:

    DB_OPERATION: sQuery =string.Format(INSERT INTO TEST_TABLE VALUES('{0}',{1}), sUUID, I64Counter);

    using(SqlCommand objCommand =newSqlCommand(sQuery, objConnection)) { // objCommand.ExecuteNonQuery(); }

    But I got the issue(Handle increasing) in that case also. So I strongly doubt the issue is related to the SqlCommand object creation statement below:

     SqlCommand objCommand =newSqlCommand(sQuery, objConnection)

    Since the code in the link you shared contains this statement I think the issue can occur using that method also.

    Regarding the usage of while loop, I have tested using while loop instead of goto as I have already mentioned in the reply to Cor Ligthert, but the issue still occurs


    Thursday, June 7, 2018 1:27 PM
  • Okay, this is what I was suggesting and this could be improved by creating a stored procedure for the T-SQL.

    What is not shown in regards to BaseSqlServerConnection and the variables mHasException and mLastException as these are from a MSDN code sample I wrote and can be found there.

    using System;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    using DataConnections;
    
    namespace InsertsWithLoop
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var ops = new Operations();
                if (ops.CreateAndPopulate())
                {
                    MessageBox.Show("Success");
                }
                else
                {
                    MessageBox.Show(ops.LastExceptionMessage);
                }
            }
        }
        public class Operations : BaseSqlServerConnection
        {
            public Operations() => DefaultCatalog = "ForumExample";
            public bool CreateAndPopulate()
            {
                var commandStatement = @"
                IF NOT EXISTS
                (
                    SELECT NAME
                    FROM SYSOBJECTS
                    WHERE NAME = 'TEST_TABLE'
                )
                CREATE TABLE TEST_TABLE
                (UUID     VARCHAR(36),
                 SEQUENCE BIGINT
                );
                DECLARE @id INT;
                SET @id=1;
                WHILE @id < 10001
                BEGIN 
                    INSERT TEST_TABLE (UUID, SEQUENCE) 
                    VALUES( CAST(@id as VARCHAR),@id ); 
                    SET @id = @id + 1;
                END;
                SELECT UUID
                      ,SEQUENCE
                  FROM dbo.TEST_TABLE";
    
                try
                {
                    using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })
                    {
                        using (SqlCommand cmd = new SqlCommand() { Connection = cn })
                        {
                            cmd.CommandText = commandStatement;
                            cn.Open();
                            var results = cmd.ExecuteNonQuery();
                            return results == 10000;
                        }
                    }
    
                }
                catch (Exception e)
                {
                    mHasException = true;
                    mLastException = e;
                }
    
                return IsSuccessFul;
            }      
        }
    }
    

    I first wrote the SQL in SQL-Server Management Studio (SSMS) e.g.

    IF NOT EXISTS
    (
        SELECT NAME
        FROM SYSOBJECTS
        WHERE NAME = 'TEST_TABLE'
    )
        CREATE TABLE TEST_TABLE
    (UUID     VARCHAR(36),
     SEQUENCE BIGINT
    );
    DECLARE @id INT;
    SET @id=1;
    WHILE @id < 10001
    BEGIN 
        INSERT TEST_TABLE (UUID, SEQUENCE) 
        VALUES( CAST(@id as VARCHAR),@id ); 
        SET @id = @id + 1;
    END;
    SELECT UUID
          ,SEQUENCE
      FROM dbo.TEST_TABLE

    Took the above SQL and simply dropped it into code.

    All objects used above are disposed of promptly.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, June 7, 2018 2:06 PM
    Moderator
  • Hi  Cor Ligthert,

    Thanks for your quick reply. I am not concerned with the memory, but it is the handle count that keeps increasing(It doesn't come down after going up to a particular value). Also I have replaced the goto with a while loop, but the issue is still occurring. Any other thoughts?

    OK, 

    I was hoping there came a follow up. The memory will be released if there is time to release. But as long as the program is inside a method (Currently your while loop) that will not be done. 

    Therefore remove your sleep part and set the procedure in a timer method which elapsed every 1000 milliseconds. 


    Success
    Cor


    Thursday, June 7, 2018 2:43 PM
  • If the memory usage in task manager is increasing, then it is not a memory leak. 

    A memory leak is when it caused the computer to go down. 

    But you're using Goto. Since 1957 is this statement seen as bad programming.

    https://en.wikipedia.org/wiki/Goto#Criticism

    You can of course disagree about this, But what you tell is one of the most obvious problems which occur by using GoTo


    Success
    Cor



    Each for/while/do...while/if/else/break/continue is goto :) Is it bad to use these commands?

    No it is not bad. Right way is reduce it by well using object oriented programming. 

    Thursday, June 7, 2018 3:24 PM
  • Hi Karen,

    Your sample inserts a specified number of records into the table using SQL script. But I need an infinite loop implementation which keeps inserting records with a finite delay between each insert.

    Friday, June 8, 2018 12:25 PM
  • Hi Karen,

    Your sample inserts a specified number of records into the table using SQL script. But I need an infinite loop implementation which keeps inserting records with a finite delay between each insert.

    Any reason you only look at the replies from Karen, I thought I had yesterday exactly given the solution to this problem.  

    Success
    Cor

    Friday, June 8, 2018 1:29 PM
  • Hi Karen,

    Your sample inserts a specified number of records into the table using SQL script. But I need an infinite loop implementation which keeps inserting records with a finite delay between each insert.

    Use this, adjust wait time as needed via WAITFOR

    IF NOT EXISTS
    (
        SELECT NAME
        FROM SYSOBJECTS
        WHERE NAME = 'TEST_TABLE'
    )
        CREATE TABLE TEST_TABLE
    (UUID     VARCHAR(36),
     SEQUENCE BIGINT
    );
    DECLARE @id INT;
    SET @id=1;
    WHILE 1 =1
    BEGIN 
        INSERT TEST_TABLE (UUID, SEQUENCE) 
        VALUES( CAST(@id as VARCHAR),@id ); 
        SET @id = @id + 1;
    	WAITFOR DELAY '00:00:1' ;
    END;


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 8, 2018 1:43 PM
    Moderator
  • Hi Karen,

    Your sample inserts a specified number of records into the table using SQL script. But I need an infinite loop implementation which keeps inserting records with a finite delay between each insert.

    Use this, adjust wait time as needed via WAITFOR

    IF NOT EXISTS
    (
        SELECT NAME
        FROM SYSOBJECTS
        WHERE NAME = 'TEST_TABLE'
    )
        CREATE TABLE TEST_TABLE
    (UUID     VARCHAR(36),
     SEQUENCE BIGINT
    );
    DECLARE @id INT;
    SET @id=1;
    WHILE 1 =1
    BEGIN 
        INSERT TEST_TABLE (UUID, SEQUENCE) 
        VALUES( CAST(@id as VARCHAR),@id ); 
        SET @id = @id + 1;
    	WAITFOR DELAY '00:00:1' ;
    END;


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Hi Karen,

    Thank you for your continued support. I also had thought about implementing the delay in the SQL script. But the issue with this method is that, we would have no control with thin the C# program once the script started executing. I missed to mention this issue in my previous reply..

    Saturday, June 9, 2018 4:38 AM
  • Create a stored procedure e.g.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE dbo.spInfiniteLooper AS
    BEGIN
    IF NOT EXISTS
    (
        SELECT NAME
        FROM SYSOBJECTS
        WHERE NAME = 'TEST_TABLE'
    )
        CREATE TABLE TEST_TABLE
    (UUID     VARCHAR(36),
     SEQUENCE BIGINT
    );
    DECLARE @id INT;
    SET @id=1;
    WHILE 1 =1
    BEGIN 
        INSERT TEST_TABLE (UUID, SEQUENCE) 
        VALUES( CAST(@id as VARCHAR),@id ); 
        SET @id = @id + 1;
    	WAITFOR DELAY '00:00:1' ;
    END;
    END
    
    GO
    
    Then write code similar to the following.
    private CancellationTokenSource cts;
    private async void TestSqlServerCancelSprocExecution()
    {
        cts = new CancellationTokenSource();
        try
        {
            await Task.Run(() =>
            {
                using (SqlConnection conn = new SqlConnection("connStr"))
                {
                    conn.InfoMessage += conn_InfoMessage;
                    conn.FireInfoMessageEventOnUserErrors = true;
                    conn.Open();
    
                    var cmd = conn.CreateCommand();
                    cts.Token.Register(() => cmd.Cancel());
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "dbo.[CancelSprocTest]";
                    cmd.ExecuteNonQuery();
                }
           });
        }
        catch (SqlException)
        {
            // sproc was cancelled
        }
    }
    
    private void cancelButton_Click(object sender, EventArgs e)
    {
        cts.Cancel();
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, June 9, 2018 9:23 AM
    Moderator
  • Create a stored procedure e.g.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE dbo.spInfiniteLooper AS
    BEGIN
    IF NOT EXISTS
    (
        SELECT NAME
        FROM SYSOBJECTS
        WHERE NAME = 'TEST_TABLE'
    )
        CREATE TABLE TEST_TABLE
    (UUID     VARCHAR(36),
     SEQUENCE BIGINT
    );
    DECLARE @id INT;
    SET @id=1;
    WHILE 1 =1
    BEGIN 
        INSERT TEST_TABLE (UUID, SEQUENCE) 
        VALUES( CAST(@id as VARCHAR),@id ); 
        SET @id = @id + 1;
    	WAITFOR DELAY '00:00:1' ;
    END;
    END
    
    GO
    Then write code similar to the following.
    private CancellationTokenSource cts;
    private async void TestSqlServerCancelSprocExecution()
    {
        cts = new CancellationTokenSource();
        try
        {
            await Task.Run(() =>
            {
                using (SqlConnection conn = new SqlConnection("connStr"))
                {
                    conn.InfoMessage += conn_InfoMessage;
                    conn.FireInfoMessageEventOnUserErrors = true;
                    conn.Open();
    
                    var cmd = conn.CreateCommand();
                    cts.Token.Register(() => cmd.Cancel());
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "dbo.[CancelSprocTest]";
                    cmd.ExecuteNonQuery();
                }
           });
        }
        catch (SqlException)
        {
            // sproc was cancelled
        }
    }
    
    private void cancelButton_Click(object sender, EventArgs e)
    {
        cts.Cancel();
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Hi Karen,

    Again thanks for the reply and a different solution.

    I discussed the stored procedure method with my client and the client is not willing to use stored procedures due to some reasons. The client is so keen to use the method I have shown in the original post and the the only thing that worries him about that method is the increase of handles. So the client wish to shift the focus on to how to eliminate the increase in handle count in the initial method. So I would like to get more information on what causes the handle count to increase in the initial method. Can you please inform me if you have any information regarding the increase of handle count?

    Monday, June 11, 2018 11:28 AM