0

Is there any way to see how far a script has been executed?

I'm currently running a script with adds many indexes and so is taking a long time to complete.

So I was wondering if there is a way to see what bit of code is currently being executed (without previously using print statements) so I can have a rough idea when the script will finish

Jamie Stuart Robin Parsons
  • 1,132
  • 4
  • 20
  • 36
  • 2
    You say you don't want print statements, but do you have a logger? – Josh Dec 10 '13 at 15:28
  • Related question http://stackoverflow.com/questions/9293429/currently-running-query-inside-a-stored-procedure/9293720#9293720 – Martin Smith Dec 10 '13 at 15:38
  • You could figure out what it's currently running, what it's waiting on and whether it's being blocked by looking at DMVs like `sys.dm_exec_requests`... – Aaron Bertrand Dec 10 '13 at 15:44

2 Answers2

0

If the script you are trying to monitor is being run across the network and not from a scheduled job or stored procedure on the server, then you can use network monitoring tools to inspect the data across the network to see what requests are being made. You will likely need something like Foglight for SQL Server to really get good information.

Long-term, the best thing is to write to a logging table that would have the details. ;-)

Additionally, this documentation on monitoring SQL Server might be of use in tracking down a way to monitor the current execution step.

See DBCC INPUTBUFFER for a way to see what statement was last run by a connection.

Norman H
  • 2,248
  • 24
  • 27
0

Depending on how your procedure was written, you may be able to tell where it's executing by running SQL Server Profiler.

Michael
  • 599
  • 6
  • 11