How to tell what processes are running on your SQL Server database

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 = ‘<username>’

Advertisements

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s