CODEWITHSUNDEEP
azure-sql-database
CarCrazyBen
CarCrazyBen

Reputation: 1136

kill Azure SQL DB sessions

We are using Azure SQL DB for our cloud database solution. One of our developers encountered some errors yesterday while trying to execute some insert statements. I suspect he tried again, and again, and....

I ran the below query to identify open Sessions and see 30 separate entries for the User our developer uses to log in. Is it worth killing these sessions? Or should I leave them alone? Is there something else I should evaluate to tell me whether or not our DB has "hung" sessions?

SELECT   
c.session_id, c.net_transport, c.encrypt_option,   
c.auth_scheme, s.host_name, s.program_name,   
s.client_interface_name, s.login_name, s.nt_domain,   
s.nt_user_name, s.original_login_name, c.connect_time,   
s.login_time   
FROM sys.dm_exec_connections AS c  
JOIN sys.dm_exec_sessions AS s  
ON c.session_id = s.session_id  
WHERE 1=1
  and login_name = 'some_user_name'

Upvotes: 1

Views: 4563

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28920

I don't recommend killing sessions with out knowing what they are doing first..

so step1 would be to understand their current state..

select spid,blocked,waittime,waittype,lastwaittype,txt.text
 from sys.sysprocesses sp
 cross apply
 sys.dm_exec_sql_text(sp.sql_handle)  txt

The above dmv gives the state of all sessions and you can see if any of them are blocked ,if they are blocked, you can check the blocking session and try knowing why it is blocked by inputting blocking session spid like below

dbcc inputbuffer(blocking session spid)

With this you can try checking if the hung state you are experiencing is due to blocking.You can try resolving the blocking or try killing blocking spid

IF you don't see any blocking,but if think those sessions are hung,try running below dmv to see last read,write state..by this way you can check if the session is really doing some work 

 select last_read,
last_write,session_id,connect_time
 from sys.dm_exec_connections

Try checking what the session is about before killing it(using input buffer..because, some may be internal processes like

SSISDB.internal.update_worker_agent_status;1

Upvotes: 4

Alberto Morillo
Alberto Morillo

Reputation: 15688

You can kill them. Open transactions will be rolled back. Below script can help you kill all connections except yours.

DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'

FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id <> @@SPID
--WHERE status = 'sleeping'
ORDER BY c.connect_time ASC

EXEC(@kill)

Add other sessions you want to keep on the WHERE clause.

Upvotes: 1

Related Questions