Is it possible to tell what query a given SQL Server process is running?

Our application has st开发者_Go百科arted having problems with SQL Server 2005 queries getting blocked. Is is possible to tell what query the blocking process is running? If it is possible how is it done?

If you are running an SQL Server version bigger than Express, the Activity Monitor of SQL Server Management Studio allows you to make a snapshot of the current activity on the system. There, you can see which connection is blocking which other connection. You can also see which tables (or other objects) are locked by these connections, giving you a hint on what causes the blocking.

If you know the SPID of the blocking process, you can run DBCC INPUTBUFFER(xxx), where xxx is the SPID. If you're using Activity Monitor, you can achieve the same thing by double-clicking the row containing that process.

I know that it is possible, however, not being a sql guru I can't give you a query to run that will tell you what query is blocking. I will plug an application that I am evaluating which is doing very well for us so far.

Sql Sentry has been helping my small team identify problems areas. This is noteworthy in that we only have one person who has any proficiency with sql server.

I have no affiliation with the company nor am I being compensated for the plug in any way.

Run a SQL Profiler trace, only including Blocked Process Report (within Errors). Keep it running during the day and monitor it for blocking information.