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.

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)