Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Backup\ Restore completion Status. Expand / Collapse
Author
Message
Posted Tuesday, May 18, 2010 9:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:46 PM
Points: 1,380, Visits: 2,684
Thanks Steve for changing the original question. I use this all the time to figure out how far along a backup or restore is.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Post #923669
Posted Tuesday, May 18, 2010 12:53 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638
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


I think had the same confusion. I read the question as to check the completion status of the backup after it had completed. Not as an ongoing monitoring of the progress of the task.
Post #923839
Posted Wednesday, May 19, 2010 12:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, Visits: 373
Yes, it is good question but where and how we can use this? Anybody can explain with an example?

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
Post #924071
Posted Wednesday, May 19, 2010 1:48 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Kari Suresh (5/19/2010)
where and how we can use this? Anybody can explain with an example?

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).
Post #924101
Posted Wednesday, May 19, 2010 11:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
Good Question :)
Post #924816
Posted Wednesday, May 19, 2010 11:06 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
Thanks vk-kirov
For the Example
Post #924818
Posted Thursday, May 20, 2010 10:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 21,755, Visits: 15,457
Nice question.

Thanks




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #925375
Posted Thursday, May 20, 2010 11:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 858, Visits: 1,387
Thanks Jason and everyone for putting up your valuable suggestion and comments. Many Thanx. Might be the question looked a little confusing due to the keywords which I used (E.g, table in place DMV), well, I am pretty good to see our experts are not only experts infact they are indeed a good MENTOR.

I am pleased to see that my question has been published.

Moreover, your postive feedback always help me to put up new questions and make me encourage to clarify the doubts of the other users.

Thanks.. Thanks & Thanks@!!


Thanks.
Post #925403
Posted Thursday, May 20, 2010 10:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:48 AM
Points: 3,241, Visits: 5,002
vk-kirov (5/19/2010)
Kari Suresh (5/19/2010)
where and how we can use this? Anybody can explain with an example?

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).



Very Nice explanation. Answered alot of questions and confusions.
And Very informative QOTD Saurov. Thanks.



----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #925660
Posted Sunday, October 17, 2010 3:21 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 8,835, Visits: 9,393
Nice question (I saw it after the change from "table"). Nice example from vk_kirov made it clear how it can be used.

Tom
Post #1005921
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse