NOSKIP & SKIP make no difference to the amount of time backup take

Hi
I run a full backup once a month and a transaction log backup every 10 mins
through working hours for the remainder of the month until the backup is
re-initialised at th start of the following month.
I backup to virtual sql disk object devices.
As the month progresses the backup takes longer and longer whilst the amount
of data being backed up every 10 minutes cycle is always roughly the same.
its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
month.
I noticed that NOSKIP was being used in the command - i changed this to
SKIP, but it made no difference in the amount of time the backup takes. Isnt
this suppoed to stop some sort of integrity scan on all other backup sets in
the archive?
full syntax of the backup command that runs now is:
BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
but as I say, the SKIP command instead of NOSKIP makes no difference.
whats going on here?
any help appreciated.
Thanks
Alastair.
On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
wrote:
> Hi
> I run a full backup once a month and a transaction log backup every 10 mins
> through working hours for the remainder of the month until the backup is
> re-initialised at th start of the following month.
> I backup to virtual sql disk object devices.
> As the month progresses the backup takes longer and longer whilst the amount
> of data being backed up every 10 minutes cycle is always roughly the same.
> its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> month.
> I noticed that NOSKIP was being used in the command - i changed this to
> SKIP, but it made no difference in the amount of time the backup takes. Isnt
> this suppoed to stop some sort of integrity scan on all other backup sets in
> the archive?
> full syntax of the backup command that runs now is:
> BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> but as I say, the SKIP command instead of NOSKIP makes no difference.
> whats going on here?
> any help appreciated.
> Thanks
> Alastair.
Noskip/skip does not make any difference on Backup time. It just
checks expiration date of backup sets. I just guess that the reason
may be that your disk gets fragmented after number of backups and that
may cause delay.
|||yes but if theres a LOT of backup sets surely this will add a delay?
"amish" wrote:

> On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
> wrote:
> Noskip/skip does not make any difference on Backup time. It just
> checks expiration date of backup sets. I just guess that the reason
> may be that your disk gets fragmented after number of backups and that
> may cause delay.
>
|||Looking at the BACKUP commands you posted, which includes REWIND and
NOFORMAT, it looks like the backup goes to tape. When you referred to
"virtual sql disk object devices" I was not expecting that. Tapes are
not virtual disks, tapes are tapes.
I have seen that behavior when backing up directly to tape. The
problem in that case was reading the tape from the start to the point
where the prior backup ended. Lets consider that by the end of the
month there have been around 1000 log backups assuming a 5 day work
week and an 8 hour work day.
I suggest changing the REWIND option to NOREWIND. From the
documentation: "NOREWIND -- Specifies that SQL Server will keep the
tape open after the backup operation. You can use this option to help
improve performance when performing multiple backup operations to a
tape."
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:

>Hi
>I run a full backup once a month and a transaction log backup every 10 mins
>through working hours for the remainder of the month until the backup is
>re-initialised at th start of the following month.
>I backup to virtual sql disk object devices.
>As the month progresses the backup takes longer and longer whilst the amount
>of data being backed up every 10 minutes cycle is always roughly the same.
>its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>month.
>I noticed that NOSKIP was being used in the command - i changed this to
>SKIP, but it made no difference in the amount of time the backup takes. Isnt
>this suppoed to stop some sort of integrity scan on all other backup sets in
>the archive?
>full syntax of the backup command that runs now is:
>BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>but as I say, the SKIP command instead of NOSKIP makes no difference.
>whats going on here?
>any help appreciated.
>Thanks
>Alastair.
|||thanks for your response Roy.
if you go to server objects >backup devices you can configure a virtual
'tape' that is in effect a file on the physical disk. this is what im backing
up to. as far as I can tell the only differnece between using this method and
backing up direct to disk is that you get to use a shorter name in the backup
job command rather than a full file path.
anyhoo
may be this ISNT the only differnce. REWIND is default right? so maybe SQL
is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
what happens in 2 backups time (20 mins.)
cheers for the pointer
"Roy Harvey" wrote:

> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> <Methodology@.discussions.microsoft.com> wrote:
>
|||On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>
> <Methodol...@.discussions.microsoft.com> wrote:
>
>
>
>
>
>
> - Show quoted text -
I dont have much idea about tapes but in BOL it says that
NOREWIND implies NOUNLOAD, and these options are incompatible within a
single BACKUP statement
and Methodology has specified also NOUNLOAD in the script.
|||fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
implies the former
thanks
"amish" wrote:

> On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> I dont have much idea about tapes but in BOL it says that
> NOREWIND implies NOUNLOAD, and these options are incompatible within a
> single BACKUP statement
> and Methodology has specified also NOUNLOAD in the script.
>
|||NOUNLOAD/UNLOAD and REWIND/NOREWIND are tape-specific commands. If
you are not using tape they should not be in the BACKUP command at
all.
However, I am paranoid when it comes to dealing with backups. I don't
know that anyone has ever confirmed that there are no side effects to
using tape-specific features with a disk backup. So while I would
suggest removing the tape-specific parameters if you are not using
tape, I would combine that with starting a fresh backup file. It
would not hurt to backup to another device or file for the rest of the
month.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:02:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>thanks for your response Roy.
>if you go to server objects >backup devices you can configure a virtual
>'tape' that is in effect a file on the physical disk. this is what im backing
>up to. as far as I can tell the only differnece between using this method and
>backing up direct to disk is that you get to use a shorter name in the backup
>job command rather than a full file path.
>anyhoo
>may be this ISNT the only differnce. REWIND is default right? so maybe SQL
>is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
>what happens in 2 backups time (20 mins.)
>cheers for the pointer
>"Roy Harvey" wrote:
|||no difference - still taking 5 minutes...
heeeelllpppppp
"Methodology" wrote:
[vbcol=seagreen]
> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
> implies the former
> thanks
>
> "amish" wrote:
|||I guess the answer is not to write so many backups to a single file.
Personally I don't think I would set up a backup scheme with one
monthly database backup and log backups every ten minutes. If a
database is active enough to justify a ten-minute interval for the
logs, I would backup the enitre database at least weekly, but more
likely daily. Of course I have no way of knowing your environment and
specific factors that went into the design.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:40:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>no difference - still taking 5 minutes...
>
>heeeelllpppppp
>
>
>"Methodology" wrote:

NOSKIP & SKIP make no difference to the amount of time backup take

