February 21, 2012 at 10:48 am
I have created a Maintenance plan to index some tables, takes about 15 minutes to run. After reloading tables in a large job i want to run the same plan as a step. It takes 0.0 seconds to "run" according to the history. My assumption is the job just starts the plan and moves on.
I have seen the same result either by running the package through the store and starting it via sp_maintplan_start.
Is there a way to start the plan and "wait" for completion?
worse case I am just going to script out the plan
February 21, 2012 at 11:17 am
GiantBenMav (2/21/2012)
I have created a Maintenance plan to index some tables, takes about 15 minutes to run. After reloading tables in a large job i want to run the same plan as a step. It takes 0.0 seconds to "run" according to the history. My assumption is the job just starts the plan and moves on.I have seen the same result either by running the package through the store and starting it via sp_maintplan_start.
Is there a way to start the plan and "wait" for completion?
worse case I am just going to script out the plan
I don't know what that means. What do you mean to "index some tables"? Why do you assume the job just starts and moves on? Have you looked at the job history? The agent's activity monitor? I also don't understand why you are "indexing", then loading data, then "indexing". And this is all in an SSIS task created using the Maintenance Plan?
Jared
CE - Microsoft
February 21, 2012 at 11:25 am
I want to index the tables I am loading data into before they are used later in the job. When i run the maintenance plan as a step in the job it takes 0 seconds to run.
I have seen similar behavior when starting a job from one server on another, it just starts the step and moves on. When I run the TSQL statement to start the maintenance plan, all that is returned is a message saying the plan has started. There is no notification of the job finishing. I know it runs, because the indexes are rebuilt successfully.
February 21, 2012 at 11:34 am
GiantBenMav (2/21/2012)
I want to index the tables I am loading data into before they are used later in the job. When i run the maintenance plan as a step in the job it takes 0 seconds to run.I have seen similar behavior when starting a job from one server on another, it just starts the step and moves on. When I run the TSQL statement to start the maintenance plan, all that is returned is a message saying the plan has started. There is no notification of the job finishing. I know it runs, because the indexes are rebuilt successfully.
I still don't understand. Do these tables exist before the job? What EXACTLY are you doing that you think is causing a problem? All I can gather from your statements so far is that you:
1. Create a maintenance plan that creates indexes on tables that currently have none.
That' it. How do you know it took 0 seconds to run? You mean the statement that started the step in the query editor? Maybe you are seeing the execution time for the statement you ran to kick off the job. That will not continue to keep tabs on how long it takes the job to run. Try right-clicking on the job instead of scripting it. Then run it from step one. That will keep a window open until it is complete. Otherwise, you can simply look at the job history to see the duration of the job.
Jared
CE - Microsoft
February 21, 2012 at 11:48 am
I know the index job takes 14 minutes to run; but when started from another job it takes 0 seconds (per the history).
February 21, 2012 at 11:51 am
GiantBenMav (2/21/2012)
I know the index job takes 14 minutes to run; but when started from another job it takes 0 seconds (per the history).
You're still not answering my questions. Give me a set up. These tables exist before the job is run? If they do, don't they already have the indexes on them? Are they empty? If they have data, are you dropping the indexes and then recreating them? What are you doing?
Jared
CE - Microsoft
February 21, 2012 at 12:09 pm
All that is secondary. Really it comes down to:
1. I have a maintenance plan job
2. I want to start that job in another job
3. I do not want to move to the next step (in the second job) until the plan has finished running.
4. Similar application could be starting a job on another server (with some tweaking)
Thanks
February 21, 2012 at 12:19 pm
GiantBenMav (2/21/2012)
All that is secondary. Really it comes down to:1. I have a maintenance plan job
2. I want to start that job in another job
3. I do not want to move to the next step (in the second job) until the plan has finished running.
4. Similar application could be starting a job on another server (with some tweaking)
Thanks
Ok, so basically what is happening is that the parent job is kicking off a child job as a step. However, the query to kick off the job only does just that, it does not wait for the job to finish. The statement is complete when it simply starts the job. So, why not merger the 2 jobs into 1. Don't have a job start a job, a job contains steps that are dependent on each other.
Outside of that, you could query directly after the start job script to determine if the job is done, if it is not... Wait 30 seconds and try again. Basically, keep looping in that last section of script until the job is complete. Then complete the script and move to the next step. Do you know where to query to see if the job has completed or is still running?
Jared
CE - Microsoft
February 21, 2012 at 1:49 pm
This will start the maintenance plan and monitor for the job completion.
Declare @JobStatus int
Declare @JobID nvarchar(150)
Declare @PlanID nvarchar(150)
--This is the Job you want to start
Set @JobID = '####' --Job ID
Set @PlanID = '{####}' --Maintenance Plan ID
Set @JobStatus = 1
--Execute the Maintenance Plan
EXECUTE msdb..sp_maintplan_start @PlanID,NULL
create table #job ( Job_ID uniqueidentifier, Last_Run_Date int, Last_Run_Time int, Next_Run_Date int, Next_Run_Time int, Next_Run_Schedule_ID int, Requested_To_Run int, Request_Source int, Request_Source_ID varchar(100), Running int, Current_Step int, Current_Retry_Attempt int, State int )
--Check every 5 seconds to see if the maintenance plan has finished processing
WHILE ISNULL(@JobStatus, 0) <> 0 BEGIN
WAITFOR DELAY '00:00:5';
Truncate Table #Job
insert into #job
exec master.dbo.xp_sqlagent_enum_jobs 1,sa
Set @JobStatus = (select Running from #job where Job_ID = @JobID)
--Select @JobStatus
--select * from #job where Job_ID = @JobID
END
--Output the completion time
Select GetDate() as JobCompletionTime
drop table #job
February 21, 2012 at 2:53 pm
Perhaps something like this?
http://www.sqlservercentral.com/scripts/Agent/86910/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply