How to check if a table is being queried in SQL Server

Question

I need to know if there is any way to find out if a table is being queried by some other process in SQL Server.

I am trying to Merge Empty Partitions on a table, and that table is being queried by many process. So, i need to check if the table is being read/insert by other process,if yes than my merge operation will not proceed. Because if it runs then it gets locked and fails over time. Due to which sometimes i run out of partitions or i have to run it manually.

use sql server profiler... http://msdn.microsoft.com/en-us/library/ms181091.aspx — loxxy, Dec 11 '12 at 07:40
What are your partitions based on? If it is based on dates, you could come up with a better partitioning scheme. — Vaibhav Desai, Dec 11 '12 at 07:42
Any query that answers the question "Is this table being used?" gives you an answer that is stale before you can act on it. — Damien_The_Unbeliever, Dec 11 '12 at 07:44
Check out. http://stackoverflow.com/questions/2155594/how-can-i-tell-if-a-database-table-is-being-accessed-anymore-want-something-lik — Daniel PP Cabral, Dec 11 '12 at 07:56

score 1 · Answer 1 · answered Dec 11 '12 at 08:26

1

How would possibly help knowing would querying if the table was not used? Think about it, any information you get ('no query') is already obsolete by the time you act. This is not the way to go, the way to go is simply to reduce the lock timeout and attempt to do your operation:

SET LOCK_TIMEOUT 1;
ALTER PARTITION FUNCTION ... MERGE ...

answered Dec 11 '12 at 08:26

Remus Rusanu

288,378
40
442
569

this can work for me.. thnx – Nausif Dec 11 '12 at 09:03

How to check if a table is being queried in SQL Server

1 Answers1