Hi
I run a full backup once a month and a transaction log backup every 10 mins
through working hours for the remainder of the month until the backup is
re-initialised at th start of the following month.
I backup to virtual sql disk object devices.
As the month progresses the backup takes longer and longer whilst the amount
of data being backed up every 10 minutes cycle is always roughly the same.
its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
month.
I noticed that NOSKIP was being used in the command - i changed this to
SKIP, but it made no difference in the amount of time the backup takes. Isnt
this suppoed to stop some sort of integrity scan on all other backup sets in
the archive?
full syntax of the backup command that runs now is:
BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
but as I say, the SKIP command instead of NOSKIP makes no difference.
whats going on here?
any help appreciated.
Thanks
Alastair.On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
wrote:
> Hi
> I run a full backup once a month and a transaction log backup every 10 mins
> through working hours for the remainder of the month until the backup is
> re-initialised at th start of the following month.
> I backup to virtual sql disk object devices.
> As the month progresses the backup takes longer and longer whilst the amount
> of data being backed up every 10 minutes cycle is always roughly the same.
> its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> month.
> I noticed that NOSKIP was being used in the command - i changed this to
> SKIP, but it made no difference in the amount of time the backup takes. Isnt
> this suppoed to stop some sort of integrity scan on all other backup sets in
> the archive?
> full syntax of the backup command that runs now is:
> BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> but as I say, the SKIP command instead of NOSKIP makes no difference.
> whats going on here?
> any help appreciated.
> Thanks
> Alastair.
Noskip/skip does not make any difference on Backup time. It just
checks expiration date of backup sets. I just guess that the reason
may be that your disk gets fragmented after number of backups and that
may cause delay.|||yes but if theres a LOT of backup sets surely this will add a delay?
"amish" wrote:
> On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
> wrote:
> > Hi
> >
> > I run a full backup once a month and a transaction log backup every 10 mins
> > through working hours for the remainder of the month until the backup is
> > re-initialised at th start of the following month.
> >
> > I backup to virtual sql disk object devices.
> >
> > As the month progresses the backup takes longer and longer whilst the amount
> > of data being backed up every 10 minutes cycle is always roughly the same.
> > its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > month.
> >
> > I noticed that NOSKIP was being used in the command - i changed this to
> > SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > this suppoed to stop some sort of integrity scan on all other backup sets in
> > the archive?
> >
> > full syntax of the backup command that runs now is:
> >
> > BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> > but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> > whats going on here?
> >
> > any help appreciated.
> >
> > Thanks
> >
> > Alastair.
> Noskip/skip does not make any difference on Backup time. It just
> checks expiration date of backup sets. I just guess that the reason
> may be that your disk gets fragmented after number of backups and that
> may cause delay.
>|||Looking at the BACKUP commands you posted, which includes REWIND and
NOFORMAT, it looks like the backup goes to tape. When you referred to
"virtual sql disk object devices" I was not expecting that. Tapes are
not virtual disks, tapes are tapes.
I have seen that behavior when backing up directly to tape. The
problem in that case was reading the tape from the start to the point
where the prior backup ended. Lets consider that by the end of the
month there have been around 1000 log backups assuming a 5 day work
week and an 8 hour work day.
I suggest changing the REWIND option to NOREWIND. From the
documentation: "NOREWIND -- Specifies that SQL Server will keep the
tape open after the backup operation. You can use this option to help
improve performance when performing multiple backup operations to a
tape."
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>Hi
>I run a full backup once a month and a transaction log backup every 10 mins
>through working hours for the remainder of the month until the backup is
>re-initialised at th start of the following month.
>I backup to virtual sql disk object devices.
>As the month progresses the backup takes longer and longer whilst the amount
>of data being backed up every 10 minutes cycle is always roughly the same.
>its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>month.
>I noticed that NOSKIP was being used in the command - i changed this to
>SKIP, but it made no difference in the amount of time the backup takes. Isnt
>this suppoed to stop some sort of integrity scan on all other backup sets in
>the archive?
>full syntax of the backup command that runs now is:
>BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>but as I say, the SKIP command instead of NOSKIP makes no difference.
>whats going on here?
>any help appreciated.
>Thanks
>Alastair.|||thanks for your response Roy.
if you go to server objects >backup devices you can configure a virtual
'tape' that is in effect a file on the physical disk. this is what im backing
up to. as far as I can tell the only differnece between using this method and
backing up direct to disk is that you get to use a shorter name in the backup
job command rather than a full file path.
anyhoo
may be this ISNT the only differnce. REWIND is default right? so maybe SQL
is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
what happens in 2 backups time (20 mins.)
cheers for the pointer
"Roy Harvey" wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> <Methodology@.discussions.microsoft.com> wrote:
> >Hi
> >
> >I run a full backup once a month and a transaction log backup every 10 mins
> >through working hours for the remainder of the month until the backup is
> >re-initialised at th start of the following month.
> >
> >I backup to virtual sql disk object devices.
> >
> >As the month progresses the backup takes longer and longer whilst the amount
> >of data being backed up every 10 minutes cycle is always roughly the same.
> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> >month.
> >
> >I noticed that NOSKIP was being used in the command - i changed this to
> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> >this suppoed to stop some sort of integrity scan on all other backup sets in
> >the archive?
> >
> >full syntax of the backup command that runs now is:
> >
> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> >whats going on here?
> >
> >any help appreciated.
> >
> >Thanks
> >
> >Alastair.
>|||On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>
> <Methodol...@.discussions.microsoft.com> wrote:
> >Hi
> >I run a full backup once a month and a transaction log backup every 10 mins
> >through working hours for the remainder of the month until the backup is
> >re-initialised at th start of the following month.
> >I backup to virtual sql disk object devices.
> >As the month progresses the backup takes longer and longer whilst the amount
> >of data being backed up every 10 minutes cycle is always roughly the same.
> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> >month.
> >I noticed that NOSKIP was being used in the command - i changed this to
> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> >this suppoed to stop some sort of integrity scan on all other backup sets in
> >the archive?
> >full syntax of the backup command that runs now is:
> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >whats going on here?
> >any help appreciated.
> >Thanks
> >Alastair.- Hide quoted text -
> - Show quoted text -
I dont have much idea about tapes but in BOL it says that
NOREWIND implies NOUNLOAD, and these options are incompatible within a
single BACKUP statement
and Methodology has specified also NOUNLOAD in the script.|||fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
implies the former
thanks
"amish" wrote:
> On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> > Looking at the BACKUP commands you posted, which includes REWIND and
> > NOFORMAT, it looks like the backup goes to tape. When you referred to
> > "virtual sql disk object devices" I was not expecting that. Tapes are
> > not virtual disks, tapes are tapes.
> >
> > I have seen that behavior when backing up directly to tape. The
> > problem in that case was reading the tape from the start to the point
> > where the prior backup ended. Lets consider that by the end of the
> > month there have been around 1000 log backups assuming a 5 day work
> > week and an 8 hour work day.
> >
> > I suggest changing the REWIND option to NOREWIND. From the
> > documentation: "NOREWIND -- Specifies that SQL Server will keep the
> > tape open after the backup operation. You can use this option to help
> > improve performance when performing multiple backup operations to a
> > tape."
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> >
> >
> >
> > <Methodol...@.discussions.microsoft.com> wrote:
> > >Hi
> >
> > >I run a full backup once a month and a transaction log backup every 10 mins
> > >through working hours for the remainder of the month until the backup is
> > >re-initialised at th start of the following month.
> >
> > >I backup to virtual sql disk object devices.
> >
> > >As the month progresses the backup takes longer and longer whilst the amount
> > >of data being backed up every 10 minutes cycle is always roughly the same.
> > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > >month.
> >
> > >I noticed that NOSKIP was being used in the command - i changed this to
> > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > >this suppoed to stop some sort of integrity scan on all other backup sets in
> > >the archive?
> >
> > >full syntax of the backup command that runs now is:
> >
> > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> > >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> > >whats going on here?
> >
> > >any help appreciated.
> >
> > >Thanks
> >
> > >Alastair.- Hide quoted text -
> >
> > - Show quoted text -
> I dont have much idea about tapes but in BOL it says that
> NOREWIND implies NOUNLOAD, and these options are incompatible within a
> single BACKUP statement
> and Methodology has specified also NOUNLOAD in the script.
>|||NOUNLOAD/UNLOAD and REWIND/NOREWIND are tape-specific commands. If
you are not using tape they should not be in the BACKUP command at
all.
However, I am paranoid when it comes to dealing with backups. I don't
know that anyone has ever confirmed that there are no side effects to
using tape-specific features with a disk backup. So while I would
suggest removing the tape-specific parameters if you are not using
tape, I would combine that with starting a fresh backup file. It
would not hurt to backup to another device or file for the rest of the
month.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:02:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>thanks for your response Roy.
>if you go to server objects >backup devices you can configure a virtual
>'tape' that is in effect a file on the physical disk. this is what im backing
>up to. as far as I can tell the only differnece between using this method and
>backing up direct to disk is that you get to use a shorter name in the backup
>job command rather than a full file path.
>anyhoo
>may be this ISNT the only differnce. REWIND is default right? so maybe SQL
>is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
>what happens in 2 backups time (20 mins.)
>cheers for the pointer
>"Roy Harvey" wrote:
>> Looking at the BACKUP commands you posted, which includes REWIND and
>> NOFORMAT, it looks like the backup goes to tape. When you referred to
>> "virtual sql disk object devices" I was not expecting that. Tapes are
>> not virtual disks, tapes are tapes.
>> I have seen that behavior when backing up directly to tape. The
>> problem in that case was reading the tape from the start to the point
>> where the prior backup ended. Lets consider that by the end of the
>> month there have been around 1000 log backups assuming a 5 day work
>> week and an 8 hour work day.
>> I suggest changing the REWIND option to NOREWIND. From the
>> documentation: "NOREWIND -- Specifies that SQL Server will keep the
>> tape open after the backup operation. You can use this option to help
>> improve performance when performing multiple backup operations to a
>> tape."
>> Roy Harvey
>> Beacon Falls, CT
>> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>> <Methodology@.discussions.microsoft.com> wrote:
>> >Hi
>> >
>> >I run a full backup once a month and a transaction log backup every 10 mins
>> >through working hours for the remainder of the month until the backup is
>> >re-initialised at th start of the following month.
>> >
>> >I backup to virtual sql disk object devices.
>> >
>> >As the month progresses the backup takes longer and longer whilst the amount
>> >of data being backed up every 10 minutes cycle is always roughly the same.
>> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>> >month.
>> >
>> >I noticed that NOSKIP was being used in the command - i changed this to
>> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
>> >this suppoed to stop some sort of integrity scan on all other backup sets in
>> >the archive?
>> >
>> >full syntax of the backup command that runs now is:
>> >
>> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>> >
>> >but as I say, the SKIP command instead of NOSKIP makes no difference.
>> >
>> >whats going on here?
>> >
>> >any help appreciated.
>> >
>> >Thanks
>> >
>> >Alastair.|||no difference - still taking 5 minutes...
heeeelllpppppp
"Methodology" wrote:
> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
> implies the former
> thanks
>
> "amish" wrote:
> > On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> > > Looking at the BACKUP commands you posted, which includes REWIND and
> > > NOFORMAT, it looks like the backup goes to tape. When you referred to
> > > "virtual sql disk object devices" I was not expecting that. Tapes are
> > > not virtual disks, tapes are tapes.
> > >
> > > I have seen that behavior when backing up directly to tape. The
> > > problem in that case was reading the tape from the start to the point
> > > where the prior backup ended. Lets consider that by the end of the
> > > month there have been around 1000 log backups assuming a 5 day work
> > > week and an 8 hour work day.
> > >
> > > I suggest changing the REWIND option to NOREWIND. From the
> > > documentation: "NOREWIND -- Specifies that SQL Server will keep the
> > > tape open after the backup operation. You can use this option to help
> > > improve performance when performing multiple backup operations to a
> > > tape."
> > >
> > > Roy Harvey
> > > Beacon Falls, CT
> > >
> > > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> > >
> > >
> > >
> > > <Methodol...@.discussions.microsoft.com> wrote:
> > > >Hi
> > >
> > > >I run a full backup once a month and a transaction log backup every 10 mins
> > > >through working hours for the remainder of the month until the backup is
> > > >re-initialised at th start of the following month.
> > >
> > > >I backup to virtual sql disk object devices.
> > >
> > > >As the month progresses the backup takes longer and longer whilst the amount
> > > >of data being backed up every 10 minutes cycle is always roughly the same.
> > > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > > >month.
> > >
> > > >I noticed that NOSKIP was being used in the command - i changed this to
> > > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > > >this suppoed to stop some sort of integrity scan on all other backup sets in
> > > >the archive?
> > >
> > > >full syntax of the backup command that runs now is:
> > >
> > > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> > >
> > > >but as I say, the SKIP command instead of NOSKIP makes no difference.
> > >
> > > >whats going on here?
> > >
> > > >any help appreciated.
> > >
> > > >Thanks
> > >
> > > >Alastair.- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > I dont have much idea about tapes but in BOL it says that
> >
> > NOREWIND implies NOUNLOAD, and these options are incompatible within a
> > single BACKUP statement
> >
> > and Methodology has specified also NOUNLOAD in the script.
> >
> >|||I guess the answer is not to write so many backups to a single file.
Personally I don't think I would set up a backup scheme with one
monthly database backup and log backups every ten minutes. If a
database is active enough to justify a ten-minute interval for the
logs, I would backup the enitre database at least weekly, but more
likely daily. Of course I have no way of knowing your environment and
specific factors that went into the design.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:40:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>no difference - still taking 5 minutes...
>
>heeeelllpppppp
>
>
>"Methodology" wrote:
>> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
>> implies the former
>> thanks
>>
>> "amish" wrote:
>> > On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
>> > > Looking at the BACKUP commands you posted, which includes REWIND and
>> > > NOFORMAT, it looks like the backup goes to tape. When you referred to
>> > > "virtual sql disk object devices" I was not expecting that. Tapes are
>> > > not virtual disks, tapes are tapes.
>> > >
>> > > I have seen that behavior when backing up directly to tape. The
>> > > problem in that case was reading the tape from the start to the point
>> > > where the prior backup ended. Lets consider that by the end of the
>> > > month there have been around 1000 log backups assuming a 5 day work
>> > > week and an 8 hour work day.
>> > >
>> > > I suggest changing the REWIND option to NOREWIND. From the
>> > > documentation: "NOREWIND -- Specifies that SQL Server will keep the
>> > > tape open after the backup operation. You can use this option to help
>> > > improve performance when performing multiple backup operations to a
>> > > tape."
>> > >
>> > > Roy Harvey
>> > > Beacon Falls, CT
>> > >
>> > > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>> > >
>> > >
>> > >
>> > > <Methodol...@.discussions.microsoft.com> wrote:
>> > > >Hi
>> > >
>> > > >I run a full backup once a month and a transaction log backup every 10 mins
>> > > >through working hours for the remainder of the month until the backup is
>> > > >re-initialised at th start of the following month.
>> > >
>> > > >I backup to virtual sql disk object devices.
>> > >
>> > > >As the month progresses the backup takes longer and longer whilst the amount
>> > > >of data being backed up every 10 minutes cycle is always roughly the same.
>> > > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>> > > >month.
>> > >
>> > > >I noticed that NOSKIP was being used in the command - i changed this to
>> > > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
>> > > >this suppoed to stop some sort of integrity scan on all other backup sets in
>> > > >the archive?
>> > >
>> > > >full syntax of the backup command that runs now is:
>> > >
>> > > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>> > > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>> > >
>> > > >but as I say, the SKIP command instead of NOSKIP makes no difference.
>> > >
>> > > >whats going on here?
>> > >
>> > > >any help appreciated.
>> > >
>> > > >Thanks
>> > >
>> > > >Alastair.- Hide quoted text -
>> > >
>> > > - Show quoted text -
>> >
>> > I dont have much idea about tapes but in BOL it says that
>> >
>> > NOREWIND implies NOUNLOAD, and these options are incompatible within a
>> > single BACKUP statement
>> >
>> > and Methodology has specified also NOUNLOAD in the script.
>> >
>> >

