How to Kill SQL Threads or Kill User Sessions

0


How to Kill SQL Threads or Kill User Sessions

Method-1

This is one of best and quickest way to Kill All Threads or Kill All User Sessions or Kill All Sessions

Sometime you may get error while restoring a database that action cannot be performed as user’s sessions are active on the database. If it is important to restore and killing database user’s session won’t harm much, then you can use below script. This will temporally change the access mode to single user and kill sessions, and then revert it to multi user mode.

Note: This script will kill all users session associated with a database. In this script I have demonstrated AdventureWorks database.

USE master;

GO

ALTER DATABASE AdventureWorks

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

ALTER DATABASE AdventureWorks

SET MULTI_USER;

GO


Method-2

Let’s say you want to kill all processes by a particular user or account, then you can use below script to find the related spid for all the processes initiated by user or account. Here my account is HABIB\Administrator

SELECT spid from master..sysprocesses

WHERE loginame = ‘HABIB\Administrator’


Once you get the spid details, you can simply execute KILL command against those processes.

Command is KILL <spid>

 

For more article updates, videos and posters join our official page in Facebook

Facebook Page: https://www.facebook.com/ServerGeeks

Web Site: https://servergeeks.wordpress.com/

Video Channel: https://www.youtube.com/user/Habibmvp

T-SQL – Finding SQL Server Service Restart Date-Time

3

T-SQL – Finding SQL Server Service Restart Date-Time


There are many ways to find out the date & time on which SQL Server service was last restarted.

Method-1: Checking SQL Server Error Log

First time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.


sp_readerrorlog 0,1,‘Copyright (c)’

Method-2: Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.


SELECT sqlserver_start_time FROM
sys.dm_os_sys_info;


Or you can use Using sys.dm_exec_sessions DMV

session_id is created when the SQL Server is started and the login time remains the same until the service is restarted again.

SELECT login_time FROM
sys.dm_exec_sessions

WHERE session_id = 1;


Method-3: Start time of the Default Trace

The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces

where is_default = 1


Method-4: Creating date of tempdb

The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.

SELECT create_date FROM sys.databases
WHERE name = ‘tempdb’

T-SQL – To Get Graphical Execution Plan for Active Sessions

0

T-SQL – To Get Graphical Execution Plan for Active Sessions

If you want to get the graphical execution plan for all active sessions on an instance using T-SQL Script, then this handy script would be a great help. The below query gets the session_id and the query_plan (in XML format). This file in turn needs to be saved as a .sqlplan file to view the Execution Plan in Graphical format. The “Save as” option in SSMS or any text editor can be used for this purpose.

SELECT

er.session_id ,

qp.query_plan

FROM sys.dm_exec_requests er

CROSS APPLY sys. dm_exec_query_plan( er.plan_handle ) qp

If you click the query_plan link, you will get the execution plan in the graphical format (screenshot below)