how do i stop a maintenance plan?

  • Follow


I have a maintenance plan that runs every night.  Sometimes it takes too long to run and it affects production for the 
following morning.  I can see and kill the process it is currently running but then the next task starts and i have to 
kill that too.  How can i stop the thing once it has started?

thanks

sqlserver 2005
1
Reply Dan 3/11/2010 2:38:28 PM

Manually, or automatically?

SSMS has a right-click menu on the job, but that is manual.

-- 
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Dan Holmes" <dan.holmes@routematch.com> wrote in message 
news:%23%236qEiSwKHA.5812@TK2MSFTNGP02.phx.gbl...
>I have a maintenance plan that runs every night.  Sometimes it takes too 
>long to run and it affects production for the following morning.  I can see 
>and kill the process it is currently running but then the next task starts 
>and i have to kill that too.  How can i stop the thing once it has started?
>
> thanks
>
> sqlserver 2005 


0
Reply Jay 3/11/2010 2:53:17 PM


Dan,

Does this happen on certain days of the week? If you knew what caused it to 
run longer you could schedule differently.

Chris

"Dan Holmes" <dan.holmes@routematch.com> wrote in message 
news:%23%236qEiSwKHA.5812@TK2MSFTNGP02.phx.gbl...
>I have a maintenance plan that runs every night.  Sometimes it takes too 
>long to run and it affects production for the following morning.  I can see 
>and kill the process it is currently running but then the next task starts 
>and i have to kill that too.  How can i stop the thing once it has started?
>
> thanks
>
> sqlserver 2005 


0
Reply Chris 3/11/2010 6:50:45 PM

Dan,

This is a bit of a sledge hammer but....

You could also get a list of running jobs at 6:00 AM (pick your target
time here), if any of the jobs are the ones created by the maint plan,
then execute a sp_stop_job against it.  It will finish up the last
piece it was working on but then should die.  if you had to you could
just execute a sp_stop_job for each of the jobs in the maint plan.
Not sure if it will fail or jot if the job is not running.  Test test
test!

http://msdn.microsoft.com/en-us/library/aa260308%28SQL.80%29.aspx

my 2 cents!

David Hay

On Mar 11, 9:38=A0am, Dan Holmes <dan.hol...@routematch.com> wrote:
> I have a maintenance plan that runs every night. =A0Sometimes it takes to=
o long to run and it affects production for the
> following morning. =A0I can see and kill the process it is currently runn=
ing but then the next task starts and i have to
> kill that too. =A0How can i stop the thing once it has started?
>
> thanks
>
> sqlserver 2005

0
Reply David 3/11/2010 9:13:12 PM

Dan Holmes (dan.holmes@routematch.com) writes:
> I have a maintenance plan that runs every night.  Sometimes it takes too
> long to run and it affects production for the following morning.  I can
> see and kill the process it is currently running but then the next task
> starts and i have to kill that too.  How can i stop the thing once it
> has started? 
 
Maybe you should review what the Mgmt Plans are doing altogether? Are
you rebuilding too many indexes? You are not shrinking databases, I hope?

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Reply Erland 3/11/2010 10:28:35 PM

> Maybe you should review what the Mgmt Plans are doing altogether? Are
> you rebuilding too many indexes? You are not shrinking databases, I hope?

+1

-- 
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D38EED0A5ADEYazorman@127.0.0.1...
> Dan Holmes (dan.holmes@routematch.com) writes:
>> I have a maintenance plan that runs every night.  Sometimes it takes too
>> long to run and it affects production for the following morning.  I can
>> see and kill the process it is currently running but then the next task
>> starts and i have to kill that too.  How can i stop the thing once it
>> has started?
>
> Maybe you should review what the Mgmt Plans are doing altogether? Are
> you rebuilding too many indexes? You are not shrinking databases, I hope?
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Reply Jay 3/11/2010 11:00:36 PM

+1

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Jay Konigsberg" <spam@nospam.org> wrote in message 
news:#S9Gp6WwKHA.1796@TK2MSFTNGP02.phx.gbl...
>> Maybe you should review what the Mgmt Plans are doing altogether? Are
>> you rebuilding too many indexes? You are not shrinking databases, I hope?
>
> +1
>
> -- 
> Jay Konigsberg
> SQL Server DBA in Sacramento, CA
> http://www.linkedin.com/in/jaykonigsberg
>
> Live in Sacramento, CA?
> Join the Sacramento SQL Server User Group on LinkedIn
> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>
>
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
> news:Xns9D38EED0A5ADEYazorman@127.0.0.1...
>> Dan Holmes (dan.holmes@routematch.com) writes:
>>> I have a maintenance plan that runs every night.  Sometimes it takes too
>>> long to run and it affects production for the following morning.  I can
>>> see and kill the process it is currently running but then the next task
>>> starts and i have to kill that too.  How can i stop the thing once it
>>> has started?
>>
>> Maybe you should review what the Mgmt Plans are doing altogether? Are
>> you rebuilding too many indexes? You are not shrinking databases, I hope?
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: 
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
> 
0
Reply Tibor 3/12/2010 7:52:50 AM

Agreed.  Maintenance plans should be avoided and you should control 
everything that you MIGHT want/need to do with them using TSQL scripts and 
Agent jobs.  A quick engagement with a consultant can get you where you need 
to be in short order.  Or Ola Hallengren has a WONDERFUL collection of free 
TSQL scripts that you can use to control all major maintenance activies. 
http://ola.hallengren.com/

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D38EED0A5ADEYazorman@127.0.0.1...
> Dan Holmes (dan.holmes@routematch.com) writes:
>> I have a maintenance plan that runs every night.  Sometimes it takes too
>> long to run and it affects production for the following morning.  I can
>> see and kill the process it is currently running but then the next task
>> starts and i have to kill that too.  How can i stop the thing once it
>> has started?
>
> Maybe you should review what the Mgmt Plans are doing altogether? Are
> you rebuilding too many indexes? You are not shrinking databases, I hope?
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Reply TheSQLGuru 3/12/2010 2:22:39 PM

7 Replies
4494 Views

(page loaded in 0.987 seconds)

10/19/2014 7:05:17 PM


Reply: