How to find out the execution status of a stored procedure in MS SQL Server

Question

I have a long stored procedure and how can I find out its execution status? Something like 50% completed, etc. I searched a lot and did not get a satisfactory result. Therefore, I hope that this is impossible. I am using MS SQL Server 2008. IF it is not available, can I find out the reason why it is not available?

Answer 1

You can use:

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

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

Answer 2

No, it is not available, at least in the form that you describe. This is because it is very difficult to find out what to do with a stored procedure in order to do it internally (what if it calls other stored procedures?) And how long will it take until it tries to do it.

Of course, you could examine proc and see what it does SELECT * FROM [TableName]

But then you will need to know the size of the table, the returned data types, the speed of access to the disk, whether there was a cached request, etc. etc.

Then what if another process unexpectedly closes the record and it takes longer than usual? You do not know how long the lock will be held - so how do you calculate the percentage of an unknown variable (time)?

The best thing you can do is put “breakpoints” in your proc on print messages or send emails or enter the table after each individual step, for example.

 print getdate() print 'Selecting from [Users]' Select * from [Users] print getdate() print 'Selecting from [Clients]' Select * from [Clients] print getdate() print 'Finished' 

Answer 3

If you have a .NET client application, you can receive approximate progress messages sent by the server using the 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 server-side messages can be sent as described by Janis in one of the previous answers:

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

Answer 4

SQL Server does not have a built-in function for checking the status of a stored procedure, but you can write something that will do something similar. In our case, we created a registration function that sends a message after each process in a stored process.

Let's say you have a saved proc that runs several requests:

 SELECT * FROM yourTable UPDATE ... SET ... DELETE FROM ... 

After each request / process, you can enter a step that will send data to the 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 do this even more by using the TRY...CATCH block around your queries, in which you may have different messages about whether the process was successful or failed.