dgabele (5/18/2010)

---

Once a job completes (100%), it no longer appears in the dmv right? Am I correct in understanding that the DMV only shows currently executing requests?

Thanks

Kari Suresh (5/19/2010)

---

where and how we can use this? Anybody can explain with an example?

SET NOCOUNT ON
GO
CREATE TABLE RollbackTest (a CHAR(8000))
GO
BEGIN TRANSACTION
GO
INSERT RollbackTest VALUES ('test')
GO 100000

ROLLBACK TRANSACTION

SELECT percent_complete, *
FROM sys.dm_exec_requests
WHERE session_id = <spid of the rollback process>

vk-kirov (5/19/2010)

---

For example, it may be very useful when a big transaction rolls back and you want to know the progress status of the rollback.
Here is a simple example. First of all, let's create a table and fill it with the data (it took 5 minutes on my local machine, and 1800 MB on the hard drive (800 MB for the data file and 1000 MB for the log file)).

SET NOCOUNT ON
GO
CREATE TABLE RollbackTest (a CHAR(8000))
GO
BEGIN TRANSACTION
GO
INSERT RollbackTest VALUES ('test')
GO 100000

Here we have a huge uncommitted transaction. Let's roll it back (it took 6 minutes on my local machine):

ROLLBACK TRANSACTION

We can monitor the rollback progress in another window:

SELECT percent_complete, *
FROM sys.dm_exec_requests
WHERE session_id = <spid of the rollback process>

Also we can monitor the progress of backups/restores/etc.

Of course, we can't see the execution progress of queries (such as SELECT/INSERT/UPDATE/etc).