MS SQL provides a Stored Procedure (SP) named sp_who2 which will show up all the actions going on on the MS SQL DB server as shown below:
You can have a temporary DB created with data from sp_who2 for your further processing as shown below
— check if there is a table already existing with the name
— you have choosen for your temp DB, if exists delete it
if OBJECT_ID(‘tempdb..#temp_spwho’) > 0 drop table #temp_spwho
go— create new table with required fields of sp_who2
create table #temp_spwho (
SPID int not null,
Status varchar (255) not null,
Login varchar (255) not null,
HostName varchar (255) not null,
BlkBy varchar(10) not null,
DBName varchar (255) null,
Command varchar (255) not null,
CPUTime int not null,
DiskIO int not null,
LastBatch varchar (255) not null,
ProgramName varchar (255) null,
SPID2 int not null,
RequestedBy int not null
)
go— fetch the sp_who2 results into temporary table for processing
insert #temp_spwho exec sp_who2
go— Show the rows whose SPIDs > 50 and BlkBy is NOT ‘ . ‘
select * from #temp_spwho where SPID > 50 and BlkBy != ‘ . ‘ order by LastBatch desc
go— delete the temp DB
if OBJECT_ID(‘tempdb..#temp_spwho’) > 0 drop table #temp_spwho
To further analyze what a given process is doing, you can check via below command:
DBCC inputbuffer (<SPID>)
