maintenance plan wizard: job

Showing posts with label job. Show all posts

Monday, March 19, 2012

Make SQL Server Agent job stop after X minutes

I swear there is a way in SQL 2000 to have a SQL Server Agent job (that runs
a DTS) to stop running if it's been running for X minutes. I can't find it
though. I thought it was just a checkbox and a field for a number somewhere.
any ideas?
Rich
sp_trace_setstatus @.traceid = @.TraceID, @.status = 0
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C99F8FAF-A4AE-40B5-B12E-B380E258A127@.microsoft.com...
>I swear there is a way in SQL 2000 to have a SQL Server Agent job (that
>runs
> a DTS) to stop running if it's been running for X minutes. I can't find
> it
> though. I thought it was just a checkbox and a field for a number
> somewhere.
> any ideas?

Make SQL Server Agent job stop after X minutes

I swear there is a way in SQL 2000 to have a SQL Server Agent job (that runs
a DTS) to stop running if it's been running for X minutes. I can't find it
though. I thought it was just a checkbox and a field for a number somewhere.
any ideas?Rich
sp_trace_setstatus @.traceid = @.TraceID, @.status = 0
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C99F8FAF-A4AE-40B5-B12E-B380E258A127@.microsoft.com...
>I swear there is a way in SQL 2000 to have a SQL Server Agent job (that
>runs
> a DTS) to stop running if it's been running for X minutes. I can't find
> it
> though. I thought it was just a checkbox and a field for a number
> somewhere.
> any ideas?

Monday, March 12, 2012

Make a Job continue after Primary Key Violation?

i have a stored procedure that processes new incoming data. during this
procedure i have many INSERT commands that may result with a duplicate key
violation.
this kind of error should not stop the process and the algorithm continue.
when i run it manually ( EXEC SP1 ...) it behaves ok and continue even when
primary key violation occur.
BUT, when i run it via a job (single step) it quits on the 1st error!!!
WHY ?
how can i force the job/SP to continue running after data errors like key
violations '
thanks
rafiThere are errors in sql that are not trappable. Erland has some good writing
that you should take a look:
http://www.sommarskog.se/error-handling-I.html
-oj
"Rafi" <Rafi@.discussions.microsoft.com> wrote in message
news:93C834D3-71DA-48AF-B2C6-2151EC06F136@.microsoft.com...
>i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even
> when
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi|||Hi
Why not add a where clause to the insert statement to check the PK value
does not exist? If they do exist you may want to log the fact.
It may not a good idea to SET XACT_ABORT OFF as you may miss something that
is important.
John
"Rafi" wrote:

> i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even whe
n
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi

Friday, March 9, 2012

Maitenance Job

In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
BAK files are delted that are older than this, however the TRN backups are
not deleted. Why is this and how can I resolve it?Hi ,
Typically, the size of the transaction log file stabilizes when it can hold
the maximum number of transactions that can occur between transaction log
truncations that either checkpoints or transaction log backups trigger.
However, in some situations the transaction log may become very large and
run out of space or become full.
For more information, please refer to:
317375 A transaction log grows unexpectedly or becomes full on a computer
that is running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Maitenance Job
>>thread-index: AcZn26C5VxQ5EMvgQt6cXAn7hAS36A==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>Subject: Maitenance Job
>>Date: Mon, 24 Apr 2006 13:14:01 -0700
>>Lines: 3
>>Message-ID: <E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:429048
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>In my Maintenance job I specify to keep the BAK and TRN backups for 1
week.
>>BAK files are delted that are older than this, however the TRN backups
are
>>not deleted. Why is this and how can I resolve it?|||Did you include databases in simple recovery model in the maint plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||what do you mean by simple recovery model
"Tibor Karaszi" wrote:
> Did you include databases in simple recovery model in the maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Schneider" <georgedschneider@.news.postalias> wrote in message
> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> > In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> > BAK files are delted that are older than this, however the TRN backups are
> > not deleted. Why is this and how can I resolve it?
>
>|||The tranactions logs are only like 84 kb when they are backuped up.
"George Schneider" wrote:
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||This is very well documented in Books Online (BOL), and I suggest you take the time to learn those
concepts:
BOL 2000, search for "Selecting a Recovery Model"
BOL 2005, search for "Choosing the Recovery Model for a Database"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:1EE7026E-7AB5-41E7-B5E6-F474B3B6A9E0@.microsoft.com...
> what do you mean by simple recovery model
> "Tibor Karaszi" wrote:
>> Did you include databases in simple recovery model in the maint plan?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "George Schneider" <georgedschneider@.news.postalias> wrote in message
>> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
>> > In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
>> > BAK files are delted that are older than this, however the TRN backups are
>> > not deleted. Why is this and how can I resolve it?
>>|||Hi George ,
How is everything going? Let me know if you still have concerns.
Have a good day.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Maitenance Job
>>thread-index: AcZoZcp6c/Q/UTZhQfOLwJIjZnhIqA==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>References: <E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com>
>>Subject: RE: Maitenance Job
>>Date: Tue, 25 Apr 2006 05:43:02 -0700
>>Lines: 7
>>Message-ID: <C88CCAF2-A7E1-409B-96BA-11C7F6B060F0@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:429121
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>The tranactions logs are only like 84 kb when they are backuped up.
>>"George Schneider" wrote:
>> In my Maintenance job I specify to keep the BAK and TRN backups for 1
week.
>> BAK files are delted that are older than this, however the TRN backups
are
>> not deleted. Why is this and how can I resolve it?

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.
Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:

