interface supported: transaction

Showing posts with label transaction. Show all posts

Friday, March 30, 2012

not a valid undo file for database

Hi,
I've restored a database from backup, with STANDBY option.
Now, when I'm trying to apply further Transaction logs to
this database, the restore log command is failing with an
error 'File 'd:\standby.undo' is not a valid undo file for
database 'xyz', database ID 24.
Please let me know how to I avoid this error and restore
logs to this database.
Does the file mentioned in the error message actually exist?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
> Hi,
> I've restored a database from backup, with STANDBY option.
> Now, when I'm trying to apply further Transaction logs to
> this database, the restore log command is failing with an
> error 'File 'd:\standby.undo' is not a valid undo file for
> database 'xyz', database ID 24.
> Please let me know how to I avoid this error and restore
> logs to this database.
|||Yes, it does. And I've tried renaming this file and then
it gives a different error that the file does not exist.
SO, I'm sure that it is able to access this file, but
something is not right in this file.
What's the purpose of this file anyway ?

>--Original Message--
>Does the file mentioned in the error message actually
exist?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
option.[vbcol=seagreen]
to[vbcol=seagreen]
an[vbcol=seagreen]
for
>
>.
>
|||The UNDO file is created when you perform RESTORE using the STANDBY option.
This is because SQL Server will actually perform recovery based on the transaction log when you are
using STANDBY, but as you say you want to be able to perform additional restores, SQL Server will
save the recovery work it performs in this undo file so it can undo the recovery work when you do
the next restore.
SQL Server will remember the name of the undo file so it will automatically find it when next
restore is performed. In this case, SQL Server doesn't recognize the undo file as a valid file.
Perhaps someone deleted the file and just created one through notepad, or picked some other UNDO
file and renamed it? Bottom-line is that SLQ Server *need* a valid UNDO file for the next restore.
You can always re-start all restores from the latest database backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:2abc01c4ab08$347ecdb0$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes, it does. And I've tried renaming this file and then
> it gives a different error that the file does not exist.
> SO, I'm sure that it is able to access this file, but
> something is not right in this file.
> What's the purpose of this file anyway ?
> exist?
> message
> option.
> to
> an
> for
sql

not a valid undo file for database

Hi,
I've restored a database from backup, with STANDBY option.
Now, when I'm trying to apply further Transaction logs to
this database, the restore log command is failing with an
error 'File 'd:\standby.undo' is not a valid undo file for
database 'xyz', database ID 24.
Please let me know how to I avoid this error and restore
logs to this database.Does the file mentioned in the error message actually exist?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
> Hi,
> I've restored a database from backup, with STANDBY option.
> Now, when I'm trying to apply further Transaction logs to
> this database, the restore log command is failing with an
> error 'File 'd:\standby.undo' is not a valid undo file for
> database 'xyz', database ID 24.
> Please let me know how to I avoid this error and restore
> logs to this database.|||Yes, it does. And I've tried renaming this file and then
it gives a different error that the file does not exist.
SO, I'm sure that it is able to access this file, but
something is not right in this file.
What's the purpose of this file anyway ?
>--Original Message--
>Does the file mentioned in the error message actually
exist?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
message
>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
>> Hi,
>> I've restored a database from backup, with STANDBY
option.
>> Now, when I'm trying to apply further Transaction logs
to
>> this database, the restore log command is failing with
an
>> error 'File 'd:\standby.undo' is not a valid undo file
for
>> database 'xyz', database ID 24.
>> Please let me know how to I avoid this error and restore
>> logs to this database.
>
>.
>|||The UNDO file is created when you perform RESTORE using the STANDBY option.
This is because SQL Server will actually perform recovery based on the transaction log when you are
using STANDBY, but as you say you want to be able to perform additional restores, SQL Server will
save the recovery work it performs in this undo file so it can undo the recovery work when you do
the next restore.
SQL Server will remember the name of the undo file so it will automatically find it when next
restore is performed. In this case, SQL Server doesn't recognize the undo file as a valid file.
Perhaps someone deleted the file and just created one through notepad, or picked some other UNDO
file and renamed it? Bottom-line is that SLQ Server *need* a valid UNDO file for the next restore.
You can always re-start all restores from the latest database backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:2abc01c4ab08$347ecdb0$a501280a@.phx.gbl...
> Yes, it does. And I've tried renaming this file and then
> it gives a different error that the file does not exist.
> SO, I'm sure that it is able to access this file, but
> something is not right in this file.
> What's the purpose of this file anyway ?
>>--Original Message--
>>Does the file mentioned in the error message actually
> exist?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
>> Hi,
>> I've restored a database from backup, with STANDBY
> option.
>> Now, when I'm trying to apply further Transaction logs
> to
>> this database, the restore log command is failing with
> an
>> error 'File 'd:\standby.undo' is not a valid undo file
> for
>> database 'xyz', database ID 24.
>> Please let me know how to I avoid this error and restore
>> logs to this database.
>>
>>.

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

Monday, March 12, 2012

non logged transactions with transactional replication

I know that if you're doing log shipping and someone runs a fast bcp ( non
logged ) activity against the database that subsequent transaction log dumps
won't load until you do another full dump and load. This can be a problem,
because of the time involved in dumping and loading. How does transactional
replication handle non logged activity? The same way as log shipping? Which
would be bad, or does the data that was inserted or changed as a result of
the non logged transaction make it to the target server?
John,
it is my understanding that in the full recovery model, every change to the
database is logged, so there's no issue with transactional replication. In
SQL Server 2005 the transaction log may be backed up after minimally logged
operations (eg Bulk load operations in the bulk-logged model) so log
shipping is also possible.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But, if you run a transaction that is truly minimally logged (BCP in
bulk-logged recovery model), you will have to reinitialize.
I don't understand why that is considered "bad". A minimally logged
transaction does not write everything to the tran log. Therefore, if you
were allowed to backup the tran log and restore it, you would wind up with a
corrupted database. With respect to replication, a minimally logged
transaction doesn't even hit the replication engine, so your publisher and
subscriber will completely out of synch and since there is no track of the
data, there is no ability to resynch them except by reinitializing.
(Replication doesn't even detect that a minimally logged transaction
occured.)
This is not an issue for either replication or log shipping. It is a side
effect of the way the transaction was executed. The replication and log
shipping features are simply protecting the integrity of the databases
against something they can not control.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OMFC2BDLGHA.648@.TK2MSFTNGP14.phx.gbl...
> John,
> it is my understanding that in the full recovery model, every change to
> the database is logged, so there's no issue with transactional
> replication. In SQL Server 2005 the transaction log may be backed up after
> minimally logged operations (eg Bulk load operations in the bulk-logged
> model) so log shipping is also possible.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It doesn't handle not logged activity because it is based on logged events
and you can't do non-logged events on tables you are replicating.
Non-logged activity will occur when the bulk copy recovery model is selected
and
1) you do certain operations, i.e. create index, select into, etc
2) fast bcp - which requires no indexes on the table among other things
transactional replication requires a table with an index, so you can't do
fast bcp with it. It also requires publications built on preexisting tables
so you won't be able to replicate a table you are selecting into.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <John@.discussions.microsoft.com> wrote in message
news:59BAC0A1-DE3B-4E1B-A121-0C4E9B78497C@.microsoft.com...
>I know that if you're doing log shipping and someone runs a fast bcp ( non
> logged ) activity against the database that subsequent transaction log
> dumps
> won't load until you do another full dump and load. This can be a problem,
> because of the time involved in dumping and loading. How does
> transactional
> replication handle non logged activity? The same way as log shipping?
> Which
> would be bad, or does the data that was inserted or changed as a result of
> the non logged transaction make it to the target server?
|||Yes, you can in fact perform minimally logged operations on tables that are
being replicated. Writetext and updatetext come to mind.
Also there is no such thing as a "non-logged" event. Every single operation
that manipulates data will place some kind of entry into the log.
Therefore, the correct term is "minimally logged" and it has been for about
a decade now.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eLcsW4vLGHA.1192@.TK2MSFTNGP11.phx.gbl...
> It doesn't handle not logged activity because it is based on logged
> events and you can't do non-logged events on tables you are replicating.
> Non-logged activity will occur when the bulk copy recovery model is
> selected and
> 1) you do certain operations, i.e. create index, select into, etc
> 2) fast bcp - which requires no indexes on the table among other things
> transactional replication requires a table with an index, so you can't do
> fast bcp with it. It also requires publications built on preexisting
> tables so you won't be able to replicate a table you are selecting into.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "John" <John@.discussions.microsoft.com> wrote in message
> news:59BAC0A1-DE3B-4E1B-A121-0C4E9B78497C@.microsoft.com...
>

Saturday, February 25, 2012

