April 6, 2011 at 1:32 pm
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')
April 6, 2011 at 2:13 pm
Hmmmm ... off the top of my head:
1) Put the query above into a job "Restore status", using sp_send_dbmail to send you the results of the query
2) Schedule it to run every 5 minutes, but have it disabled.
3) Make the first step of your restore job enable the status job.
4) Then make the last step of your restore job disable the status job
April 6, 2011 at 2:21 pm
Actually i need the step-by-step instructions.
no idea how to do that. and the ways you says will just send me an email that the job succeeded but not the output of the job.
April 6, 2011 at 2:46 pm
Look up sp_send_dbmail. It shows examples of how you can put your query in the @QUERY variable.
"... the ways you says will just send me an email that the job succeeded but not the output of the job. ..."
No, Your "Status job" will run your query every 5 minutes between steps 4 and 5 and send you the results. Then your backup job will disable it when the restore is complete, until next time.
I was assuming your backup is run through a SQL scheduled job, so that you can add a couple of steps to it.
I haven't tested the idea, so if someone sees a problem, please reply.
April 7, 2011 at 7:44 am
hi i am using the below to send me the email but i am not able to get it instead i get an error
exec 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.
April 7, 2011 at 8:05 am
by the way the error says
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 208, Level 16, State 1, Server IBM-CORE-01, Line 1
April 7, 2011 at 9:02 am
I think you need double quotes around your literals:
FROM sys.dm_exec_requests r WHERE command IN (''RESTORE DATABASE'',''BACKUP DATABASE'')',
April 7, 2011 at 9:48 am
no did not work says
Msg 119, Level 15, State 1, Line 1
Must pass parameter number 6 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
April 7, 2011 at 10:22 am
What is @name ?
The query you posted above works for me with the double quotes.
April 7, 2011 at 11:22 am
i don't see the @name as well as i do not know what is the @name for
April 7, 2011 at 12:19 pm
Perhaps it has to do with how you are setting your parameters ?
I found this at the following link
The meaning of the error message is that if you say:
EXEC my_sp 1, 2, @p3 = 3, 4
This is illegal. Once you have started to name your parameters in the parameter list, you cannot switch back to positioned parameters.
April 8, 2011 at 3:32 pm
homebrew01 (4/7/2011)
What is @name ?The query you posted above works for me with the double quotes.
the @name = @value line is just to indicate that parameters have to be passed in name/value pairs once you began that construct.
I believe homebrew is correct - it is the single quote around the literals that's causing a termination of the query string, then the compiler picks the comma between them as the beginning of a new parameter... and THAT parameter is not a name/value pair.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply