SQLAlchemy engine.execute() leaves a connection to the database in sleeping status

Question

I am using SQL server database. I've noticed that when executing the code below, I get a connection to the database left over in 'sleeping' state with an 'AWAITING COMMAND' status.

    engine = create_engine(url, connect_args={'autocommit': True})
    res = engine.execute(f"CREATE DATABASE my_database")
    res.close()
    engine.dispose()

With a breakpoint after the engine.dispose() call, I can see an entry on the server in the EXEC sp_who2 table. This entry only disappears after I kill the process.

Precondition

Answer 1

You basically want to kill all the connections You could use something like this:

For MS SQL Server 2012 and above

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

For MS SQL Server 2000, 2005, 2008

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('MyDB')

EXEC(@kill); 

Or something more script-like:

DECLARE @pid SMALLINT, @sql NVARCHAR(100)
DECLARE curs CURSOR LOCAL FORWARD_ONLY FOR
  SELECT DISTINCT pid FROM master..sysprocesses where dbid = DB_ID(@dbname)
OPEN curs
fetch next from curs into @pid
while @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'KILL ' + CONVERT(VARCHAR, @pid)
    EXEC(@sql)
    FETCH NEXT FROM curs into @pid
END
CLOSE curs
DEALLOCATE curs

More can be found here: Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

Precondition

Answer 2

Probably Connection Pooling

Connection Pooling

A connection pool is a standard technique used to maintain long running connections in memory for efficient re-use, as well as to provide management for the total number of connections an application might use simultaneously.

Particularly for server-side web applications, a connection pool is the standard way to maintain a “pool” of active database connections in memory which are reused across requests.

SQLAlchemy includes several connection pool implementations which integrate with the Engine. They can also be used directly for applications that want to add pooling to an otherwise plain DBAPI approach.

.

I'm not sure if this is what gets in the way of my teardown method which drops the database

To drop a database that's possibly in use try:

USE master;
ALTER DATABASE mydb SET RESTRiCTED_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE mydb;

Tellurize

Yeah, I'm not sure if this is what gets in the way of my teardown method which drops the database - I get "database in use" error which I assume is a sleeping connection from the pool. Not sure if I can time these out. – Precondition