NOSKIP & SKIP make no difference to the amount of time backup take

Hi
I run a full backup once a month and a transaction log backup every 10 mins
through working hours for the remainder of the month until the backup is
re-initialised at th start of the following month.
I backup to virtual sql disk object devices.
As the month progresses the backup takes longer and longer whilst the amount
of data being backed up every 10 minutes cycle is always roughly the same.
its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
month.
I noticed that NOSKIP was being used in the command - i changed this to
SKIP, but it made no difference in the amount of time the backup takes. Isnt
this suppoed to stop some sort of integrity scan on all other backup sets in
the archive?
full syntax of the backup command that runs now is:
BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , N
OINIT ,
NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
but as I say, the SKIP command instead of NOSKIP makes no difference.
whats going on here?
any help appreciated.
Thanks
Alastair.On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
wrote:
> Hi
> I run a full backup once a month and a transaction log backup every 10 min
s
> through working hours for the remainder of the month until the backup is
> re-initialised at th start of the following month.
> I backup to virtual sql disk object devices.
> As the month progresses the backup takes longer and longer whilst the amou
nt
> of data being backed up every 10 minutes cycle is always roughly the same.
> its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> month.
> I noticed that NOSKIP was being used in the command - i changed this to
> SKIP, but it made no difference in the amount of time the backup takes. Is
nt
> this suppoed to stop some sort of integrity scan on all other backup sets
in
> the archive?
> full syntax of the backup command that runs now is:
> BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT ,
NOINIT ,
> NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> but as I say, the SKIP command instead of NOSKIP makes no difference.
> whats going on here?
> any help appreciated.
> Thanks
> Alastair.
Noskip/skip does not make any difference on Backup time. It just
checks expiration date of backup sets. I just guess that the reason
may be that your disk gets fragmented after number of backups and that
may cause delay.|||Looking at the BACKUP commands you posted, which includes REWIND and
NOFORMAT, it looks like the backup goes to tape. When you referred to
"virtual sql disk object devices" I was not expecting that. Tapes are
not virtual disks, tapes are tapes.
I have seen that behavior when backing up directly to tape. The
problem in that case was reading the tape from the start to the point
where the prior backup ended. Lets consider that by the end of the
month there have been around 1000 log backups assuming a 5 day work
week and an 8 hour work day.
I suggest changing the REWIND option to NOREWIND. From the
documentation: "NOREWIND -- Specifies that SQL Server will keep the
tape open after the backup operation. You can use this option to help
improve performance when performing multiple backup operations to a
tape."
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:

