Sometimes operations (like drop, etc) will fail on your DB if someone Running a command and locking a resource.
So I came up (or found somewhere) a query to tell me what’s currently running.
SELECT P.spid as SPID, P.kpid as KPID, P.dbid as DBID, D.name, convert(varchar, P.last_batch, 5) as StartDate, convert(varchar, P.last_batch, 8) as StartTime,
P.hostname as HostName, P.nt_domain as Domain, P.nt_username as Username,P.cmd as Command, P.waitresource, P.status as [Status],P.cpu as CPU,P.memusage as MEM,P.physical_io as IO
FROM sys.sysprocesses P
JOIN sys.databases D
ON P.dbid = D.database_id
ORDER BY D.name, P.cpu
This will return all the processes, sometimes you just want the processes for a specific user. Then you just need to add a where clause.
WHERE nt_username = ‘
’
This post first appeared on Random Technical Thoughts | Things I Want To Remem, please read the originial post: here