MSSQL
Process Management

Current Process

SELECT @@SPID AS CurrentSPID;

In SSMS the SPID is also shown in the bottom information bar of a query window.

Active Sessions

exec sp_who

exec sp_who2

EXEC sp_who 'login'

EXEC sp_who @loginame = 'login'

EXEC sp_who sessionID

EXEC sp_who 'ACTIVE'

SELECT *

  FROM sys.dm_exec_sessions;

SELECT *

  FROM sys.dm_exec_sessions

 WHERE is_user_process = 1;

Activity Monitor...

Note: Column headings have dropdowns. User Processes column defaults to 1 (i.e. only user processes). Set to "all" to see system and user processes,

Kill Sessions

To kill a process...

KILL 57

In this example we are killing the process with an SPID of 57.Run from SSMS Query window or sqlcmd...

For rollback status...

KILL 57 WITH STATUSONLY

This example does NOT kill any process, but will show the rollback progress of a process that is already killed. If the session has not been killed already or rollback has already completed, you will get...Msg 6120, Level 16, State 1, Line 1 Status report cannot be obtained. Rollback operation for Process ID 57 is not in progress

Blocking Sessions

SELECT *

  FROM sys.dm_exec_requests

 WHERE blocking_session_id <> 0;

GO

Last Activity

DECLARE @last_boot DATETIME

SET @last_boot = (SELECT[sqlserver_start_time] FROM sys.dm_os_sys_info)


SELECT @@servername AS [ServerName],

       'last_boot' = @last_boot,

       'days_since_last_boot' = DATEDIFF(d, @last_boot, getdate())


IF OBJECT_ID('tempdb..##Table_usage_data') IS NOT NULL

   DROP TABLE ##Table_usage_data


CREATE TABLE ##Table_usage_data (ID int identity (1,1),

                                 [database] VARCHAR(255),

                                 [last_user_seek] DATETIME,

                                 [last_user_scan] DATETIME,

                                 [last_update] DATETIME)


DECLARE @get_last_user_activity_timestamp VARCHAR(MAX)

SET @get_last_user_activity_timestamp = '' SELECT @get_last_user_activity_timestamp = @get_last_user_activity_timestamp + 'SELECT db_name([database_id]), MAX(last_user_seek), MAX(last_user_scan), MAX([last_user_update]) FROM sys.dm_db_index_usage_stats WHERE DB_NAME([database_id]) = ''' + [name] + ''' GROUP BY [database_id];' + CHAR(10) FROM sys.databases WHERE [database_id] > 4 AND [state_desc] = 'online'


INSERT INTO ##Table_usage_data ([database],

                                [last_user_seek],

                                [last_user_scan],

                                [last_update])

  EXEC (@get_last_user_activity_timestamp)


SELECT [database],

       last_user_scan,

       last_user_seek,

       last_update

  FROM ##Table_usage_data 

Bibliography