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 ?

score 5 · Accepted Answer · answered Jul 10 '12 at 14:15

5

You can use:

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

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

answered Jul 10 '12 at 14:15

Jānis

2,216
1
17
27

@Janis Thanks you ,So we can see the value only while executing with SSMS. right ? – kbvishnu Jul 10 '12 at 14:21
1

The best part: NO! you can access them from .NET: http://stackoverflow.com/questions/765142/access-to-sql-server-messages-via-ado-net – Jānis Jul 10 '12 at 14:25

score 3 · Answer 2 · answered Jul 10 '12 at 14:09

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'

This is an option. But consider the situation that the sp is running under transaction. if we have a log table and we tries to enter the datetime, but we can see only after the transaction.So we need to wait till then. I hope I am right. Pls tell me if it is wrong. — kbvishnu, Jul 10 '12 at 14:14
If the `PRINT` statements are the only thing producing output, you might not receive any until the whole process completes, due to buffering. `RAISERROR('Selecting from [Users]',10,1) WITH NOWAIT ` sends an informational message and doesn't suffer from buffering issues. — Damien_The_Unbeliever, Jul 10 '12 at 14:17
@VeeKeyBee Yes, that's right - the table-logging idea would be undone if a `TRANSACTION` was rolled-back, but the general 'checkpoint' approach would still work. — Widor, Jul 10 '12 at 14:18

Ivan Golović · Answer 3 · 2012-07-10T14:37:25.530

If you have a .NET client application you can receive approximate progress messages sent by server by using following syntax in C#:

    using (SqlConnection conn = new SqlConnection(...))
    {
        conn.FireInfoMessageEventOnUserErrors = true;
        conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
        using (SqlCommand comm = new SqlCommand("dbo.sp1", conn) 
               { CommandType = CommandType.StoredProcedure })
        {
            conn.Open(); 
            comm.ExecuteNonQuery();
        }
    }

    static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
    {
        // Process received message
    }

While messages on the server side can be sent as described by Janis in one of previous answers:

    RAISERROR('33%%..', 10, 0) WITH NOWAIT

score 1 · Answer 4 · answered Jul 10 '12 at 14:15

There is no feature built in to SQL Server to check the status of a stored procedure but you can write something that will do something similar. In our case, we created a logging function that post a message after each process within a stored proc.

Let's say you have a stored proc that runs multiple queries:

SELECT *
FROM yourTable

UPDATE ...
SET ...

DELETE 
FROM ...

You can put in place after each query/process a step that will post data to a logging table:

SELECT *
FROM yourTable

-- log query
INSERT INTO LogTable (DateComplete, Status, TaskId)
VALUES (getdate(), 'Complete', 1)

UPDATE ...
SET ...

-- log query
INSERT INTO LogTable (DateComplete, Status, TaskId)
VALUES (getdate(), 'Complete', 2)

DELETE 
FROM ...

-- log query
INSERT INTO LogTable (DateComplete, Status, TaskId)
VALUES (getdate(), 'Complete', 3)

You can take this even further by using a TRY...CATCH block around your queries which you can then have different messages on whether or not the process was successful or failed.

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

4 Answers4