>Hi
>I run a full backup once a month and a transaction log backup every 10 mins
>through working hours for the remainder of the month until the backup is
>re-initialised at th start of the following month.
>I backup to virtual sql disk object devices.
>As the month progresses the backup takes longer and longer whilst the amoun
t
>of data being backed up every 10 minutes cycle is always roughly the same.
>its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>month.
>I noticed that NOSKIP was being used in the command - i changed this to
>SKIP, but it made no difference in the amount of time the backup takes. Isn
t
>this suppoed to stop some sort of integrity scan on all other backup sets i
n
>the archive?
>full syntax of the backup command that runs now is:
>BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT ,
NOINIT ,
>NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>but as I say, the SKIP command instead of NOSKIP makes no difference.
>whats going on here?
>any help appreciated.
>Thanks
>Alastair.|||On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>
> <Methodol...@.discussions.microsoft.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
I dont have much idea about tapes but in BOL it says that
NOREWIND implies NOUNLOAD, and these options are incompatible within a
single BACKUP statement
and Methodology has specified also NOUNLOAD in the script.

NoRows Property in Matrix does not work

I wanted to set the NoRows property for a matrix on a report to inform
the user when this condition occurs. I run the report by supplying
parameters that I am sure returns no data. Specifically, I am
determining the table to query based on user inputs. I provide inputs
that will look for a non existent table. I do an existence check on
the table to see if it is there to prevent a sql server error. If it
finds no table the query is not run. However, given that nothing is
returned the norows property is ignored and I get the error "Invalid
attempt to read when no data is present."
Could it be because the matrix requires an empty dataset back?That is what it was. I now ensure I return an empty dataset if the
table does not exist.

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__Stephen
I got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid = (
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid => (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

noob: How do i run pl/sql on ms sql?

Hi experts,

I'm using MS SQL 2000. The closest i could get to having pl/sql within ms sql was tru its stored procedures feature.

I created a new stored procedure within the built-in Northwind database. I pasted the following inside:

--------------------
declare

vname Employees.firstname%TYPE;

begin

SELECT firstname INTO vname FROM Employees
WHERE firstname = 'Nancy';

dbms_output.put_line ('Name is ' || vname);

EXCEPTION

when NO_DATA_FOUND then
dbms_output.put_line ('no data found');

when TOO_MANY_RECORDS then
dbms_output.put_line ('too many records');

END;

--------------------

I checked the syntax and i get some error about the employees table.
Error 155: 'Employees' is not a recognized cursor option

Any idea?

Thanks..You've got some serious reading to do...there is a major methodolgy switch you'll have to understand (not to mention sytax) between the two...

Oracle has a lot of nice "built in" features, and at the same time is extremely painful...

For example, ylucan not use %TYPE...

YOu must explicitlety declare EVERYTHING...

This is how you write what your example is trying to do...

CREATE PROC mySproc99
AS

BEGIN

DECLARE @.vname varchar(40), @.Rowcount int, @.Error int

SELECT @.vname = Lastname
FROM Employees
WHERE firstname = 'Nancy'

SELECT @.RowCount = @.@.ROWCOUNT, @.Error = @.@.Error

IF @.Error <> 0
BEGIN
PRINT 'Error Condition ' + CONVERT(varchar(5),@.Error)
Return @.Error
END

IF @.RowCount <> 1
BEGIN
PRINT 'Multiple Rows Found Error'
Return 2
END

IF @.RowCount = 1
BEGIN
PRINT 'Name is '+ @.vname
Return 0
END
END

GO

EXEC mySproc99
GO

DROP PROC mySproc99
GO

Non-SysAdmins have been denied permission to run CmdExec job steps

Is there a certain role or permission that a user must be given to run jobs?
Thanks.
Ron
Per BOL, under "jobs, running/ Transact SQL".
Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role can
start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
ChrisR
"Ron" wrote:

> Is there a certain role or permission that a user must be given to run jobs?
> Thanks.
> Ron
|||Under properties of SQL Server Agent there's a setting under the job system
tab:
"Non-sysadmin job step proxy account" - I checked this off and users were OK.
Ron
"Ron" wrote:

> Is there a certain role or permission that a user must be given to run jobs?
> Thanks.
> Ron

Non-SysAdmins have been denied permission to run CmdExec job steps

Is there a certain role or permission that a user must be given to run jobs?
Thanks.
RonPer BOL, under "jobs, running/ Transact SQL".
Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role can
start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
--
ChrisR
"Ron" wrote:
> Is there a certain role or permission that a user must be given to run jobs?
> Thanks.
> Ron|||Under properties of SQL Server Agent there's a setting under the job system
tab:
"Non-sysadmin job step proxy account" - I checked this off and users were OK.
Ron
"Ron" wrote:
> Is there a certain role or permission that a user must be given to run jobs?
> Thanks.
> Ron

Non-SysAdmins have been denied permission to run CmdExec job steps

Is there a certain role or permission that a user must be given to run jobs?
Thanks.
RonPer BOL, under "jobs, running/ Transact SQL".
Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role ca
n
start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
ChrisR
"Ron" wrote:

> Is there a certain role or permission that a user must be given to run job
s?
> Thanks.
> Ron|||Under properties of SQL Server Agent there's a setting under the job system
tab:
"Non-sysadmin job step proxy account" - I checked this off and users were OK
.
Ron
"Ron" wrote:

> Is there a certain role or permission that a user must be given to run job
s?
> Thanks.
> Ronsql

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!sql

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

None-Domain server cannot access SQL2005 data on Windows 2003 domain server

I'm trying to run a test from my test environment which is a non-domain Windows 2000 server to access my domain 2003 with SQL2005. I have install 2005 tools to try to access the SQL server.

- I have try following the KB265808 - no success.

- Reading alot of blogs and it seems all are pointing to the same problem. "Remote access" but the settign is enabled.

Error Message:

TITLE: Connect to Server

Cannot connect to ardsqldatawh.

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

Question: Could Windows 2003 security be blocking access? I'm using sa account to access.

Also, sa account does not seems to work for remote access. It is ok when accessing locally.

Any help would be appreciated.

949jc

Hi 959jc,

Have you configured the Windows firewall on your server machine to allow remote connections? If you're intending to use Named Pipes, then you will need to enable the "File and Print sharing" exception. Also, please take a look at this topic in the BOL for additional info regarding configuring firewalls: http://msdn2.microsoft.com/en-us/library/ms175043.aspx.

Another consideration is whether you've restarted your instance after enabling remote protocols.

Otherwise, please provide your connection string and the contents of your error log after you've restarted the service.

Thanks,
Il-Sung.

Non sa running jobs?

Is there a way for a user to manage ( run, delete, add ) a job if he has not
got sa permission?
Thanks
AlexAlex,
Yes if the user is a member of the SYSADMIN fixed server role. Note however
that membership of this role should be limited as the role enables a user to
do pretty much anything in SQL Server.
HTH
Jerry
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> Is there a way for a user to manage ( run, delete, add ) a job if he has
> not
> got sa permission?
> Thanks
> Alex|||Thanks for this. But the problem we have is SOX compliance !. Us dba's have
been stripped off sa/SYSADMIN fixed role permission so we can't modify data.
The side effect of this is that there are loads of admin tasks we cannot now
do.
Alex
"Jerry Spivey" wrote:

> Alex,
> Yes if the user is a member of the SYSADMIN fixed server role. Note howev
er
> that membership of this role should be limited as the role enables a user
to
> do pretty much anything in SQL Server.
> HTH
> Jerry
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
>
>|||Hmmm...that doesn't sound right. You're a DBA but you're not a member of
the SYSADMIN fixed server role? How can you do your job?
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...[vbcol=seagreen]
> Thanks for this. But the problem we have is SOX compliance !. Us dba's
> have
> been stripped off sa/SYSADMIN fixed role permission so we can't modify
> data.
> The side effect of this is that there are loads of admin tasks we cannot
> now
> do.
> Alex
>
> "Jerry Spivey" wrote:
>|||Since we don't want SA running jobs (or being in the Sysadmin group), I set
up a login with win authent, sysadmin to run jobs. No one needs the passwor
d
to use; just set this user up as the proxy account (under agent properties,
job system), and if any job owner is not a sysadmin, it defaults to this guy
.
HTH, mary
"Alex" wrote:
[vbcol=seagreen]
> Thanks for this. But the problem we have is SOX compliance !. Us dba's hav
e
> been stripped off sa/SYSADMIN fixed role permission so we can't modify dat
a.
> The side effect of this is that there are loads of admin tasks we cannot n
ow
> do.
> Alex
>
> "Jerry Spivey" wrote:
>|||Yes, try telling this to the auditors!
We are very frustrated. If we need to add a job we will need to apply for
permission to log in as sa, after going through multilevel of sign off from
the senior management - all for running a SQL Profile, or creating
maintenance plan !!
and then all actions will be be logged as well.
Alex
"Jerry Spivey" wrote:

> Hmmm...that doesn't sound right. You're a DBA but you're not a member of
> the SYSADMIN fixed server role? How can you do your job?
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
>
>|||Curious, if the is a reason to suddenly restore a db in the middle of the da
y
so production activities can resume, is the same process to log in as SA
required?
ChrisR
"Alex" wrote:
[vbcol=seagreen]
> Yes, try telling this to the auditors!
> We are very frustrated. If we need to add a job we will need to apply for
> permission to log in as sa, after going through multilevel of sign off fro
m
> the senior management - all for running a SQL Profile, or creating
> maintenance plan !!
> and then all actions will be be logged as well.
> Alex
> "Jerry Spivey" wrote:
>|||I don't recall anything in SOX that states that a DBA can not have sa
rights. Someone is taking the wording way beyond it's intent. SQL2005 will
have roles specifically for this but in 2000 it is not that easy.
Andrew J. Kelly SQL MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...[vbcol=seagreen]
> Thanks for this. But the problem we have is SOX compliance !. Us dba's
> have
> been stripped off sa/SYSADMIN fixed role permission so we can't modify
> data.
> The side effect of this is that there are loads of admin tasks we cannot
> now
> do.
> Alex
>
> "Jerry Spivey" wrote:
>|||Thanks Mary. This could be the escape route we have been looking for!
Alex
"Mary" wrote:
[vbcol=seagreen]
> Since we don't want SA running jobs (or being in the Sysadmin group), I se
t
> up a login with win authent, sysadmin to run jobs. No one needs the passw
ord
> to use; just set this user up as the proxy account (under agent properties
,
> job system), and if any job owner is not a sysadmin, it defaults to this g
uy.
> HTH, mary
> "Alex" wrote:
>|||Andrew
We can run sa tasks, but we need authorisation every time we need to. its
like working with one of your hands tied.
Alex
"Andrew J. Kelly" wrote:

> I don't recall anything in SOX that states that a DBA can not have sa
> rights. Someone is taking the wording way beyond it's intent. SQL2005 wi
ll
> have roles specifically for this but in 2000 it is not that easy.
> --
> Andrew J. Kelly SQL MVP
>
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
>
>

Non sa running jobs?

Is there a way for a user to manage ( run, delete, add ) a job if he has not
got sa permission?
Thanks
AlexAlex,
Yes if the user is a member of the SYSADMIN fixed server role. Note however
that membership of this role should be limited as the role enables a user to
do pretty much anything in SQL Server.
HTH
Jerry
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> Is there a way for a user to manage ( run, delete, add ) a job if he has
> not
> got sa permission?
> Thanks
> Alex|||Thanks for this. But the problem we have is SOX compliance !. Us dba's have
been stripped off sa/SYSADMIN fixed role permission so we can't modify data.
The side effect of this is that there are loads of admin tasks we cannot now
do.
Alex
"Jerry Spivey" wrote:
> Alex,
> Yes if the user is a member of the SYSADMIN fixed server role. Note however
> that membership of this role should be limited as the role enables a user to
> do pretty much anything in SQL Server.
> HTH
> Jerry
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> > Is there a way for a user to manage ( run, delete, add ) a job if he has
> > not
> > got sa permission?
> >
> > Thanks
> >
> > Alex
>
>|||Hmmm...that doesn't sound right. You're a DBA but you're not a member of
the SYSADMIN fixed server role? How can you do your job?
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
> Thanks for this. But the problem we have is SOX compliance !. Us dba's
> have
> been stripped off sa/SYSADMIN fixed role permission so we can't modify
> data.
> The side effect of this is that there are loads of admin tasks we cannot
> now
> do.
> Alex
>
> "Jerry Spivey" wrote:
>> Alex,
>> Yes if the user is a member of the SYSADMIN fixed server role. Note
>> however
>> that membership of this role should be limited as the role enables a user
>> to
>> do pretty much anything in SQL Server.
>> HTH
>> Jerry
>> "Alex" <Alex@.discussions.microsoft.com> wrote in message
>> news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
>> > Is there a way for a user to manage ( run, delete, add ) a job if he
>> > has
>> > not
>> > got sa permission?
>> >
>> > Thanks
>> >
>> > Alex
>>|||Since we don't want SA running jobs (or being in the Sysadmin group), I set
up a login with win authent, sysadmin to run jobs. No one needs the password
to use; just set this user up as the proxy account (under agent properties,
job system), and if any job owner is not a sysadmin, it defaults to this guy.
HTH, mary
"Alex" wrote:
> Thanks for this. But the problem we have is SOX compliance !. Us dba's have
> been stripped off sa/SYSADMIN fixed role permission so we can't modify data.
> The side effect of this is that there are loads of admin tasks we cannot now
> do.
> Alex
>
> "Jerry Spivey" wrote:
> > Alex,
> >
> > Yes if the user is a member of the SYSADMIN fixed server role. Note however
> > that membership of this role should be limited as the role enables a user to
> > do pretty much anything in SQL Server.
> >
> > HTH
> >
> > Jerry
> > "Alex" <Alex@.discussions.microsoft.com> wrote in message
> > news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> > > Is there a way for a user to manage ( run, delete, add ) a job if he has
> > > not
> > > got sa permission?
> > >
> > > Thanks
> > >
> > > Alex
> >
> >
> >|||Yes, try telling this to the auditors!
We are very frustrated. If we need to add a job we will need to apply for
permission to log in as sa, after going through multilevel of sign off from
the senior management - all for running a SQL Profile, or creating
maintenance plan !!
and then all actions will be be logged as well.
Alex
"Jerry Spivey" wrote:
> Hmmm...that doesn't sound right. You're a DBA but you're not a member of
> the SYSADMIN fixed server role? How can you do your job?
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
> > Thanks for this. But the problem we have is SOX compliance !. Us dba's
> > have
> > been stripped off sa/SYSADMIN fixed role permission so we can't modify
> > data.
> > The side effect of this is that there are loads of admin tasks we cannot
> > now
> > do.
> >
> > Alex
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Alex,
> >>
> >> Yes if the user is a member of the SYSADMIN fixed server role. Note
> >> however
> >> that membership of this role should be limited as the role enables a user
> >> to
> >> do pretty much anything in SQL Server.
> >>
> >> HTH
> >>
> >> Jerry
> >> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> >> news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> >> > Is there a way for a user to manage ( run, delete, add ) a job if he
> >> > has
> >> > not
> >> > got sa permission?
> >> >
> >> > Thanks
> >> >
> >> > Alex
> >>
> >>
> >>
>
>|||Curious, if the is a reason to suddenly restore a db in the middle of the day
so production activities can resume, is the same process to log in as SA
required?
--
ChrisR
"Alex" wrote:
> Yes, try telling this to the auditors!
> We are very frustrated. If we need to add a job we will need to apply for
> permission to log in as sa, after going through multilevel of sign off from
> the senior management - all for running a SQL Profile, or creating
> maintenance plan !!
> and then all actions will be be logged as well.
> Alex
> "Jerry Spivey" wrote:
> > Hmmm...that doesn't sound right. You're a DBA but you're not a member of
> > the SYSADMIN fixed server role? How can you do your job?
> > "Alex" <Alex@.discussions.microsoft.com> wrote in message
> > news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
> > > Thanks for this. But the problem we have is SOX compliance !. Us dba's
> > > have
> > > been stripped off sa/SYSADMIN fixed role permission so we can't modify
> > > data.
> > > The side effect of this is that there are loads of admin tasks we cannot
> > > now
> > > do.
> > >
> > > Alex
> > >
> > >
> > > "Jerry Spivey" wrote:
> > >
> > >> Alex,
> > >>
> > >> Yes if the user is a member of the SYSADMIN fixed server role. Note
> > >> however
> > >> that membership of this role should be limited as the role enables a user
> > >> to
> > >> do pretty much anything in SQL Server.
> > >>
> > >> HTH
> > >>
> > >> Jerry
> > >> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> > >> news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> > >> > Is there a way for a user to manage ( run, delete, add ) a job if he
> > >> > has
> > >> > not
> > >> > got sa permission?
> > >> >
> > >> > Thanks
> > >> >
> > >> > Alex
> > >>
> > >>
> > >>
> >
> >
> >|||I don't recall anything in SOX that states that a DBA can not have sa
rights. Someone is taking the wording way beyond it's intent. SQL2005 will
have roles specifically for this but in 2000 it is not that easy.
--
Andrew J. Kelly SQL MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
> Thanks for this. But the problem we have is SOX compliance !. Us dba's
> have
> been stripped off sa/SYSADMIN fixed role permission so we can't modify
> data.
> The side effect of this is that there are loads of admin tasks we cannot
> now
> do.
> Alex
>
> "Jerry Spivey" wrote:
>> Alex,
>> Yes if the user is a member of the SYSADMIN fixed server role. Note
>> however
>> that membership of this role should be limited as the role enables a user
>> to
>> do pretty much anything in SQL Server.
>> HTH
>> Jerry
>> "Alex" <Alex@.discussions.microsoft.com> wrote in message
>> news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
>> > Is there a way for a user to manage ( run, delete, add ) a job if he
>> > has
>> > not
>> > got sa permission?
>> >
>> > Thanks
>> >
>> > Alex
>>|||Thanks Mary. This could be the escape route we have been looking for!
Alex
"Mary" wrote:
> Since we don't want SA running jobs (or being in the Sysadmin group), I set
> up a login with win authent, sysadmin to run jobs. No one needs the password
> to use; just set this user up as the proxy account (under agent properties,
> job system), and if any job owner is not a sysadmin, it defaults to this guy.
> HTH, mary
> "Alex" wrote:
> > Thanks for this. But the problem we have is SOX compliance !. Us dba's have
> > been stripped off sa/SYSADMIN fixed role permission so we can't modify data.
> > The side effect of this is that there are loads of admin tasks we cannot now
> > do.
> >
> > Alex
> >
> >
> > "Jerry Spivey" wrote:
> >
> > > Alex,
> > >
> > > Yes if the user is a member of the SYSADMIN fixed server role. Note however
> > > that membership of this role should be limited as the role enables a user to
> > > do pretty much anything in SQL Server.
> > >
> > > HTH
> > >
> > > Jerry
> > > "Alex" <Alex@.discussions.microsoft.com> wrote in message
> > > news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> > > > Is there a way for a user to manage ( run, delete, add ) a job if he has
> > > > not
> > > > got sa permission?
> > > >
> > > > Thanks
> > > >
> > > > Alex
> > >
> > >
> > >|||Andrew
We can run sa tasks, but we need authorisation every time we need to. its
like working with one of your hands tied.
Alex
"Andrew J. Kelly" wrote:
> I don't recall anything in SOX that states that a DBA can not have sa
> rights. Someone is taking the wording way beyond it's intent. SQL2005 will
> have roles specifically for this but in 2000 it is not that easy.
> --
> Andrew J. Kelly SQL MVP
>
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
> > Thanks for this. But the problem we have is SOX compliance !. Us dba's
> > have
> > been stripped off sa/SYSADMIN fixed role permission so we can't modify
> > data.
> > The side effect of this is that there are loads of admin tasks we cannot
> > now
> > do.
> >
> > Alex
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Alex,
> >>
> >> Yes if the user is a member of the SYSADMIN fixed server role. Note
> >> however
> >> that membership of this role should be limited as the role enables a user
> >> to
> >> do pretty much anything in SQL Server.
> >>
> >> HTH
> >>
> >> Jerry
> >> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> >> news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> >> > Is there a way for a user to manage ( run, delete, add ) a job if he
> >> > has
> >> > not
> >> > got sa permission?
> >> >
> >> > Thanks
> >> >
> >> > Alex
> >>
> >>
> >>
>
>

Non sa running jobs?

Is there a way for a user to manage ( run, delete, add ) a job if he has not
got sa permission?
Thanks
Alex
Alex,
Yes if the user is a member of the SYSADMIN fixed server role. Note however
that membership of this role should be limited as the role enables a user to
do pretty much anything in SQL Server.
HTH
Jerry
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
> Is there a way for a user to manage ( run, delete, add ) a job if he has
> not
> got sa permission?
> Thanks
> Alex
|||Thanks for this. But the problem we have is SOX compliance !. Us dba's have
been stripped off sa/SYSADMIN fixed role permission so we can't modify data.
The side effect of this is that there are loads of admin tasks we cannot now
do.
Alex
"Jerry Spivey" wrote:

> Alex,
> Yes if the user is a member of the SYSADMIN fixed server role. Note however
> that membership of this role should be limited as the role enables a user to
> do pretty much anything in SQL Server.
> HTH
> Jerry
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:3CD1CB0F-42E8-4DA9-A04C-CBD6AE4845CB@.microsoft.com...
>
>
|||Hmmm...that doesn't sound right. You're a DBA but you're not a member of
the SYSADMIN fixed server role? How can you do your job?
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...[vbcol=seagreen]
> Thanks for this. But the problem we have is SOX compliance !. Us dba's
> have
> been stripped off sa/SYSADMIN fixed role permission so we can't modify
> data.
> The side effect of this is that there are loads of admin tasks we cannot
> now
> do.
> Alex
>
> "Jerry Spivey" wrote:
|||Since we don't want SA running jobs (or being in the Sysadmin group), I set
up a login with win authent, sysadmin to run jobs. No one needs the password
to use; just set this user up as the proxy account (under agent properties,
job system), and if any job owner is not a sysadmin, it defaults to this guy.
HTH, mary
"Alex" wrote:
[vbcol=seagreen]
> Thanks for this. But the problem we have is SOX compliance !. Us dba's have
> been stripped off sa/SYSADMIN fixed role permission so we can't modify data.
> The side effect of this is that there are loads of admin tasks we cannot now
> do.
> Alex
>
> "Jerry Spivey" wrote:
|||Yes, try telling this to the auditors!
We are very frustrated. If we need to add a job we will need to apply for
permission to log in as sa, after going through multilevel of sign off from
the senior management - all for running a SQL Profile, or creating
maintenance plan !!
and then all actions will be be logged as well.
Alex
"Jerry Spivey" wrote:

> Hmmm...that doesn't sound right. You're a DBA but you're not a member of
> the SYSADMIN fixed server role? How can you do your job?
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
>
>
|||Curious, if the is a reason to suddenly restore a db in the middle of the day
so production activities can resume, is the same process to log in as SA
required?
ChrisR
"Alex" wrote:
[vbcol=seagreen]
> Yes, try telling this to the auditors!
> We are very frustrated. If we need to add a job we will need to apply for
> permission to log in as sa, after going through multilevel of sign off from
> the senior management - all for running a SQL Profile, or creating
> maintenance plan !!
> and then all actions will be be logged as well.
> Alex
> "Jerry Spivey" wrote:
|||I don't recall anything in SOX that states that a DBA can not have sa
rights. Someone is taking the wording way beyond it's intent. SQL2005 will
have roles specifically for this but in 2000 it is not that easy.
Andrew J. Kelly SQL MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...[vbcol=seagreen]
> Thanks for this. But the problem we have is SOX compliance !. Us dba's
> have
> been stripped off sa/SYSADMIN fixed role permission so we can't modify
> data.
> The side effect of this is that there are loads of admin tasks we cannot
> now
> do.
> Alex
>
> "Jerry Spivey" wrote:
|||Thanks Mary. This could be the escape route we have been looking for!
Alex
"Mary" wrote:
[vbcol=seagreen]
> Since we don't want SA running jobs (or being in the Sysadmin group), I set
> up a login with win authent, sysadmin to run jobs. No one needs the password
> to use; just set this user up as the proxy account (under agent properties,
> job system), and if any job owner is not a sysadmin, it defaults to this guy.
> HTH, mary
> "Alex" wrote:
|||Andrew
We can run sa tasks, but we need authorisation every time we need to. its
like working with one of your hands tied.
Alex
"Andrew J. Kelly" wrote:

> I don't recall anything in SOX that states that a DBA can not have sa
> rights. Someone is taking the wording way beyond it's intent. SQL2005 will
> have roles specifically for this but in 2000 it is not that easy.
> --
> Andrew J. Kelly SQL MVP
>
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:AD536297-1152-4F9E-A36A-88171E6FA033@.microsoft.com...
>
>