We have two SQL 2000 servers, one runs on SP2 and another
one runs on SP3, I set up the same maintanence plans on
them and selected 'attemp to repair any minor problems'
but the job 'Integrity Check' failed at 'Check data and
index linkage' due to 'database needs to be in single user
mode' on both user and system databases. Any idea what I
did wrong? OS is Win2K SP3.
Many thanks!You didn't do anything wrong, it needs to be in single user mode to do this
and if there is any connection in the db it can't put it in single user
mode. You should uncheck this anyway. Let it report the errors and then
you decide how and when to fix them.
--
Andrew J. Kelly
SQL Server MVP
"Ray Yan" <remonyan@.hotmail.com> wrote in message
news:61d901c34107$597b1180$7d02280a@.phx.gbl...
> We have two SQL 2000 servers, one runs on SP2 and another
> one runs on SP3, I set up the same maintanence plans on
> them and selected 'attemp to repair any minor problems'
> but the job 'Integrity Check' failed at 'Check data and
> index linkage' due to 'database needs to be in single user
> mode' on both user and system databases. Any idea what I
> did wrong? OS is Win2K SP3.
> Many thanks!
Showing posts with label maintanence. Show all posts
Showing posts with label maintanence. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Maintanence Plans for Backups
Hi,
I'm about to setup several backup stategies for several different SQL
Servers. I'm planning on using Full and T-log backups for the databases
using Maintanence Plans. I've been advised not to use Differential backups
because of space requirements. Any advantage to using custom scripts to
perform backups for databases and t-logs as opposed to using the jobs
created with Maintanence Plans? I.e., any issues with using Maintanence
Plans for backups?
Thanks
Jerry
Jerry,
If you are already worried about having enough space to store your backups
and you haven't even started yet you are in trouble. Disk space is way too
cheap to be dealing with issues like that. Are you going to sacrifice your
recoverability due to lack of disk space? Not saying you absolutely need to
do differential backups or not but deciding based on disk space is not the
way to go about it. You should look into using SQL LiteSpeed for your
backups then. It can cut down the time and disk space required by as much
as 80% in some cases. Personally I don't like to use the MP as it is too
inflexible for many of my clients and when there is a problem it can be
difficult to figure out exactly what is happening.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm about to setup several backup stategies for several different SQL
> Servers. I'm planning on using Full and T-log backups for the databases
> using Maintanence Plans. I've been advised not to use Differential
> backups because of space requirements. Any advantage to using custom
> scripts to perform backups for databases and t-logs as opposed to using
> the jobs created with Maintanence Plans? I.e., any issues with using
> Maintanence Plans for backups?
> Thanks
> Jerry
>
|||Andrew,
Good points. However I can only make suggestions on HW/SW purchases not cut
the check. I agree we do need more disk space available for local backups
as well as remote storage prior to copying to tape for offsite storage. I'm
currently analyzing our current disaster recovery strategy to make
suggestions for improvement and additional hard drive space will certainly
be one of them. Most of the databases are very small for the industry so
the speed and size of the backups are not so much of an issue as the lack of
additional space is...i.e., another few drives and we should be fine.
I think MPs will work for what we are doing...just wanted to know if anyone
out there had experienced any issues with them before we move in that
direction as opposed to creating custom scripts in jobs.
Thanks for the input though.
Have a killer holiday!
J
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
> Jerry,
> If you are already worried about having enough space to store your backups
> and you haven't even started yet you are in trouble. Disk space is way
> too cheap to be dealing with issues like that. Are you going to sacrifice
> your recoverability due to lack of disk space? Not saying you absolutely
> need to do differential backups or not but deciding based on disk space is
> not the way to go about it. You should look into using SQL LiteSpeed for
> your backups then. It can cut down the time and disk space required by as
> much as 80% in some cases. Personally I don't like to use the MP as it is
> too inflexible for many of my clients and when there is a problem it can
> be difficult to figure out exactly what is happening.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>
|||Although there is a group of people who do not particularly care for Maint
plans, I think it would be fine for you to start with them...
If you have a problem ( like overlapping times between the different tasks),
things may die and it might be difficult to figure out..
However if you have overlapping tasks w/o MPs you will get the same
problem...
I would suggest you do something today ( like MPs), and then move to
something more sophistaced when/if you see any problems...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236VZvGxYFHA.3624@.tk2msftngp13.phx.gbl...
> Andrew,
> Good points. However I can only make suggestions on HW/SW purchases not
> cut the check. I agree we do need more disk space available for local
> backups as well as remote storage prior to copying to tape for offsite
> storage. I'm currently analyzing our current disaster recovery strategy
> to make suggestions for improvement and additional hard drive space will
> certainly be one of them. Most of the databases are very small for the
> industry so the speed and size of the backups are not so much of an issue
> as the lack of additional space is...i.e., another few drives and we
> should be fine.
> I think MPs will work for what we are doing...just wanted to know if
> anyone out there had experienced any issues with them before we move in
> that direction as opposed to creating custom scripts in jobs.
> Thanks for the input though.
> Have a killer holiday!
> J
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
>
I'm about to setup several backup stategies for several different SQL
Servers. I'm planning on using Full and T-log backups for the databases
using Maintanence Plans. I've been advised not to use Differential backups
because of space requirements. Any advantage to using custom scripts to
perform backups for databases and t-logs as opposed to using the jobs
created with Maintanence Plans? I.e., any issues with using Maintanence
Plans for backups?
Thanks
Jerry
Jerry,
If you are already worried about having enough space to store your backups
and you haven't even started yet you are in trouble. Disk space is way too
cheap to be dealing with issues like that. Are you going to sacrifice your
recoverability due to lack of disk space? Not saying you absolutely need to
do differential backups or not but deciding based on disk space is not the
way to go about it. You should look into using SQL LiteSpeed for your
backups then. It can cut down the time and disk space required by as much
as 80% in some cases. Personally I don't like to use the MP as it is too
inflexible for many of my clients and when there is a problem it can be
difficult to figure out exactly what is happening.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm about to setup several backup stategies for several different SQL
> Servers. I'm planning on using Full and T-log backups for the databases
> using Maintanence Plans. I've been advised not to use Differential
> backups because of space requirements. Any advantage to using custom
> scripts to perform backups for databases and t-logs as opposed to using
> the jobs created with Maintanence Plans? I.e., any issues with using
> Maintanence Plans for backups?
> Thanks
> Jerry
>
|||Andrew,
Good points. However I can only make suggestions on HW/SW purchases not cut
the check. I agree we do need more disk space available for local backups
as well as remote storage prior to copying to tape for offsite storage. I'm
currently analyzing our current disaster recovery strategy to make
suggestions for improvement and additional hard drive space will certainly
be one of them. Most of the databases are very small for the industry so
the speed and size of the backups are not so much of an issue as the lack of
additional space is...i.e., another few drives and we should be fine.
I think MPs will work for what we are doing...just wanted to know if anyone
out there had experienced any issues with them before we move in that
direction as opposed to creating custom scripts in jobs.
Thanks for the input though.
Have a killer holiday!
J
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
> Jerry,
> If you are already worried about having enough space to store your backups
> and you haven't even started yet you are in trouble. Disk space is way
> too cheap to be dealing with issues like that. Are you going to sacrifice
> your recoverability due to lack of disk space? Not saying you absolutely
> need to do differential backups or not but deciding based on disk space is
> not the way to go about it. You should look into using SQL LiteSpeed for
> your backups then. It can cut down the time and disk space required by as
> much as 80% in some cases. Personally I don't like to use the MP as it is
> too inflexible for many of my clients and when there is a problem it can
> be difficult to figure out exactly what is happening.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>
|||Although there is a group of people who do not particularly care for Maint
plans, I think it would be fine for you to start with them...
If you have a problem ( like overlapping times between the different tasks),
things may die and it might be difficult to figure out..
However if you have overlapping tasks w/o MPs you will get the same
problem...
I would suggest you do something today ( like MPs), and then move to
something more sophistaced when/if you see any problems...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236VZvGxYFHA.3624@.tk2msftngp13.phx.gbl...
> Andrew,
> Good points. However I can only make suggestions on HW/SW purchases not
> cut the check. I agree we do need more disk space available for local
> backups as well as remote storage prior to copying to tape for offsite
> storage. I'm currently analyzing our current disaster recovery strategy
> to make suggestions for improvement and additional hard drive space will
> certainly be one of them. Most of the databases are very small for the
> industry so the speed and size of the backups are not so much of an issue
> as the lack of additional space is...i.e., another few drives and we
> should be fine.
> I think MPs will work for what we are doing...just wanted to know if
> anyone out there had experienced any issues with them before we move in
> that direction as opposed to creating custom scripts in jobs.
> Thanks for the input though.
> Have a killer holiday!
> J
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
>
Maintanence Plans for Backups
Hi,
I'm about to setup several backup stategies for several different SQL
Servers. I'm planning on using Full and T-log backups for the databases
using Maintanence Plans. I've been advised not to use Differential backups
because of space requirements. Any advantage to using custom scripts to
perform backups for databases and t-logs as opposed to using the jobs
created with Maintanence Plans? I.e., any issues with using Maintanence
Plans for backups?
Thanks
JerryJerry,
If you are already worried about having enough space to store your backups
and you haven't even started yet you are in trouble. Disk space is way too
cheap to be dealing with issues like that. Are you going to sacrifice your
recoverability due to lack of disk space? Not saying you absolutely need to
do differential backups or not but deciding based on disk space is not the
way to go about it. You should look into using SQL LiteSpeed for your
backups then. It can cut down the time and disk space required by as much
as 80% in some cases. Personally I don't like to use the MP as it is too
inflexible for many of my clients and when there is a problem it can be
difficult to figure out exactly what is happening.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm about to setup several backup stategies for several different SQL
> Servers. I'm planning on using Full and T-log backups for the databases
> using Maintanence Plans. I've been advised not to use Differential
> backups because of space requirements. Any advantage to using custom
> scripts to perform backups for databases and t-logs as opposed to using
> the jobs created with Maintanence Plans? I.e., any issues with using
> Maintanence Plans for backups?
> Thanks
> Jerry
>|||Andrew,
Good points. However I can only make suggestions on HW/SW purchases not cut
the check. I agree we do need more disk space available for local backups
as well as remote storage prior to copying to tape for offsite storage. I'm
currently analyzing our current disaster recovery strategy to make
suggestions for improvement and additional hard drive space will certainly
be one of them. Most of the databases are very small for the industry so
the speed and size of the backups are not so much of an issue as the lack of
additional space is...i.e., another few drives and we should be fine.
I think MPs will work for what we are doing...just wanted to know if anyone
out there had experienced any issues with them before we move in that
direction as opposed to creating custom scripts in jobs.
Thanks for the input though.
Have a killer holiday!
J
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
> Jerry,
> If you are already worried about having enough space to store your backups
> and you haven't even started yet you are in trouble. Disk space is way
> too cheap to be dealing with issues like that. Are you going to sacrifice
> your recoverability due to lack of disk space? Not saying you absolutely
> need to do differential backups or not but deciding based on disk space is
> not the way to go about it. You should look into using SQL LiteSpeed for
> your backups then. It can cut down the time and disk space required by as
> much as 80% in some cases. Personally I don't like to use the MP as it is
> too inflexible for many of my clients and when there is a problem it can
> be difficult to figure out exactly what is happening.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> I'm about to setup several backup stategies for several different SQL
>> Servers. I'm planning on using Full and T-log backups for the databases
>> using Maintanence Plans. I've been advised not to use Differential
>> backups because of space requirements. Any advantage to using custom
>> scripts to perform backups for databases and t-logs as opposed to using
>> the jobs created with Maintanence Plans? I.e., any issues with using
>> Maintanence Plans for backups?
>> Thanks
>> Jerry
>|||Although there is a group of people who do not particularly care for Maint
plans, I think it would be fine for you to start with them...
If you have a problem ( like overlapping times between the different tasks),
things may die and it might be difficult to figure out..
However if you have overlapping tasks w/o MPs you will get the same
problem...
I would suggest you do something today ( like MPs), and then move to
something more sophistaced when/if you see any problems...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236VZvGxYFHA.3624@.tk2msftngp13.phx.gbl...
> Andrew,
> Good points. However I can only make suggestions on HW/SW purchases not
> cut the check. I agree we do need more disk space available for local
> backups as well as remote storage prior to copying to tape for offsite
> storage. I'm currently analyzing our current disaster recovery strategy
> to make suggestions for improvement and additional hard drive space will
> certainly be one of them. Most of the databases are very small for the
> industry so the speed and size of the backups are not so much of an issue
> as the lack of additional space is...i.e., another few drives and we
> should be fine.
> I think MPs will work for what we are doing...just wanted to know if
> anyone out there had experienced any issues with them before we move in
> that direction as opposed to creating custom scripts in jobs.
> Thanks for the input though.
> Have a killer holiday!
> J
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
>> Jerry,
>> If you are already worried about having enough space to store your
>> backups and you haven't even started yet you are in trouble. Disk space
>> is way too cheap to be dealing with issues like that. Are you going to
>> sacrifice your recoverability due to lack of disk space? Not saying you
>> absolutely need to do differential backups or not but deciding based on
>> disk space is not the way to go about it. You should look into using SQL
>> LiteSpeed for your backups then. It can cut down the time and disk space
>> required by as much as 80% in some cases. Personally I don't like to use
>> the MP as it is too inflexible for many of my clients and when there is a
>> problem it can be difficult to figure out exactly what is happening.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> I'm about to setup several backup stategies for several different SQL
>> Servers. I'm planning on using Full and T-log backups for the databases
>> using Maintanence Plans. I've been advised not to use Differential
>> backups because of space requirements. Any advantage to using custom
>> scripts to perform backups for databases and t-logs as opposed to using
>> the jobs created with Maintanence Plans? I.e., any issues with using
>> Maintanence Plans for backups?
>> Thanks
>> Jerry
>>
>
I'm about to setup several backup stategies for several different SQL
Servers. I'm planning on using Full and T-log backups for the databases
using Maintanence Plans. I've been advised not to use Differential backups
because of space requirements. Any advantage to using custom scripts to
perform backups for databases and t-logs as opposed to using the jobs
created with Maintanence Plans? I.e., any issues with using Maintanence
Plans for backups?
Thanks
JerryJerry,
If you are already worried about having enough space to store your backups
and you haven't even started yet you are in trouble. Disk space is way too
cheap to be dealing with issues like that. Are you going to sacrifice your
recoverability due to lack of disk space? Not saying you absolutely need to
do differential backups or not but deciding based on disk space is not the
way to go about it. You should look into using SQL LiteSpeed for your
backups then. It can cut down the time and disk space required by as much
as 80% in some cases. Personally I don't like to use the MP as it is too
inflexible for many of my clients and when there is a problem it can be
difficult to figure out exactly what is happening.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm about to setup several backup stategies for several different SQL
> Servers. I'm planning on using Full and T-log backups for the databases
> using Maintanence Plans. I've been advised not to use Differential
> backups because of space requirements. Any advantage to using custom
> scripts to perform backups for databases and t-logs as opposed to using
> the jobs created with Maintanence Plans? I.e., any issues with using
> Maintanence Plans for backups?
> Thanks
> Jerry
>|||Andrew,
Good points. However I can only make suggestions on HW/SW purchases not cut
the check. I agree we do need more disk space available for local backups
as well as remote storage prior to copying to tape for offsite storage. I'm
currently analyzing our current disaster recovery strategy to make
suggestions for improvement and additional hard drive space will certainly
be one of them. Most of the databases are very small for the industry so
the speed and size of the backups are not so much of an issue as the lack of
additional space is...i.e., another few drives and we should be fine.
I think MPs will work for what we are doing...just wanted to know if anyone
out there had experienced any issues with them before we move in that
direction as opposed to creating custom scripts in jobs.
Thanks for the input though.
Have a killer holiday!
J
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
> Jerry,
> If you are already worried about having enough space to store your backups
> and you haven't even started yet you are in trouble. Disk space is way
> too cheap to be dealing with issues like that. Are you going to sacrifice
> your recoverability due to lack of disk space? Not saying you absolutely
> need to do differential backups or not but deciding based on disk space is
> not the way to go about it. You should look into using SQL LiteSpeed for
> your backups then. It can cut down the time and disk space required by as
> much as 80% in some cases. Personally I don't like to use the MP as it is
> too inflexible for many of my clients and when there is a problem it can
> be difficult to figure out exactly what is happening.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> I'm about to setup several backup stategies for several different SQL
>> Servers. I'm planning on using Full and T-log backups for the databases
>> using Maintanence Plans. I've been advised not to use Differential
>> backups because of space requirements. Any advantage to using custom
>> scripts to perform backups for databases and t-logs as opposed to using
>> the jobs created with Maintanence Plans? I.e., any issues with using
>> Maintanence Plans for backups?
>> Thanks
>> Jerry
>|||Although there is a group of people who do not particularly care for Maint
plans, I think it would be fine for you to start with them...
If you have a problem ( like overlapping times between the different tasks),
things may die and it might be difficult to figure out..
However if you have overlapping tasks w/o MPs you will get the same
problem...
I would suggest you do something today ( like MPs), and then move to
something more sophistaced when/if you see any problems...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236VZvGxYFHA.3624@.tk2msftngp13.phx.gbl...
> Andrew,
> Good points. However I can only make suggestions on HW/SW purchases not
> cut the check. I agree we do need more disk space available for local
> backups as well as remote storage prior to copying to tape for offsite
> storage. I'm currently analyzing our current disaster recovery strategy
> to make suggestions for improvement and additional hard drive space will
> certainly be one of them. Most of the databases are very small for the
> industry so the speed and size of the backups are not so much of an issue
> as the lack of additional space is...i.e., another few drives and we
> should be fine.
> I think MPs will work for what we are doing...just wanted to know if
> anyone out there had experienced any issues with them before we move in
> that direction as opposed to creating custom scripts in jobs.
> Thanks for the input though.
> Have a killer holiday!
> J
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
>> Jerry,
>> If you are already worried about having enough space to store your
>> backups and you haven't even started yet you are in trouble. Disk space
>> is way too cheap to be dealing with issues like that. Are you going to
>> sacrifice your recoverability due to lack of disk space? Not saying you
>> absolutely need to do differential backups or not but deciding based on
>> disk space is not the way to go about it. You should look into using SQL
>> LiteSpeed for your backups then. It can cut down the time and disk space
>> required by as much as 80% in some cases. Personally I don't like to use
>> the MP as it is too inflexible for many of my clients and when there is a
>> problem it can be difficult to figure out exactly what is happening.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> I'm about to setup several backup stategies for several different SQL
>> Servers. I'm planning on using Full and T-log backups for the databases
>> using Maintanence Plans. I've been advised not to use Differential
>> backups because of space requirements. Any advantage to using custom
>> scripts to perform backups for databases and t-logs as opposed to using
>> the jobs created with Maintanence Plans? I.e., any issues with using
>> Maintanence Plans for backups?
>> Thanks
>> Jerry
>>
>
Maintanence Plans for Backups
Hi,
I'm about to setup several backup stategies for several different SQL
Servers. I'm planning on using Full and T-log backups for the databases
using Maintanence Plans. I've been advised not to use Differential backups
because of space requirements. Any advantage to using custom scripts to
perform backups for databases and t-logs as opposed to using the jobs
created with Maintanence Plans? I.e., any issues with using Maintanence
Plans for backups?
Thanks
JerryJerry,
If you are already worried about having enough space to store your backups
and you haven't even started yet you are in trouble. Disk space is way too
cheap to be dealing with issues like that. Are you going to sacrifice your
recoverability due to lack of disk space? Not saying you absolutely need to
do differential backups or not but deciding based on disk space is not the
way to go about it. You should look into using SQL LiteSpeed for your
backups then. It can cut down the time and disk space required by as much
as 80% in some cases. Personally I don't like to use the MP as it is too
inflexible for many of my clients and when there is a problem it can be
difficult to figure out exactly what is happening.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm about to setup several backup stategies for several different SQL
> Servers. I'm planning on using Full and T-log backups for the databases
> using Maintanence Plans. I've been advised not to use Differential
> backups because of space requirements. Any advantage to using custom
> scripts to perform backups for databases and t-logs as opposed to using
> the jobs created with Maintanence Plans? I.e., any issues with using
> Maintanence Plans for backups?
> Thanks
> Jerry
>|||Andrew,
Good points. However I can only make suggestions on HW/SW purchases not cut
the check. I agree we do need more disk space available for local backups
as well as remote storage prior to copying to tape for offsite storage. I'm
currently analyzing our current disaster recovery strategy to make
suggestions for improvement and additional hard drive space will certainly
be one of them. Most of the databases are very small for the industry so
the speed and size of the backups are not so much of an issue as the lack of
additional space is...i.e., another few drives and we should be fine.
I think MPs will work for what we are doing...just wanted to know if anyone
out there had experienced any issues with them before we move in that
direction as opposed to creating custom scripts in jobs.
Thanks for the input though.
Have a killer holiday!
J
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
> Jerry,
> If you are already worried about having enough space to store your backups
> and you haven't even started yet you are in trouble. Disk space is way
> too cheap to be dealing with issues like that. Are you going to sacrifice
> your recoverability due to lack of disk space? Not saying you absolutely
> need to do differential backups or not but deciding based on disk space is
> not the way to go about it. You should look into using SQL LiteSpeed for
> your backups then. It can cut down the time and disk space required by as
> much as 80% in some cases. Personally I don't like to use the MP as it is
> too inflexible for many of my clients and when there is a problem it can
> be difficult to figure out exactly what is happening.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>|||Although there is a group of people who do not particularly care for Maint
plans, I think it would be fine for you to start with them...
If you have a problem ( like overlapping times between the different tasks),
things may die and it might be difficult to figure out..
However if you have overlapping tasks w/o MPs you will get the same
problem...
I would suggest you do something today ( like MPs), and then move to
something more sophistaced when/if you see any problems...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236VZvGxYFHA.3624@.tk2msftngp13.phx.gbl...
> Andrew,
> Good points. However I can only make suggestions on HW/SW purchases not
> cut the check. I agree we do need more disk space available for local
> backups as well as remote storage prior to copying to tape for offsite
> storage. I'm currently analyzing our current disaster recovery strategy
> to make suggestions for improvement and additional hard drive space will
> certainly be one of them. Most of the databases are very small for the
> industry so the speed and size of the backups are not so much of an issue
> as the lack of additional space is...i.e., another few drives and we
> should be fine.
> I think MPs will work for what we are doing...just wanted to know if
> anyone out there had experienced any issues with them before we move in
> that direction as opposed to creating custom scripts in jobs.
> Thanks for the input though.
> Have a killer holiday!
> J
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
>
I'm about to setup several backup stategies for several different SQL
Servers. I'm planning on using Full and T-log backups for the databases
using Maintanence Plans. I've been advised not to use Differential backups
because of space requirements. Any advantage to using custom scripts to
perform backups for databases and t-logs as opposed to using the jobs
created with Maintanence Plans? I.e., any issues with using Maintanence
Plans for backups?
Thanks
JerryJerry,
If you are already worried about having enough space to store your backups
and you haven't even started yet you are in trouble. Disk space is way too
cheap to be dealing with issues like that. Are you going to sacrifice your
recoverability due to lack of disk space? Not saying you absolutely need to
do differential backups or not but deciding based on disk space is not the
way to go about it. You should look into using SQL LiteSpeed for your
backups then. It can cut down the time and disk space required by as much
as 80% in some cases. Personally I don't like to use the MP as it is too
inflexible for many of my clients and when there is a problem it can be
difficult to figure out exactly what is happening.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm about to setup several backup stategies for several different SQL
> Servers. I'm planning on using Full and T-log backups for the databases
> using Maintanence Plans. I've been advised not to use Differential
> backups because of space requirements. Any advantage to using custom
> scripts to perform backups for databases and t-logs as opposed to using
> the jobs created with Maintanence Plans? I.e., any issues with using
> Maintanence Plans for backups?
> Thanks
> Jerry
>|||Andrew,
Good points. However I can only make suggestions on HW/SW purchases not cut
the check. I agree we do need more disk space available for local backups
as well as remote storage prior to copying to tape for offsite storage. I'm
currently analyzing our current disaster recovery strategy to make
suggestions for improvement and additional hard drive space will certainly
be one of them. Most of the databases are very small for the industry so
the speed and size of the backups are not so much of an issue as the lack of
additional space is...i.e., another few drives and we should be fine.
I think MPs will work for what we are doing...just wanted to know if anyone
out there had experienced any issues with them before we move in that
direction as opposed to creating custom scripts in jobs.
Thanks for the input though.
Have a killer holiday!
J
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
> Jerry,
> If you are already worried about having enough space to store your backups
> and you haven't even started yet you are in trouble. Disk space is way
> too cheap to be dealing with issues like that. Are you going to sacrifice
> your recoverability due to lack of disk space? Not saying you absolutely
> need to do differential backups or not but deciding based on disk space is
> not the way to go about it. You should look into using SQL LiteSpeed for
> your backups then. It can cut down the time and disk space required by as
> much as 80% in some cases. Personally I don't like to use the MP as it is
> too inflexible for many of my clients and when there is a problem it can
> be difficult to figure out exactly what is happening.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%236dmz$vYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>|||Although there is a group of people who do not particularly care for Maint
plans, I think it would be fine for you to start with them...
If you have a problem ( like overlapping times between the different tasks),
things may die and it might be difficult to figure out..
However if you have overlapping tasks w/o MPs you will get the same
problem...
I would suggest you do something today ( like MPs), and then move to
something more sophistaced when/if you see any problems...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236VZvGxYFHA.3624@.tk2msftngp13.phx.gbl...
> Andrew,
> Good points. However I can only make suggestions on HW/SW purchases not
> cut the check. I agree we do need more disk space available for local
> backups as well as remote storage prior to copying to tape for offsite
> storage. I'm currently analyzing our current disaster recovery strategy
> to make suggestions for improvement and additional hard drive space will
> certainly be one of them. Most of the databases are very small for the
> industry so the speed and size of the backups are not so much of an issue
> as the lack of additional space is...i.e., another few drives and we
> should be fine.
> I think MPs will work for what we are doing...just wanted to know if
> anyone out there had experienced any issues with them before we move in
> that direction as opposed to creating custom scripts in jobs.
> Thanks for the input though.
> Have a killer holiday!
> J
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OD40V7wYFHA.2884@.tk2msftngp13.phx.gbl...
>
Maintanence Plans
I am having trouble with my database maintanence plan.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.sql
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.sql
Maintanence Plans
I am having trouble with my database maintanence plan.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.
Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.
Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.
Maintanence Plans
I am having trouble with my database maintanence plan.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.
maintanence plan fails - Adding job step
I am trying to create a new maint plan on a dev server and it fails to create
it. It is failing when attempting to add the job steps to the agent. I have
performed this on two other dev server without any issues. I verified all
the settings, configurations, protocols, proxies, and logins are configured
the same on all servers.
The other servers taht this is working on do not have any special proxy
settings and I tried to create a new connection and used the "local
connection" settings. None will work.
Here is the tech details from the error it is generating. Any help will be
appreciated.
===================================
Create maintenance plan failed.
===================================
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476
Program Location:
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan.AddAgentJob(ServerConnecti on localConnObj, String proxyName)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan..ctor(String
subplanName, String proxyAccount, Package package, ServerConnection
localConnObj)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlan.AddSubPlan(String subplanName, String proxyAccount)
at
Microsoft.SqlServer.Management.MaintenancePlanWiza rd.MaintenancePlanWizardForm.PerformActions()
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImplFinish(StringCollection createQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImpl()
===================================
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
For help, click:
http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476
Server Name: TST-TFS2005\TSTTFS2005,1450
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 28
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
Stan
This error generally means that SSIS is not installed on the server. This is
required for the new style maintenance plans in SQL2005. Can you confirm
whether Integration Services is installed on the server you are seeing the
error on.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Stan" <Stan@.discussions.microsoft.com> wrote in message
news:696C7781-51A5-4B4E-9595-9AA307ECA806@.microsoft.com...
>I am trying to create a new maint plan on a dev server and it fails to
>create
> it. It is failing when attempting to add the job steps to the agent. I
> have
> performed this on two other dev server without any issues. I verified all
> the settings, configurations, protocols, proxies, and logins are
> configured
> the same on all servers.
> The other servers taht this is working on do not have any special proxy
> settings and I tried to create a new connection and used the "local
> connection" settings. None will work.
> Here is the tech details from the error it is generating. Any help will
> be
> appreciated.
>
> ===================================
> Create maintenance plan failed.
> ===================================
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan.AddAgentJob(ServerConnecti on
> localConnObj, String proxyName)
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan..ctor(String
> subplanName, String proxyAccount, Package package, ServerConnection
> localConnObj)
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlan.AddSubPlan(String
> subplanName, String proxyAccount)
> at
> Microsoft.SqlServer.Management.MaintenancePlanWiza rd.MaintenancePlanWizardForm.PerformActions()
> ===================================
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection
> sqlCommands, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection
> queries)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection
> queries, Boolean includeDbContext)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImplFinish(StringCollection
> createQuery, ScriptingOptions so)
> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImpl()
> ===================================
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476
> --
> Server Name: TST-TFS2005\TSTTFS2005,1450
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 28
>
> --
> Program Location:
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
> methodName, Boolean async)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
>
> --
> Stan
it. It is failing when attempting to add the job steps to the agent. I have
performed this on two other dev server without any issues. I verified all
the settings, configurations, protocols, proxies, and logins are configured
the same on all servers.
The other servers taht this is working on do not have any special proxy
settings and I tried to create a new connection and used the "local
connection" settings. None will work.
Here is the tech details from the error it is generating. Any help will be
appreciated.
===================================
Create maintenance plan failed.
===================================
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476
Program Location:
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan.AddAgentJob(ServerConnecti on localConnObj, String proxyName)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan..ctor(String
subplanName, String proxyAccount, Package package, ServerConnection
localConnObj)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlan.AddSubPlan(String subplanName, String proxyAccount)
at
Microsoft.SqlServer.Management.MaintenancePlanWiza rd.MaintenancePlanWizardForm.PerformActions()
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImplFinish(StringCollection createQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImpl()
===================================
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
For help, click:
http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476
Server Name: TST-TFS2005\TSTTFS2005,1450
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 28
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
Stan
This error generally means that SSIS is not installed on the server. This is
required for the new style maintenance plans in SQL2005. Can you confirm
whether Integration Services is installed on the server you are seeing the
error on.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Stan" <Stan@.discussions.microsoft.com> wrote in message
news:696C7781-51A5-4B4E-9595-9AA307ECA806@.microsoft.com...
>I am trying to create a new maint plan on a dev server and it fails to
>create
> it. It is failing when attempting to add the job steps to the agent. I
> have
> performed this on two other dev server without any issues. I verified all
> the settings, configurations, protocols, proxies, and logins are
> configured
> the same on all servers.
> The other servers taht this is working on do not have any special proxy
> settings and I tried to create a new connection and used the "local
> connection" settings. None will work.
> Here is the tech details from the error it is generating. Any help will
> be
> appreciated.
>
> ===================================
> Create maintenance plan failed.
> ===================================
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan.AddAgentJob(ServerConnecti on
> localConnObj, String proxyName)
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan..ctor(String
> subplanName, String proxyAccount, Package package, ServerConnection
> localConnObj)
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlan.AddSubPlan(String
> subplanName, String proxyAccount)
> at
> Microsoft.SqlServer.Management.MaintenancePlanWiza rd.MaintenancePlanWizardForm.PerformActions()
> ===================================
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection
> sqlCommands, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection
> queries)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection
> queries, Boolean includeDbContext)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImplFinish(StringCollection
> createQuery, ScriptingOptions so)
> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImpl()
> ===================================
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476
> --
> Server Name: TST-TFS2005\TSTTFS2005,1450
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 28
>
> --
> Program Location:
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
> methodName, Boolean async)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
>
> --
> Stan
Maintanence plan challenge!
Heres a challenge...
Sudddenly our backup maintanence plan stoped working. In the job history the
following error is displayed:
The job failed. The job invoked by the user sa. The last step to run was
step 1 (Step 1).
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
The maintanence plan error log is empty.
The job does not backup transaction logs and i've deseleccted integrity
check. I've created a new database and recrated the maintanene plan on this
database with the same result. I also created a maintanence plan with only an
integrity check. Still the same result...
I′m going nuts....
Hi ,
Can you mail the syntax you are using for sqlmaint.exe along with any
switches that have been used.
Thanks & Regards
Vishal
"Stefan Andersson" wrote:
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history the
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on this
> database with the same result. I also created a maintanence plan with only an
> integrity check. Still the same result...
> I′m going nuts....
|||The maintanence plan are scheduled in Enterprise Manager so i dont run it
with any switches. It does not matter which type of maintanece plan i create
(backup, integrity check, etc).
"Vishal Gandhi" wrote:
[vbcol=seagreen]
> Hi ,
> Can you mail the syntax you are using for sqlmaint.exe along with any
> switches that have been used.
> Thanks & Regards
> Vishal
> "Stefan Andersson" wrote:
|||Specify a report file for the plan and check for error messages from SQL Server in the report file.
If you are lucky, you find SQL Server errors there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stefan Andersson" <StefanAndersson@.discussions.microsoft.com> wrote in message
news:A79A5153-3A52-4456-AE97-28130C174A6B@.microsoft.com...
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history the
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on this
> database with the same result. I also created a maintanence plan with only an
> integrity check. Still the same result...
> Im going nuts....
Sudddenly our backup maintanence plan stoped working. In the job history the
following error is displayed:
The job failed. The job invoked by the user sa. The last step to run was
step 1 (Step 1).
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
The maintanence plan error log is empty.
The job does not backup transaction logs and i've deseleccted integrity
check. I've created a new database and recrated the maintanene plan on this
database with the same result. I also created a maintanence plan with only an
integrity check. Still the same result...
I′m going nuts....
Hi ,
Can you mail the syntax you are using for sqlmaint.exe along with any
switches that have been used.
Thanks & Regards
Vishal
"Stefan Andersson" wrote:
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history the
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on this
> database with the same result. I also created a maintanence plan with only an
> integrity check. Still the same result...
> I′m going nuts....
|||The maintanence plan are scheduled in Enterprise Manager so i dont run it
with any switches. It does not matter which type of maintanece plan i create
(backup, integrity check, etc).
"Vishal Gandhi" wrote:
[vbcol=seagreen]
> Hi ,
> Can you mail the syntax you are using for sqlmaint.exe along with any
> switches that have been used.
> Thanks & Regards
> Vishal
> "Stefan Andersson" wrote:
|||Specify a report file for the plan and check for error messages from SQL Server in the report file.
If you are lucky, you find SQL Server errors there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stefan Andersson" <StefanAndersson@.discussions.microsoft.com> wrote in message
news:A79A5153-3A52-4456-AE97-28130C174A6B@.microsoft.com...
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history the
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on this
> database with the same result. I also created a maintanence plan with only an
> integrity check. Still the same result...
> Im going nuts....
Maintanence plan challenge!
Heres a challenge...
Sudddenly our backup maintanence plan stoped working. In the job history the
following error is displayed:
The job failed. The job invoked by the user sa. The last step to run was
step 1 (Step 1).
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
The maintanence plan error log is empty.
The job does not backup transaction logs and i've deseleccted integrity
check. I've created a new database and recrated the maintanene plan on this
database with the same result. I also created a maintanence plan with only an
integrity check. Still the same result...
I´m going nuts....Hi ,
Can you mail the syntax you are using for sqlmaint.exe along with any
switches that have been used.
Thanks & Regards
Vishal
"Stefan Andersson" wrote:
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history the
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on this
> database with the same result. I also created a maintanence plan with only an
> integrity check. Still the same result...
> I´m going nuts....|||The maintanence plan are scheduled in Enterprise Manager so i dont run it
with any switches. It does not matter which type of maintanece plan i create
(backup, integrity check, etc).
"Vishal Gandhi" wrote:
> Hi ,
> Can you mail the syntax you are using for sqlmaint.exe along with any
> switches that have been used.
> Thanks & Regards
> Vishal
> "Stefan Andersson" wrote:
> > Heres a challenge...
> >
> > Sudddenly our backup maintanence plan stoped working. In the job history the
> > following error is displayed:
> >
> > The job failed. The job invoked by the user sa. The last step to run was
> > step 1 (Step 1).
> >
> > Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> > (Error 22029). The step failed.
> >
> > The maintanence plan error log is empty.
> >
> > The job does not backup transaction logs and i've deseleccted integrity
> > check. I've created a new database and recrated the maintanene plan on this
> > database with the same result. I also created a maintanence plan with only an
> > integrity check. Still the same result...
> >
> > I´m going nuts....|||Specify a report file for the plan and check for error messages from SQL Server in the report file.
If you are lucky, you find SQL Server errors there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stefan Andersson" <StefanAndersson@.discussions.microsoft.com> wrote in message
news:A79A5153-3A52-4456-AE97-28130C174A6B@.microsoft.com...
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history the
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on this
> database with the same result. I also created a maintanence plan with only an
> integrity check. Still the same result...
> I´m going nuts....sql
Sudddenly our backup maintanence plan stoped working. In the job history the
following error is displayed:
The job failed. The job invoked by the user sa. The last step to run was
step 1 (Step 1).
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
The maintanence plan error log is empty.
The job does not backup transaction logs and i've deseleccted integrity
check. I've created a new database and recrated the maintanene plan on this
database with the same result. I also created a maintanence plan with only an
integrity check. Still the same result...
I´m going nuts....Hi ,
Can you mail the syntax you are using for sqlmaint.exe along with any
switches that have been used.
Thanks & Regards
Vishal
"Stefan Andersson" wrote:
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history the
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on this
> database with the same result. I also created a maintanence plan with only an
> integrity check. Still the same result...
> I´m going nuts....|||The maintanence plan are scheduled in Enterprise Manager so i dont run it
with any switches. It does not matter which type of maintanece plan i create
(backup, integrity check, etc).
"Vishal Gandhi" wrote:
> Hi ,
> Can you mail the syntax you are using for sqlmaint.exe along with any
> switches that have been used.
> Thanks & Regards
> Vishal
> "Stefan Andersson" wrote:
> > Heres a challenge...
> >
> > Sudddenly our backup maintanence plan stoped working. In the job history the
> > following error is displayed:
> >
> > The job failed. The job invoked by the user sa. The last step to run was
> > step 1 (Step 1).
> >
> > Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> > (Error 22029). The step failed.
> >
> > The maintanence plan error log is empty.
> >
> > The job does not backup transaction logs and i've deseleccted integrity
> > check. I've created a new database and recrated the maintanene plan on this
> > database with the same result. I also created a maintanence plan with only an
> > integrity check. Still the same result...
> >
> > I´m going nuts....|||Specify a report file for the plan and check for error messages from SQL Server in the report file.
If you are lucky, you find SQL Server errors there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stefan Andersson" <StefanAndersson@.discussions.microsoft.com> wrote in message
news:A79A5153-3A52-4456-AE97-28130C174A6B@.microsoft.com...
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history the
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on this
> database with the same result. I also created a maintanence plan with only an
> integrity check. Still the same result...
> I´m going nuts....sql
Maintanence plan challenge!
Heres a challenge...
Sudddenly our backup maintanence plan stoped working. In the job history the
following error is displayed:
The job failed. The job invoked by the user sa. The last step to run was
step 1 (Step 1).
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42
000]
(Error 22029). The step failed.
The maintanence plan error log is empty.
The job does not backup transaction logs and i've deseleccted integrity
check. I've created a new database and recrated the maintanene plan on this
database with the same result. I also created a maintanence plan with only a
n
integrity check. Still the same result...
I′m going nuts....Hi ,
Can you mail the syntax you are using for sqlmaint.exe along with any
switches that have been used.
Thanks & Regards
Vishal
"Stefan Andersson" wrote:
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history t
he
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on th
is
> database with the same result. I also created a maintanence plan with only
an
> integrity check. Still the same result...
> I′m going nuts....|||The maintanence plan are scheduled in Enterprise Manager so i dont run it
with any switches. It does not matter which type of maintanece plan i create
(backup, integrity check, etc).
"Vishal Gandhi" wrote:
[vbcol=seagreen]
> Hi ,
> Can you mail the syntax you are using for sqlmaint.exe along with any
> switches that have been used.
> Thanks & Regards
> Vishal
> "Stefan Andersson" wrote:
>|||Specify a report file for the plan and check for error messages from SQL Ser
ver in the report file.
If you are lucky, you find SQL Server errors there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stefan Andersson" <StefanAndersson@.discussions.microsoft.com> wrote in mess
age
news:A79A5153-3A52-4456-AE97-28130C174A6B@.microsoft.com...
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history t
he
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on th
is
> database with the same result. I also created a maintanence plan with only
an
> integrity check. Still the same result...
> Im going nuts....
Sudddenly our backup maintanence plan stoped working. In the job history the
following error is displayed:
The job failed. The job invoked by the user sa. The last step to run was
step 1 (Step 1).
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42
000]
(Error 22029). The step failed.
The maintanence plan error log is empty.
The job does not backup transaction logs and i've deseleccted integrity
check. I've created a new database and recrated the maintanene plan on this
database with the same result. I also created a maintanence plan with only a
n
integrity check. Still the same result...
I′m going nuts....Hi ,
Can you mail the syntax you are using for sqlmaint.exe along with any
switches that have been used.
Thanks & Regards
Vishal
"Stefan Andersson" wrote:
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history t
he
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on th
is
> database with the same result. I also created a maintanence plan with only
an
> integrity check. Still the same result...
> I′m going nuts....|||The maintanence plan are scheduled in Enterprise Manager so i dont run it
with any switches. It does not matter which type of maintanece plan i create
(backup, integrity check, etc).
"Vishal Gandhi" wrote:
[vbcol=seagreen]
> Hi ,
> Can you mail the syntax you are using for sqlmaint.exe along with any
> switches that have been used.
> Thanks & Regards
> Vishal
> "Stefan Andersson" wrote:
>|||Specify a report file for the plan and check for error messages from SQL Ser
ver in the report file.
If you are lucky, you find SQL Server errors there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stefan Andersson" <StefanAndersson@.discussions.microsoft.com> wrote in mess
age
news:A79A5153-3A52-4456-AE97-28130C174A6B@.microsoft.com...
> Heres a challenge...
> Sudddenly our backup maintanence plan stoped working. In the job history t
he
> following error is displayed:
> The job failed. The job invoked by the user sa. The last step to run was
> step 1 (Step 1).
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> The maintanence plan error log is empty.
> The job does not backup transaction logs and i've deseleccted integrity
> check. I've created a new database and recrated the maintanene plan on th
is
> database with the same result. I also created a maintanence plan with only
an
> integrity check. Still the same result...
> Im going nuts....
Subscribe to:
Posts (Atom)