How can I check if BULK INSERT is running on a table?

Question

A BULK INSERT command may take a long time to run.

I have a program that receives multiple files and pushes them to the DB using the BULK INSERT command to a specific table. After a file is uploaded, the program deletes it and moves to the next.

The BULK INSERT command may continue to run on the DB after the program stopped. so a file may be loaded to the DB, or still in process when my program starts.

So, my questions are:

1. Can I tell if a BULK INSERT command is still running for a specific table?
2. Can I tell if a file has been already loaded to the DB?

I can get the running query by running:

select t.text 
from sys.dm_exec_requests r 
cross_apply sys.dm_exec_sql_text(r.sql_handle) t
where command= 'BULK INSERT' 

but how can I know on which table it runs?

Answer 1

There is no way to directly monitor the queries against a single table in a database. Instead, what you have is the ability to see queries running against that database. Then, you can filter the query text to determine which table or tables that query is running against. So, in order to do exactly what you want, you need to pick one of the methods for monitoring queries. Extended Events are historical more than they're current, although, you can look for statement starts without statement ends to find queries that are active. Query Store is historic only. Querying the DMVs is usually the best way to see what's immediately happening.

So, you want to query sys.dm_exec_requests to see what's currently running. Then you combine that with sys.dm_exec_query_text to see the specific query being run. Finally, can filter for both the BULK INSERT statement and your table name against the query text. If you don't want to write your own queries to combine these DMVs, as was pointed out in the comments, sp_whoisactive, is an open-source query that you can install on your servers. Then, using it, you do the same filtering against the query text.

Regardless of how you do it, you just have to filter on the query text. There's no other way to see what's running against a single table.