none
SQLConnection.ClearPool(myConnection) doesn't clear open connections RRS feed

  • Question

  • Hello,

    when I trying to drop database I get an error "Cannot drop database "TEMP_GLOBAL" because it is currently in use"

    I use SQLConnection.ClearPool(myConnection) method (before calling to drop my DB) which supposed to clear the connection pool and close all open connections. But it doesn't.

    I still see a record in sysprocesses for db "TEMP_GLOBAL"

    also myConnection is opened in using block hence the connection should be closed by called Dispose by the exiting of the using block.

    I would appreciate any pointers that would help me resolve this issue

    thanks

    Tuesday, September 4, 2012 6:55 AM

Answers

  • Hello,

    this issue is solved.

    I was need to call to ClearPool before connection is disposed

    using (SQLConnection conn = new SQLConnection)

    {

    conn.Open();

    cmd.ExecuteNonQuery();

    SQLConnection.ClearPool(conn);

    }

    thanks

    • Marked as answer by roywax Thursday, September 6, 2012 6:13 AM
    Thursday, September 6, 2012 6:13 AM

All replies

  • Please look at here. This is the solution.
    Wednesday, September 5, 2012 2:12 AM
  • Are you sure there is no opened connection at the time you call ClearPool() and Drop Database.

    ClearPool clears the connection pool that is associated with the connection. If additional connections associated with connection are in use at the time of the call, they are marked as not to pool when Close () method is called.

    Another reason could be, other user might connected to your database?

    anyway below statement should work drop.

    ALTER DATABASE [dbname]
    SET SINGLE_USER --or RESTRICTED_USER
    WITH ROLLBACK IMMEDIATE;
    GO
    DROP DATABASE [dbname];
    GO


    Lingaraj Mishra

    Wednesday, September 5, 2012 9:28 AM
  • Hello,

    this issue is solved.

    I was need to call to ClearPool before connection is disposed

    using (SQLConnection conn = new SQLConnection)

    {

    conn.Open();

    cmd.ExecuteNonQuery();

    SQLConnection.ClearPool(conn);

    }

    thanks

    • Marked as answer by roywax Thursday, September 6, 2012 6:13 AM
    Thursday, September 6, 2012 6:13 AM