>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.
|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
to[vbcol=seagreen]
also[vbcol=seagreen]
that
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
> >Quick question, I have a maintinence job that optimizes (reorganizes back
to
> >original size my databases back to the original amount of free space). I
also
> >have jobs that defrag specific tables.
> >
> >Will the maintinence job defrag as well as reorgainize.
> >
> >I would like to get rid of my defrag jobs and have one maintinence job
that
> >does both.
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I als
o
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:

>Quick question, I have a maintinence job that optimizes (reorganizes back t
o
>original size my databases back to the original amount of free space). I al
so
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.
4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
>
to[vbcol=seagreen]
also[vbcol=seagreen]
that[vbcol=seagreen]
>

Wednesday, March 7, 2012

Maintenance Plans on MSDE

Hi NG,
I've defined a job and a maintenance plan on my SQL-Server. The target
system is using MSDE. I could see that the job was created, but with the
Enterprise Manager I cannot see that there is a folder 'Database Maintenance
Plans' under folder 'Management'.
Does anybody know if Maintenance plans are generally possible under MSDE, or
is there something missing on my target system where MSDE is installed.
Thanks for support, rene
hi Rene,
Re Fo wrote:
> Hi NG,
> I've defined a job and a maintenance plan on my SQL-Server. The target
> system is using MSDE. I could see that the job was created, but with
> the Enterprise Manager I cannot see that there is a folder 'Database
> Maintenance Plans' under folder 'Management'.
> Does anybody know if Maintenance plans are generally possible under
> MSDE, or is there something missing on my target system where MSDE is
> installed.
> Thanks for support, rene
Maintenance Plans are supported, but you do not have a graphical tool to
manage them, and have to rely on xp_sqlmaint system extended stored
procedure
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_xp_aa-sz_0mb8.asp)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Maintenance Plans Failing

We are still having issues running maintenance plans on a server since
joining a large domain. Error message for the backup job follows:
The job failed. Unable to determine if the owner (DomainName\sa-stpisap) of
job DB Backup Job for DB Maintenance Plan 'DailyMaintenancePlan' has server
access (reason: Could not obtain information about Windows NT group/user
'DomainName\sa-stpisap'. [SQLSTATE 42000] (Error 8198)).
While logged in as sa-stpisap I can right click on the db and create a
backup on the C:\ drive.
Any thoughts?
Thanks,
StevenHi Steven,
http://support.microsoft.com/kb/834124/en-us
"Steven" wrote:

> We are still having issues running maintenance plans on a server since
> joining a large domain. Error message for the backup job follows:
> The job failed. Unable to determine if the owner (DomainName\sa-stpisap)
of
> job DB Backup Job for DB Maintenance Plan 'DailyMaintenancePlan' has serve
r
> access (reason: Could not obtain information about Windows NT group/user
> 'DomainName\sa-stpisap'. [SQLSTATE 42000] (Error 8198)).
> While logged in as sa-stpisap I can right click on the db and create a
> backup on the C:\ drive.
> Any thoughts?
> Thanks,
> Steven

Maintenance Plans are Disappearing...

I'm not even sure where to post this, but has anyone had a problem with maintenance plans disappearing?

I mean the SSIS package and the job just disappearing....?

I have four servers. Two dev and two prod. I created a pretty simple maintenance plan to backup databases. There are four tasks in the plan. One for each of four databases. Backing each up to a separate file, all in the same folder.

The plan gets associated with a job that has it run daily, every six hours, with no end date.

The plan runs just fine. Then this morning, on one of the servers, the plan and its job are just gone.

This is the second time this has happened. And it's not the same server that it happened on the first time.

Why would a maintenance plan just disappear?