No truncate of transaction log during database backup

quote:

> Hello,
> A transaction log on one of my sql 2000 server is growing
> bigger and bigger. During the backup with backupexec 9.1
> the log isn't truncated. I use the option full in the
> option tab of the dbase.
> cheers!
> Kees

No truncate of transaction log during database backup

Hello,
A transaction log on one of my sql 2000 server is growing
bigger and bigger. During the backup with backupexec 9.1
the log isn't truncated. I use the option full in the
option tab of the dbase.
cheers!
KeesFull backups do not truncate inactive log entries. Transaction log backups
in full recovery model will remove inactive portions. Removing inactive
entries will not shrink the log file. There is a separate command for that.
I suggest reading the entire backup and recovery section in BOL (Books
On-Line) as your choice of recovery models has several implications for
database maintenance.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Kees" <anonymous@.discussions.microsoft.com> wrote in message
news:000c01c3dc64$0821f7b0$a001280a@.phx.gbl...
> Hello,
> A transaction log on one of my sql 2000 server is growing
> bigger and bigger. During the backup with backupexec 9.1
> the log isn't truncated. I use the option full in the
> option tab of the dbase.
> cheers!
> Kees|||Are you backing up the transaction log? That is the only
way to control the log size when using the Full recovery
model. If you don't need the transaction log, just
switch to the simple recovery model.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>Hello,
>A transaction log on one of my sql 2000 server is growing
>bigger and bigger. During the backup with backupexec 9.1
>the log isn't truncated. I use the option full in the
>option tab of the dbase.
>cheers!
>Kees
>.
>

Monday, February 20, 2012

No transaction logs

can you manually backup the transaction log from EM?
Keene
>--Original Message--
>I have a maintenance plan set up on a database to take
transaction log backups every 2 hours. The job runs
successfully everyday, but I do not see any .trn files
generated (and the task pad description shows that no
transaction log backups ever took place).
>Any idea why? I've never seen this problem on any of my
other servers.
>Thank you!
>.
>It's SQL Server 7.0 with a database option of Truncate Log on Checkpoint...d
oes that make a difference?|||Gina,
With truncate log on checkpoint set the log file backups will be =
failing. You cannot do a backup log when this option is set as the =
contents of thye log are effectively destroyed at every checkpoint (ie =
every few minutes).
It sounds like you want this option OFF if you require point-in-time =
recovery.
Mike John
"Gina" <anonymous@.discussions.microsoft.com> wrote in message =
news:A4AC132C-1726-494E-AA0C-952A18EBED8E@.microsoft.com...
> It's SQL Server 7.0 with a database option of Truncate Log on =
Checkpoint...does that make a difference?|||Thank you very much!

No transaction logs

I have a maintenance plan set up on a database to take transaction log backu
ps every 2 hours. The job runs successfully everyday, but I do not see any
.trn files generated (and the task pad description shows that no transaction
log backups ever took plac
e).
Any idea why? I've never seen this problem on any of my other servers.
Thank you!I'd start by double checking the job / maint plan definition, the recovery
model for the databases and then if needed run a profiler trace to see
whether the BACKUP LOG command is submitted. Also, make sure you define a
report file for the maint plan and check that report file for error
messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gina" <anonymous@.discussions.microsoft.com> wrote in message
news:01060BCF-B18D-427F-AFB7-86ECE6A8A9DC@.microsoft.com...
> I have a maintenance plan set up on a database to take transaction log
backups every 2 hours. The job runs successfully everyday, but I do not see
any .trn files generated (and the task pad description shows that no
transaction log backups ever took place).
> Any idea why? I've never seen this problem on any of my other servers.
> Thank you!

No Transaction Log Backup after installing SP1

