Determine the status of an executing T-Sql script

Question

I'm running a T-SQL script to migrate old data to our new DB. It's taking to long to process! Right now it's more than 2 and a half hours. Is there a way to check the status of the executing script?

Answer 1

An easy way would be to include print statements at intervals in your script.

Answer 2

you could just continually run "select count(*) from table_being_populated" on the new database and compare it to the counts in the old DB and watch it grow as the script is running to get an idea of how much data is currently in the new DB.

Answer 3

RAISERROR ('marker', 10, 1) WITH NOWAIT

This provides immediate feedback to the client: PRINT may not. Severity 10 also means it's a warning so does not make the code fall over.

Answer 4

You aren't moving the data is some sort of cursor are you? If you are moving one record at a time, this could take days or months even depending on how much data. Now if you have lots of data to move, you might want a loop but still not a one record at a time cursor, rather a loop that processes a batch of records. It is often faster to insert a million records, 1000 at a time than all million at once.

If everything is currently in one transaction, you may also have problems if you need to stop and rethink this long_running process as it will have to rollback everything. This can take a long time.

SInce you didn't set this up with logging or some other way to watch progress easily, I think the suggestion to monitor the count(*) on the tables being inserted to is a good one (and may be you only real choice). Just make sure you use no lock or the results may not return until the insert is done. Also check to see if there is blocking happening, often when something like this is taking too long, some other process is blocking it.

Answer 5

I have the same question. My query has been running for over 7 hours now... i need to know where in the process the query is. I know the reason why it's taking so long, i just want to know when it is going to end. Any ideas? I know that i've seen someone pull up a window that would monitor a query by showing a flowchart. Does anyone know how to get to that page?

Thanks!