Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
espanolanthony
Starting Member
9 Posts |
Posted - 2011-04-07 : 10:07:56
|
I have a script that does give you the information of a Backup\Restore and tell the percentage completed or percentage remaining. Now i want to create a job and would like to run this every 5 minutes while my Restore job is running. Since the restoring database is very big (1 TB) i need to see the percentage of restore completed. and i go to the query analyser after every 20 minutes and this tells me percentage completed. Does any one know how do i set up in the job step to run every 5 minutes and send me the output of the script.---------------SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)FROM sys.dm_exec_sql_text(sql_handle)))FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')so from some source i got some hint and i usedexec msdb.dbo.sp_send_dbmail@profile_name = 'SQLAdmin',@recipients = 'isantos@foo.net;',@subject = 'Percentage completed',@body = 'Some Text',@query = 'SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)FROM sys.dm_exec_sql_text(sql_handle)))FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')',@execute_query_database = 'db',@exclude_query_output = 1, @append_query_error = 1;any idea or can someone post the exact query as now i have the Profile name as well as email too. |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2011-04-07 : 10:26:20
|
to know the backup and restore percentage, no need to go with that big query,select percent_complete from sys.dm_exec_requests where command like '%backup%'select percent_complete from sys.dm_exec_requests where command like '%restore%'you can know with these queries.and regarding sending email, you can configure dbmail. then create a job with the above code, and work around with the DBmail. you can achieve it easily.ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
espanolanthony
Starting Member
9 Posts |
Posted - 2011-04-07 : 10:38:09
|
thanks but when i do what you say and run the job it emails me the details but not the output of the queryJOB RUN: 'Testing' was run on 4/7/2011 at 10:36:23 AMDURATION: 0 hours, 0 minutes, 0 secondsSTATUS: SucceededMESSAGES: The job succeeded. The Job was invoked by User IBM-1\Admin. The last step to run was step 1 (sds). |
|
|
WWRDBA2
Starting Member
2 Posts |
Posted - 2014-04-07 : 13:50:11
|
I have been using the query for a few weeks, but it doesn't seem to be giving me accurate stats if I use LiteSpeed for backup/restore.Is this because I am not running a native backup/restore?What is happening is the query will reach 100% completion, butmy job step still takes I/O for several minutes afterward.Thanks in advance. |
|
|
|
|
|
|
|