Hello,
after installing SP1 for SQL Server 2005 the sql agent cant execute the
existing management job to backup the transaction log. even if the job is
created new by deleting it in the maintanance plans and recreate it the sql
agent job can not be executed (nor manually or scheduled) the job is always
interrupted in step 2 'Execute Job' with the error "The package execution
failed. The step failed."
Has anyone else an idea to resolve this problem or should i uninstall sp1
(=reinstall sql)
ThanksThe question is WHY is the step failing. There should be extended error
messages in the log files that are generated for the job execution. What
are those messages saying?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Sorcerer" <Sorcerer@.discussions.microsoft.com> wrote in message
news:52149920-7656-4419-A9C2-CA4A641EF232@.microsoft.com...
> Hello,
> after installing SP1 for SQL Server 2005 the sql agent cant execute the
> existing management job to backup the transaction log. even if the job is
> created new by deleting it in the maintanance plans and recreate it the
> sql
> agent job can not be executed (nor manually or scheduled) the job is
> always
> interrupted in step 2 'Execute Job' with the error "The package execution
> failed. The step failed."
> Has anyone else an idea to resolve this problem or should i uninstall sp1
> (=reinstall sql)
> Thanks
>|||Hello,
this is what is written to the log:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
04/21/2006 08:20:00,TransactionLog
Sicherung,Error,0,RMCNAVI1\INSTANZ1,Tran
sactionLog Sicherung,(Job
outcome),,The job failed. The Job was invoked by Schedule 9 (20). The last
step to run was step 1 (Subplan).,00:00:11,0,0,,,,0
04/21/2006 08:20:00,TransactionLog
Sicherung,Error,1,RMCNAVI1\INSTANZ1,Tran
sactionLog
Sicherung,Subplan,,Executed as user: RMC-DE\cluster. The package execution
failed. The step failed.,00:00:11,0,0,,,,0
To be more specific to the problem:
the sp1 was installed on a cluster system (windows 2003 x64 R2 and sql 2005
x64) with three instances (two clustered and one local). after installing th
e
sp1 the agent job could not be executed any longer. on a test system (not
clustered, only one instance) the sp1 was installed and there is no problem
with the agent jobs...|||my fault: forgot to make an initial full backup of the database.
sorry for that.
"Sorcerer" wrote:

> Hello,
> after installing SP1 for SQL Server 2005 the sql agent cant execute the
> existing management job to backup the transaction log. even if the job is
> created new by deleting it in the maintanance plans and recreate it the sq
l
> agent job can not be executed (nor manually or scheduled) the job is alway
s
> interrupted in step 2 'Execute Job' with the error "The package execution
> failed. The step failed."
> Has anyone else an idea to resolve this problem or should i uninstall sp1
> (=reinstall sql)
> Thanks
>

No Transaction Log Backup after installing SP1

Hello,
after installing SP1 for SQL Server 2005 the sql agent cant execute the
existing management job to backup the transaction log. even if the job is
created new by deleting it in the maintanance plans and recreate it the sql
agent job can not be executed (nor manually or scheduled) the job is always
interrupted in step 2 'Execute Job' with the error "The package execution
failed. The step failed."
Has anyone else an idea to resolve this problem or should i uninstall sp1
(=reinstall sql)
ThanksThe question is WHY is the step failing. There should be extended error
messages in the log files that are generated for the job execution. What
are those messages saying?
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Sorcerer" <Sorcerer@.discussions.microsoft.com> wrote in message
news:52149920-7656-4419-A9C2-CA4A641EF232@.microsoft.com...
> Hello,
> after installing SP1 for SQL Server 2005 the sql agent cant execute the
> existing management job to backup the transaction log. even if the job is
> created new by deleting it in the maintanance plans and recreate it the
> sql
> agent job can not be executed (nor manually or scheduled) the job is
> always
> interrupted in step 2 'Execute Job' with the error "The package execution
> failed. The step failed."
> Has anyone else an idea to resolve this problem or should i uninstall sp1
> (=reinstall sql)
> Thanks
>|||Hello,
this is what is written to the log:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
04/21/2006 08:20:00,TransactionLog
Sicherung,Error,0,RMCNAVI1\INSTANZ1,TransactionLog Sicherung,(Job
outcome),,The job failed. The Job was invoked by Schedule 9 (20). The last
step to run was step 1 (Subplan).,00:00:11,0,0,,,,0
04/21/2006 08:20:00,TransactionLog
Sicherung,Error,1,RMCNAVI1\INSTANZ1,TransactionLog
Sicherung,Subplan,,Executed as user: RMC-DE\cluster. The package execution
failed. The step failed.,00:00:11,0,0,,,,0
To be more specific to the problem:
the sp1 was installed on a cluster system (windows 2003 x64 R2 and sql 2005
x64) with three instances (two clustered and one local). after installing the
sp1 the agent job could not be executed any longer. on a test system (not
clustered, only one instance) the sp1 was installed and there is no problem
with the agent jobs...|||my fault: forgot to make an initial full backup of the database.
sorry for that.
"Sorcerer" wrote:
> Hello,
> after installing SP1 for SQL Server 2005 the sql agent cant execute the
> existing management job to backup the transaction log. even if the job is
> created new by deleting it in the maintanance plans and recreate it the sql
> agent job can not be executed (nor manually or scheduled) the job is always
> interrupted in step 2 'Execute Job' with the error "The package execution
> failed. The step failed."
> Has anyone else an idea to resolve this problem or should i uninstall sp1
> (=reinstall sql)
> Thanks
>