Anyone?

We have almost the same problem, The SSIS jobs have dissapeared two times now. In our case the Job is still there generating errors since it can't find the Maintenance plans.

Does anyone know why this happens??

/Thomas

Saturday, February 25, 2012

Maintenance Plans and corresponding Jobs

SQL Server 2005: Creating a maintenance plan generates a job. Two
questions:
1. If I change the job instead of the maintenance plan, will the maintenance
plan break? And/or is there a way to get the job and maintenance plan out
of sycnh?
2. SQL Server 2000 generaetd cryptic command lines when creating maintenance
plans. Worse yet, error messages were nasty and tough to debug. Has this
been improved in 2005?
Thanks,
MarkMark,
In some ways it is better. If you make significant changes to the job and
then go and make a change in the maintenance plan some of those job changes
will be deleted.
If you add extra steps then these will all disappear. It would also depend
on the build that created the maintenance plan/job and the build that you
change them with if they were created before SP2 post builds.
Chris
"Mark" <mark@.idonotlikespam.com> wrote in message
news:uGjNQ4jnIHA.1204@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005: Creating a maintenance plan generates a job. Two
> questions:
> 1. If I change the job instead of the maintenance plan, will the
> maintenance plan break? And/or is there a way to get the job and
> maintenance plan out of sycnh?
> 2. SQL Server 2000 generaetd cryptic command lines when creating
> maintenance plans. Worse yet, error messages were nasty and tough to
> debug. Has this been improved in 2005?
> Thanks,
> Mark
>

Maintenance Plans and corresponding Jobs

SQL Server 2005: Creating a maintenance plan generates a job. Two
questions:
1. If I change the job instead of the maintenance plan, will the maintenance
plan break? And/or is there a way to get the job and maintenance plan out
of sycnh?
2. SQL Server 2000 generaetd cryptic command lines when creating maintenance
plans. Worse yet, error messages were nasty and tough to debug. Has this
been improved in 2005?
Thanks,
Mark
Mark,
In some ways it is better. If you make significant changes to the job and
then go and make a change in the maintenance plan some of those job changes
will be deleted.
If you add extra steps then these will all disappear. It would also depend
on the build that created the maintenance plan/job and the build that you
change them with if they were created before SP2 post builds.
Chris
"Mark" <mark@.idonotlikespam.com> wrote in message
news:uGjNQ4jnIHA.1204@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005: Creating a maintenance plan generates a job. Two
> questions:
> 1. If I change the job instead of the maintenance plan, will the
> maintenance plan break? And/or is there a way to get the job and
> maintenance plan out of sycnh?
> 2. SQL Server 2000 generaetd cryptic command lines when creating
> maintenance plans. Worse yet, error messages were nasty and tough to
> debug. Has this been improved in 2005?
> Thanks,
> Mark
>

Maintenance Plans and cleaning up backups in subfolders...

