How can I know the execution status of a stored procedure in MS SQL Server

Question

I have a long stored procedure and how do I know it execution status? Something like 50% completed etc. I searched a lot and didn't get any satisfied result.So hope that it is not possible.I am using MS SQL Server 2008. IF it is not available may I know the reason why it is not available ?

Answer 1

You can use:

RAISERROR ('Some comment, immediate', 0, 1) WITH NOWAIT 

to print message immediately after this statement. (it does not generate error message).

Answer 2

No, it is not available at least in the form you describe. This is because it's very hard to know both what a Stored Procedure is going have to to do internally (what if it calls other stored procs?) and how long it will take, until it tries to do it.

Sure, you could examine the proc and see it does SELECT * FROM [TableName]

But then you'd need to know the size of the table, the data types being returned, the disk access speed, whether the query has been cached, etc. etc.

Then, what if another process locks a record unexpectedly and it takes longer than usual? You don't know how long that lock will be held - so how do you calculate a percentage of an unknown variable (time)?

The best you can do is put 'checkpoints' in your proc to either print messages or send emails or log in a table after each discrete step, e.g.

print getdate()
print 'Selecting from [Users]'

Select * from [Users]

print getdate()
print 'Selecting from [Clients]'

Select * from [Clients]

print getdate()
print 'Finished'