No Transaction is Active

Hi,
I have a COM+ Application written by somebody else, and when I am calling
one function, it is returning an "No Transaction Is Active". I have looked a
t
the code, and cannot see why. I have the same version of the dll running
perfectly fine on other servers, so I cannot see that it is the code that is
at fault, so I am left to believe that it is being returned by SQL Server
(SQL Server 2000, SP4).
Can anyone help explain what the error message means and why it is coming up
?
Regards
SamHi
Are you getting error numbers 3902/3903? In which case you are trying to
ROLLBACK/COMMIT without a BEGIN TRANSACTION. You may want to use SQL Profile
r
to see where this is occuring.
e.g. instances such as
http://support.microsoft.com/defaul...kb;en-us;231987
http://support.microsoft.com/defaul...kb;en-us;223213
John
"Sam Coleman" wrote:

> Hi,
> I have a COM+ Application written by somebody else, and when I am calling
> one function, it is returning an "No Transaction Is Active". I have looked
at
> the code, and cannot see why. I have the same version of the dll running
> perfectly fine on other servers, so I cannot see that it is the code that
is
> at fault, so I am left to believe that it is being returned by SQL Server
> (SQL Server 2000, SP4).
> Can anyone help explain what the error message means and why it is coming
up?
> Regards
> Sam

No transaction is active

I've google'd like a crazy, tried a lot of stuff, but still can't get
it to work:
When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
doesn't:
An UPDATE-trigger on a table calls a stored procedure that selects
data and send them to a linked server.
1.
When running the stored procedure alone, everything is fine and data
is send to linked server (so there is through-put to linked server).
2.
But when run normally and the SP is run via the trigger we get:
OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
56
The operation could not be performed because OLE DB provider "SQLNCLI"
for linked server "WEBDEV1" was unable to begin a distributed
transaction.
Any ideas?
Regards /SnedkerHave you got MSDTC running? MSDTC would be responsible for managing the
inter-server (ie. distributed) transaction.
*mike hodgson*
http://sqlnerd.blogspot.com
Morten Snedker wrote:

>I've google'd like a crazy, tried a lot of stuff, but still can't get
>it to work:
>When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
>doesn't:
>An UPDATE-trigger on a table calls a stored procedure that selects
>data and send them to a linked server.
>1.
>When running the stored procedure alone, everything is fine and data
>is send to linked server (so there is through-put to linked server).
>2.
>But when run normally and the SP is run via the trigger we get:
>OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
>"No transaction is active.".
>Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
>56
>The operation could not be performed because OLE DB provider "SQLNCLI"
>for linked server "WEBDEV1" was unable to begin a distributed
>transaction.
>Any ideas?
>
>Regards /Snedker
>|||Are these two servers running Windows 2003 Server? By default, MSDTC
is locked down, preventing network connections and anonymous sessions.
You need to modify those settings for SQL to be able to use MSDTC.
http://msdn.microsoft.com/library/d...0676ec97786.asp
Morten Snedker wrote:
> I've google'd like a crazy, tried a lot of stuff, but still can't get
> it to work:
> When run on our old SQL 2000 it worked fine. Now, running SQL 2005 it
> doesn't:
> An UPDATE-trigger on a table calls a stored procedure that selects
> data and send them to a linked server.
> 1.
> When running the stored procedure alone, everything is fine and data
> is send to linked server (so there is through-put to linked server).
> 2.
> But when run normally and the SP is run via the trigger we get:
> OLE DB provider "SQLNCLI" for linked server "WEBDEV1" returned message
> "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure spVENT_AppUpdVentDeltager, Line
> 56
> The operation could not be performed because OLE DB provider "SQLNCLI"
> for linked server "WEBDEV1" was unable to begin a distributed
> transaction.
> Any ideas?
>
> Regards /Snedker|||On Fri, 09 Jun 2006 21:48:49 +1000, Mike Hodgson <e1minst3r@.gmail.com>
wrote:
Yes, it is running.
But I suppose the transaction is the same, whether it is launched from
a stored procedure - or a trigger firing the stored procedure?
/Snedker