The maintenance job I have creates sub-folders for each database when it
backups.
The Cleanup Job is pointing to the root of this folder but it's not deleting
previous backups. It's set to delete files older than 2 days.
Does this not supported by the SQL2K5?
Should I not set it to create folders for each database backup?
Thanks in advance...With sp1, you have an option to recurse sub-folders. also, there has been lots of work on maint
plans in sp2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Artunc" <artunc_u2@.hotmail.com> wrote in message
news:60572E3A-8C37-4236-A7DE-7E16EE73292D@.microsoft.com...
> The maintenance job I have creates sub-folders for each database when it
> backups.
> The Cleanup Job is pointing to the root of this folder but it's not deleting
> previous backups. It's set to delete files older than 2 days.
> Does this not supported by the SQL2K5?
> Should I not set it to create folders for each database backup?
> Thanks in advance...|||Do you know where can I enable this option?
"Tibor Karaszi" wrote:
> With sp1, you have an option to recurse sub-folders. also, there has been lots of work on maint
> plans in sp2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Artunc" <artunc_u2@.hotmail.com> wrote in message
> news:60572E3A-8C37-4236-A7DE-7E16EE73292D@.microsoft.com...
> >
> > The maintenance job I have creates sub-folders for each database when it
> > backups.
> > The Cleanup Job is pointing to the root of this folder but it's not deleting
> > previous backups. It's set to delete files older than 2 days.
> >
> > Does this not supported by the SQL2K5?
> > Should I not set it to create folders for each database backup?
> >
> > Thanks in advance...
>|||It is in the cleanup task. You have to open it in the maintenance plan
editor and you will see it.
--
Andrew J. Kelly SQL MVP
"Artunc" <artunc_u2@.hotmail.com> wrote in message
news:201DF41C-9E0C-44A2-9582-332E33E25A09@.microsoft.com...
> Do you know where can I enable this option?
> "Tibor Karaszi" wrote:
>> With sp1, you have an option to recurse sub-folders. also, there has been
>> lots of work on maint
>> plans in sp2.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Artunc" <artunc_u2@.hotmail.com> wrote in message
>> news:60572E3A-8C37-4236-A7DE-7E16EE73292D@.microsoft.com...
>> >
>> > The maintenance job I have creates sub-folders for each database when
>> > it
>> > backups.
>> > The Cleanup Job is pointing to the root of this folder but it's not
>> > deleting
>> > previous backups. It's set to delete files older than 2 days.
>> >
>> > Does this not supported by the SQL2K5?
>> > Should I not set it to create folders for each database backup?
>> >
>> > Thanks in advance...
>>|||I don't see it in there, I'm running SP1.
Do I need to install SP2 to see it?
"Andrew J. Kelly" wrote:
> It is in the cleanup task. You have to open it in the maintenance plan
> editor and you will see it.
> --
> Andrew J. Kelly SQL MVP
> "Artunc" <artunc_u2@.hotmail.com> wrote in message
> news:201DF41C-9E0C-44A2-9582-332E33E25A09@.microsoft.com...
> >
> > Do you know where can I enable this option?
> >
> > "Tibor Karaszi" wrote:
> >
> >> With sp1, you have an option to recurse sub-folders. also, there has been
> >> lots of work on maint
> >> plans in sp2.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Artunc" <artunc_u2@.hotmail.com> wrote in message
> >> news:60572E3A-8C37-4236-A7DE-7E16EE73292D@.microsoft.com...
> >> >
> >> > The maintenance job I have creates sub-folders for each database when
> >> > it
> >> > backups.
> >> > The Cleanup Job is pointing to the root of this folder but it's not
> >> > deleting
> >> > previous backups. It's set to delete files older than 2 days.
> >> >
> >> > Does this not supported by the SQL2K5?
> >> > Should I not set it to create folders for each database backup?
> >> >
> >> > Thanks in advance...
> >>
> >>
>
>|||Never mind, I see it now.
I had to open the Management Studio localy on the SQL Server.
For some reason, I don't see this option when I look at it remotely from my
workstation.
Thanks everyone...
"Artunc" wrote:
> I don't see it in there, I'm running SP1.
> Do I need to install SP2 to see it?
> "Andrew J. Kelly" wrote:
> > It is in the cleanup task. You have to open it in the maintenance plan
> > editor and you will see it.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Artunc" <artunc_u2@.hotmail.com> wrote in message
> > news:201DF41C-9E0C-44A2-9582-332E33E25A09@.microsoft.com...
> > >
> > > Do you know where can I enable this option?
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> With sp1, you have an option to recurse sub-folders. also, there has been
> > >> lots of work on maint
> > >> plans in sp2.
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://sqlblog.com/blogs/tibor_karaszi
> > >>
> > >>
> > >> "Artunc" <artunc_u2@.hotmail.com> wrote in message
> > >> news:60572E3A-8C37-4236-A7DE-7E16EE73292D@.microsoft.com...
> > >> >
> > >> > The maintenance job I have creates sub-folders for each database when
> > >> > it
> > >> > backups.
> > >> > The Cleanup Job is pointing to the root of this folder but it's not
> > >> > deleting
> > >> > previous backups. It's set to delete files older than 2 days.
> > >> >
> > >> > Does this not supported by the SQL2K5?
> > >> > Should I not set it to create folders for each database backup?
> > >> >
> > >> > Thanks in advance...
> > >>
> > >>
> >
> >
> >

Monday, March 19, 2012

Make SQL Server Agent job stop after X minutes

Make SQL Server Agent job stop after X minutes

Monday, March 12, 2012

Make a Job continue after Primary Key Violation?

Friday, March 9, 2012

Maitenance Job

Maitenance Job

Maintinence Jobs vs Defag Jobs

Maintinence Jobs vs Defag Jobs

Maintinence Jobs vs Defag Jobs

Wednesday, March 7, 2012

Maintenance Plans on MSDE

Maintenance Plans Failing

Maintenance Plans are Disappearing...

Saturday, February 25, 2012

Maintenance Plans and corresponding Jobs

Maintenance Plans and corresponding Jobs

Maintenance Plans and cleaning up backups in subfolders...

Maintenance Plans and cleaning up backups in subfolders...

Maintenance Plans and cleaning up backups in subfolders...

maintenance plan wizard

Blog Archive

About Me