Have Geek Brain

Determine the status of the executable T-Sql script

I am running a T-SQL script to transfer old data to our new DB. It will take a lot of time! Now it is over two and a half hours. Is there a way to check the status of the script being executed?

0


a source to share


4 answers


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



+1


a source


An easy way would be to include instructions print

at intervals in your script.



0


a source 


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

This provides immediate customer feedback: PRINT cannot. A severity level of 10 also means it's a warning, so the code doesn't crash.

0


a source


You are not moving the data, is it some kind of cursor? If you are moving one record at a time, it can take days or months, even depending on how much data you have. Now, if you have a lot of data to move, you may want a loop, but still not one record at a time, but a loop that processes a batch of records. It is often faster to insert a million records, 1000 at a time, than all millions at once.

If everything is currently in the same transaction, you may also run into 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.

Unless you've fixed this with logging or some other easy way to track progress, I think the suggestion to monitor counter (*) on inserted tables is a good one (and maybe you're only a real choice). Just make sure you are not using a lock, or the results may not return until after the insert is complete. Also check if blocking happens, often when something like this happens for too long some other processes will block it.

0


a source






More articles:


All Articles