>Have you got MSDTC running? MSDTC would be responsible for managing the
>inter-server (ie. distributed) transaction.|||On 9 Jun 2006 05:07:56 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:
As stated previously in this thread, the MSDTC is running.
If it wasn't running I wouldn't be able to run the stored procedure.
That is the essence of the weirdness:
I can insert data to a table on linked server 'WEBEDV1" from the
stored procedure. But when I let the trigger launch the stored
procedure, it doesn't work !?
This is my trigger:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [trgUpdateVentDeltagere]
ON [dbo].[tblVENTTilsagn]
FOR UPDATE
AS
--SET REMOTE_PROC_TRANSACTIONS OFF;
declare @.count int
declare @.journal varchar(50)
declare @.params varchar(512)
-- Find post som er blevet opdateret
select @.count = count(*)
from Inserted i join Deleted d on i.journal =d.journal
if @.count > 0 and (Substring(columns_updated(),1,1) > 0 or
Substring(columns_updated(),2,1) >0)
begin
select @.journal = i.journal from inserted i
exec spVENT_AppUpdVentDeltager @.jnr = @.journal
end
Running spVENT_AppUpdVentDeltager from this trigger fails with error
given in the subject-line.
Running spVENT_AppUpdVentDeltager alone works fine.
>Are these two servers running Windows 2003 Server? By default, MSDTC
>is locked down, preventing network connections and anonymous sessions.
>You need to modify those settings for SQL to be able to use MSDTC.
>http://msdn.microsoft.com/library/d...0676ec97786.asp
>
>Morten Snedker wrote:|||Without knowing what else that sproc does, and based on what you've
given us, I'm still going to suggest that your problem is due to MSDTC
permissions. If your servers are Windows 2003, there are some MSDTC
properties that you must change. If the information provided in the
first link I posted doesn't work, then try this one:
http://support.microsoft.com/defaul...332&Product=sql
Good luck!
Morten Snedker wrote:
> On 9 Jun 2006 05:07:56 -0700, "Tracy McKibben"
> <tracy.mckibben@.gmail.com> wrote:
> As stated previously in this thread, the MSDTC is running.
> If it wasn't running I wouldn't be able to run the stored procedure.
> That is the essence of the weirdness:
> I can insert data to a table on linked server 'WEBEDV1" from the
> stored procedure. But when I let the trigger launch the stored
> procedure, it doesn't work !?
> This is my trigger:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER TRIGGER [trgUpdateVentDeltagere]
> ON [dbo].[tblVENTTilsagn]
> FOR UPDATE
> AS
> --SET REMOTE_PROC_TRANSACTIONS OFF;
> declare @.count int
> declare @.journal varchar(50)
> declare @.params varchar(512)
> -- Find post som er blevet opdateret
> select @.count = count(*)
> from Inserted i join Deleted d on i.journal =d.journal
> if @.count > 0 and (Substring(columns_updated(),1,1) > 0 or
> Substring(columns_updated(),2,1) >0)
> begin
> select @.journal = i.journal from inserted i
> exec spVENT_AppUpdVentDeltager @.jnr = @.journal
> end
> Running spVENT_AppUpdVentDeltager from this trigger fails with error
> given in the subject-line.
> Running spVENT_AppUpdVentDeltager alone works fine.
>|||On 9 Jun 2006 06:24:56 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:
Thanks for your suggestion and your efforts. However, I've been there
already and MSDTC has been set properly.
The solution for now is moving the stored procedure from the trigger
to a client application. Less than optimal, but it works.
Regards /Snedker

>Without knowing what else that sproc does, and based on what you've
>given us, I'm still going to suggest that your problem is due to MSDTC
>permissions. If your servers are Windows 2003, there are some MSDTC
>properties that you must change. If the information provided in the
>first link I posted doesn't work, then try this one:
>http://support.microsoft.com/defaul...332&Product=sql
>Good luck!

Friday, March 30, 2012

not a valid undo file for database

not a valid undo file for database

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

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

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

Monday, March 12, 2012

non logged transactions with transactional replication

Saturday, February 25, 2012

No truncate of transaction log during database backup

No truncate of transaction log during database backup

Monday, February 20, 2012

No transaction logs

No transaction logs

No Transaction Log Backup after installing SP1

No Transaction Log Backup after installing SP1

No Transaction is Active

No transaction is active

interface supported

Blog Archive

About Me