I currently have maintenance plans defined to run backups for all DB's. For
one specific DB I have the reovery model set to simple. When the backup runs
for this DB I am receiving the following msg in the log: Backup Failed to
Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
of the database completes successfully. There is not an error code
associated with the message. I have researched with little success. Any
thoughts as to what is occurring?
Thank you...paldba
Have you got it set to backup the transaction log as part of this
maintainence plan? As in simple mode the transaction log cannot be backed up.
"paldba" wrote:
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba
|||No I do not.
"Russell" wrote:
[vbcol=seagreen]
> Have you got it set to backup the transaction log as part of this
> maintainence plan? As in simple mode the transaction log cannot be backed up.
> "paldba" wrote:
|||Seems you have a maint plan where you have included transaction log backups. and one of your
database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba
|||For a database set to simple recovery, you cannot backup the transaction
log - even to truncate the log (which is unnecessary). Attempting to do so
results in an error.
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:DAC6AE7F-260F-4364-8C05-5EF31B7A272F@.microsoft.com...[vbcol=seagreen]
> No I do not.
> "Russell" wrote:
backed up.[vbcol=seagreen]
DB's. For[vbcol=seagreen]
backup runs[vbcol=seagreen]
Failed to[vbcol=seagreen]
backup[vbcol=seagreen]
Any[vbcol=seagreen]
|||Thank you for your response. I have verifed several times that that
maintenance plans are not set up to perform transaction log backups. This
msg just started occurring in the logs after months of running error free.
No changes have been made to the DB or the maintenance plan.
"Tibor Karaszi" wrote:
> Seems you have a maint plan where you have included transaction log backups. and one of your
> database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
> mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
>
>
|||Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
command and you need to hunt that person down and ask why...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...[vbcol=seagreen]
> Thank you for your response. I have verifed several times that that
> maintenance plans are not set up to perform transaction log backups. This
> msg just started occurring in the logs after months of running error free.
> No changes have been made to the DB or the maintenance plan.
> "Tibor Karaszi" wrote:
recovery[vbcol=seagreen]
|||We are running SQL Server 2000. The message that appears in the log is
occurs exactly when the maintenance plan is running. I reran the maintenance
plan a few moments ago and the same message was generated in the log.
"Tibor Karaszi" wrote:
> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
> command and you need to hunt that person down and ask why...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> recovery
>
>
|||Strange. I don't use maint plans myself. I can only assume that is either a bug in maint wiz, or
some really strange design decision.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...[vbcol=seagreen]
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
|||Delete and recreate the plan?
Jeff
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the
maintenance[vbcol=seagreen]
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
Someone is executing this[vbcol=seagreen]
This[vbcol=seagreen]
free.[vbcol=seagreen]
backups. and one of your[vbcol=seagreen]
for a database in simple[vbcol=seagreen]
DB's. For[vbcol=seagreen]
backup runs[vbcol=seagreen]
Failed to[vbcol=seagreen]
The backup[vbcol=seagreen]
code[vbcol=seagreen]
success. Any[vbcol=seagreen]
Showing posts with label truncate. Show all posts
Showing posts with label truncate. Show all posts
Wednesday, March 21, 2012
Dump Transaction With Truncate Only
I currently have maintenance plans defined to run backups for all DB's. For
one specific DB I have the reovery model set to simple. When the backup runs
for this DB I am receiving the following msg in the log: Backup Failed to
Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
of the database completes successfully. There is not an error code
associated with the message. I have researched with little success. Any
thoughts as to what is occurring?
Thank you...paldbaHave you got it set to backup the transaction log as part of this
maintainence plan? As in simple mode the transaction log cannot be backed up.
"paldba" wrote:
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba|||No I do not.
"Russell" wrote:
> Have you got it set to backup the transaction log as part of this
> maintainence plan? As in simple mode the transaction log cannot be backed up.
> "paldba" wrote:
> > I currently have maintenance plans defined to run backups for all DB's. For
> > one specific DB I have the reovery model set to simple. When the backup runs
> > for this DB I am receiving the following msg in the log: Backup Failed to
> > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > of the database completes successfully. There is not an error code
> > associated with the message. I have researched with little success. Any
> > thoughts as to what is occurring?
> >
> > Thank you...paldba|||Seems you have a maint plan where you have included transaction log backups. and one of your
database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba|||For a database set to simple recovery, you cannot backup the transaction
log - even to truncate the log (which is unnecessary). Attempting to do so
results in an error.
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:DAC6AE7F-260F-4364-8C05-5EF31B7A272F@.microsoft.com...
> No I do not.
> "Russell" wrote:
> > Have you got it set to backup the transaction log as part of this
> > maintainence plan? As in simple mode the transaction log cannot be
backed up.
> >
> > "paldba" wrote:
> >
> > > I currently have maintenance plans defined to run backups for all
DB's. For
> > > one specific DB I have the reovery model set to simple. When the
backup runs
> > > for this DB I am receiving the following msg in the log: Backup
Failed to
> > > Complete the Command Dump Transaction xxxx With Truncate Only'. The
backup
> > > of the database completes successfully. There is not an error code
> > > associated with the message. I have researched with little success.
Any
> > > thoughts as to what is occurring?
> > >
> > > Thank you...paldba|||Thank you for your response. I have verifed several times that that
maintenance plans are not set up to perform transaction log backups. This
msg just started occurring in the logs after months of running error free.
No changes have been made to the DB or the maintenance plan.
"Tibor Karaszi" wrote:
> Seems you have a maint plan where you have included transaction log backups. and one of your
> database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
> mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > I currently have maintenance plans defined to run backups for all DB's. For
> > one specific DB I have the reovery model set to simple. When the backup runs
> > for this DB I am receiving the following msg in the log: Backup Failed to
> > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > of the database completes successfully. There is not an error code
> > associated with the message. I have researched with little success. Any
> > thoughts as to what is occurring?
> >
> > Thank you...paldba
>
>|||Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
command and you need to hunt that person down and ask why...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> Thank you for your response. I have verifed several times that that
> maintenance plans are not set up to perform transaction log backups. This
> msg just started occurring in the logs after months of running error free.
> No changes have been made to the DB or the maintenance plan.
> "Tibor Karaszi" wrote:
> > Seems you have a maint plan where you have included transaction log backups. and one of your
> > database is in simple recovery mode. You cannot perform log backup for a database in simple
recovery
> > mode.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > I currently have maintenance plans defined to run backups for all DB's. For
> > > one specific DB I have the reovery model set to simple. When the backup runs
> > > for this DB I am receiving the following msg in the log: Backup Failed to
> > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > > of the database completes successfully. There is not an error code
> > > associated with the message. I have researched with little success. Any
> > > thoughts as to what is occurring?
> > >
> > > Thank you...paldba
> >
> >
> >|||We are running SQL Server 2000. The message that appears in the log is
occurs exactly when the maintenance plan is running. I reran the maintenance
plan a few moments ago and the same message was generated in the log.
"Tibor Karaszi" wrote:
> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
> command and you need to hunt that person down and ask why...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > Thank you for your response. I have verifed several times that that
> > maintenance plans are not set up to perform transaction log backups. This
> > msg just started occurring in the logs after months of running error free.
> > No changes have been made to the DB or the maintenance plan.
> >
> > "Tibor Karaszi" wrote:
> >
> > > Seems you have a maint plan where you have included transaction log backups. and one of your
> > > database is in simple recovery mode. You cannot perform log backup for a database in simple
> recovery
> > > mode.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > I currently have maintenance plans defined to run backups for all DB's. For
> > > > one specific DB I have the reovery model set to simple. When the backup runs
> > > > for this DB I am receiving the following msg in the log: Backup Failed to
> > > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > > > of the database completes successfully. There is not an error code
> > > > associated with the message. I have researched with little success. Any
> > > > thoughts as to what is occurring?
> > > >
> > > > Thank you...paldba
> > >
> > >
> > >
>
>|||Strange. I don't use maint plans myself. I can only assume that is either a bug in maint wiz, or
some really strange design decision.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
>> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
>> command and you need to hunt that person down and ask why...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "paldba" <paldba@.discussions.microsoft.com> wrote in message
>> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
>> > Thank you for your response. I have verifed several times that that
>> > maintenance plans are not set up to perform transaction log backups. This
>> > msg just started occurring in the logs after months of running error free.
>> > No changes have been made to the DB or the maintenance plan.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > Seems you have a maint plan where you have included transaction log backups. and one of your
>> > > database is in simple recovery mode. You cannot perform log backup for a database in simple
>> recovery
>> > > mode.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
>> > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
>> > > > I currently have maintenance plans defined to run backups for all DB's. For
>> > > > one specific DB I have the reovery model set to simple. When the backup runs
>> > > > for this DB I am receiving the following msg in the log: Backup Failed to
>> > > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
>> > > > of the database completes successfully. There is not an error code
>> > > > associated with the message. I have researched with little success. Any
>> > > > thoughts as to what is occurring?
>> > > >
>> > > > Thank you...paldba
>> > >
>> > >
>> > >
>>|||Delete and recreate the plan?
Jeff
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the
maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
> > Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server?
Someone is executing this
> > command and you need to hunt that person down and ask why...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > > Thank you for your response. I have verifed several times that that
> > > maintenance plans are not set up to perform transaction log backups.
This
> > > msg just started occurring in the logs after months of running error
free.
> > > No changes have been made to the DB or the maintenance plan.
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Seems you have a maint plan where you have included transaction log
backups. and one of your
> > > > database is in simple recovery mode. You cannot perform log backup
for a database in simple
> > recovery
> > > > mode.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > > I currently have maintenance plans defined to run backups for all
DB's. For
> > > > > one specific DB I have the reovery model set to simple. When the
backup runs
> > > > > for this DB I am receiving the following msg in the log: Backup
Failed to
> > > > > Complete the Command Dump Transaction xxxx With Truncate Only'.
The backup
> > > > > of the database completes successfully. There is not an error
code
> > > > > associated with the message. I have researched with little
success. Any
> > > > > thoughts as to what is occurring?
> > > > >
> > > > > Thank you...paldba
> > > >
> > > >
> > > >
> >
> >
> >|||Resolved. I deleted and redefined the plan exactly as it was and the msg's
have mysteriously disappeared. Thanks for the suggestion. Since there
weren't any changes to the maintenance plan, I didn't think to delete it.
Thanks much!
"Jeff Dillon" wrote:
> Delete and recreate the plan?
> Jeff
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> > We are running SQL Server 2000. The message that appears in the log is
> > occurs exactly when the maintenance plan is running. I reran the
> maintenance
> > plan a few moments ago and the same message was generated in the log.
> >
> > "Tibor Karaszi" wrote:
> >
> > > Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server?
> Someone is executing this
> > > command and you need to hunt that person down and ask why...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > > > Thank you for your response. I have verifed several times that that
> > > > maintenance plans are not set up to perform transaction log backups.
> This
> > > > msg just started occurring in the logs after months of running error
> free.
> > > > No changes have been made to the DB or the maintenance plan.
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > > > Seems you have a maint plan where you have included transaction log
> backups. and one of your
> > > > > database is in simple recovery mode. You cannot perform log backup
> for a database in simple
> > > recovery
> > > > > mode.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > > > I currently have maintenance plans defined to run backups for all
> DB's. For
> > > > > > one specific DB I have the reovery model set to simple. When the
> backup runs
> > > > > > for this DB I am receiving the following msg in the log: Backup
> Failed to
> > > > > > Complete the Command Dump Transaction xxxx With Truncate Only'.
> The backup
> > > > > > of the database completes successfully. There is not an error
> code
> > > > > > associated with the message. I have researched with little
> success. Any
> > > > > > thoughts as to what is occurring?
> > > > > >
> > > > > > Thank you...paldba
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
one specific DB I have the reovery model set to simple. When the backup runs
for this DB I am receiving the following msg in the log: Backup Failed to
Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
of the database completes successfully. There is not an error code
associated with the message. I have researched with little success. Any
thoughts as to what is occurring?
Thank you...paldbaHave you got it set to backup the transaction log as part of this
maintainence plan? As in simple mode the transaction log cannot be backed up.
"paldba" wrote:
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba|||No I do not.
"Russell" wrote:
> Have you got it set to backup the transaction log as part of this
> maintainence plan? As in simple mode the transaction log cannot be backed up.
> "paldba" wrote:
> > I currently have maintenance plans defined to run backups for all DB's. For
> > one specific DB I have the reovery model set to simple. When the backup runs
> > for this DB I am receiving the following msg in the log: Backup Failed to
> > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > of the database completes successfully. There is not an error code
> > associated with the message. I have researched with little success. Any
> > thoughts as to what is occurring?
> >
> > Thank you...paldba|||Seems you have a maint plan where you have included transaction log backups. and one of your
database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> I currently have maintenance plans defined to run backups for all DB's. For
> one specific DB I have the reovery model set to simple. When the backup runs
> for this DB I am receiving the following msg in the log: Backup Failed to
> Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> of the database completes successfully. There is not an error code
> associated with the message. I have researched with little success. Any
> thoughts as to what is occurring?
> Thank you...paldba|||For a database set to simple recovery, you cannot backup the transaction
log - even to truncate the log (which is unnecessary). Attempting to do so
results in an error.
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:DAC6AE7F-260F-4364-8C05-5EF31B7A272F@.microsoft.com...
> No I do not.
> "Russell" wrote:
> > Have you got it set to backup the transaction log as part of this
> > maintainence plan? As in simple mode the transaction log cannot be
backed up.
> >
> > "paldba" wrote:
> >
> > > I currently have maintenance plans defined to run backups for all
DB's. For
> > > one specific DB I have the reovery model set to simple. When the
backup runs
> > > for this DB I am receiving the following msg in the log: Backup
Failed to
> > > Complete the Command Dump Transaction xxxx With Truncate Only'. The
backup
> > > of the database completes successfully. There is not an error code
> > > associated with the message. I have researched with little success.
Any
> > > thoughts as to what is occurring?
> > >
> > > Thank you...paldba|||Thank you for your response. I have verifed several times that that
maintenance plans are not set up to perform transaction log backups. This
msg just started occurring in the logs after months of running error free.
No changes have been made to the DB or the maintenance plan.
"Tibor Karaszi" wrote:
> Seems you have a maint plan where you have included transaction log backups. and one of your
> database is in simple recovery mode. You cannot perform log backup for a database in simple recovery
> mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > I currently have maintenance plans defined to run backups for all DB's. For
> > one specific DB I have the reovery model set to simple. When the backup runs
> > for this DB I am receiving the following msg in the log: Backup Failed to
> > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > of the database completes successfully. There is not an error code
> > associated with the message. I have researched with little success. Any
> > thoughts as to what is occurring?
> >
> > Thank you...paldba
>
>|||Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
command and you need to hunt that person down and ask why...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> Thank you for your response. I have verifed several times that that
> maintenance plans are not set up to perform transaction log backups. This
> msg just started occurring in the logs after months of running error free.
> No changes have been made to the DB or the maintenance plan.
> "Tibor Karaszi" wrote:
> > Seems you have a maint plan where you have included transaction log backups. and one of your
> > database is in simple recovery mode. You cannot perform log backup for a database in simple
recovery
> > mode.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > I currently have maintenance plans defined to run backups for all DB's. For
> > > one specific DB I have the reovery model set to simple. When the backup runs
> > > for this DB I am receiving the following msg in the log: Backup Failed to
> > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > > of the database completes successfully. There is not an error code
> > > associated with the message. I have researched with little success. Any
> > > thoughts as to what is occurring?
> > >
> > > Thank you...paldba
> >
> >
> >|||We are running SQL Server 2000. The message that appears in the log is
occurs exactly when the maintenance plan is running. I reran the maintenance
plan a few moments ago and the same message was generated in the log.
"Tibor Karaszi" wrote:
> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
> command and you need to hunt that person down and ask why...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > Thank you for your response. I have verifed several times that that
> > maintenance plans are not set up to perform transaction log backups. This
> > msg just started occurring in the logs after months of running error free.
> > No changes have been made to the DB or the maintenance plan.
> >
> > "Tibor Karaszi" wrote:
> >
> > > Seems you have a maint plan where you have included transaction log backups. and one of your
> > > database is in simple recovery mode. You cannot perform log backup for a database in simple
> recovery
> > > mode.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > I currently have maintenance plans defined to run backups for all DB's. For
> > > > one specific DB I have the reovery model set to simple. When the backup runs
> > > > for this DB I am receiving the following msg in the log: Backup Failed to
> > > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
> > > > of the database completes successfully. There is not an error code
> > > > associated with the message. I have researched with little success. Any
> > > > thoughts as to what is occurring?
> > > >
> > > > Thank you...paldba
> > >
> > >
> > >
>
>|||Strange. I don't use maint plans myself. I can only assume that is either a bug in maint wiz, or
some really strange design decision.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
>> Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server? Someone is executing this
>> command and you need to hunt that person down and ask why...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "paldba" <paldba@.discussions.microsoft.com> wrote in message
>> news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
>> > Thank you for your response. I have verifed several times that that
>> > maintenance plans are not set up to perform transaction log backups. This
>> > msg just started occurring in the logs after months of running error free.
>> > No changes have been made to the DB or the maintenance plan.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > Seems you have a maint plan where you have included transaction log backups. and one of your
>> > > database is in simple recovery mode. You cannot perform log backup for a database in simple
>> recovery
>> > > mode.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
>> > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
>> > > > I currently have maintenance plans defined to run backups for all DB's. For
>> > > > one specific DB I have the reovery model set to simple. When the backup runs
>> > > > for this DB I am receiving the following msg in the log: Backup Failed to
>> > > > Complete the Command Dump Transaction xxxx With Truncate Only'. The backup
>> > > > of the database completes successfully. There is not an error code
>> > > > associated with the message. I have researched with little success. Any
>> > > > thoughts as to what is occurring?
>> > > >
>> > > > Thank you...paldba
>> > >
>> > >
>> > >
>>|||Delete and recreate the plan?
Jeff
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> We are running SQL Server 2000. The message that appears in the log is
> occurs exactly when the maintenance plan is running. I reran the
maintenance
> plan a few moments ago and the same message was generated in the log.
> "Tibor Karaszi" wrote:
> > Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server?
Someone is executing this
> > command and you need to hunt that person down and ask why...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > > Thank you for your response. I have verifed several times that that
> > > maintenance plans are not set up to perform transaction log backups.
This
> > > msg just started occurring in the logs after months of running error
free.
> > > No changes have been made to the DB or the maintenance plan.
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Seems you have a maint plan where you have included transaction log
backups. and one of your
> > > > database is in simple recovery mode. You cannot perform log backup
for a database in simple
> > recovery
> > > > mode.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > > I currently have maintenance plans defined to run backups for all
DB's. For
> > > > > one specific DB I have the reovery model set to simple. When the
backup runs
> > > > > for this DB I am receiving the following msg in the log: Backup
Failed to
> > > > > Complete the Command Dump Transaction xxxx With Truncate Only'.
The backup
> > > > > of the database completes successfully. There is not an error
code
> > > > > associated with the message. I have researched with little
success. Any
> > > > > thoughts as to what is occurring?
> > > > >
> > > > > Thank you...paldba
> > > >
> > > >
> > > >
> >
> >
> >|||Resolved. I deleted and redefined the plan exactly as it was and the msg's
have mysteriously disappeared. Thanks for the suggestion. Since there
weren't any changes to the maintenance plan, I didn't think to delete it.
Thanks much!
"Jeff Dillon" wrote:
> Delete and recreate the plan?
> Jeff
> "paldba" <paldba@.discussions.microsoft.com> wrote in message
> news:87B7E14E-EB90-433B-8F26-FDED7E1FD1A4@.microsoft.com...
> > We are running SQL Server 2000. The message that appears in the log is
> > occurs exactly when the maintenance plan is running. I reran the
> maintenance
> > plan a few moments ago and the same message was generated in the log.
> >
> > "Tibor Karaszi" wrote:
> >
> > > Ahh, I missed the WITH TRUNCATE_ONLY part. What version of SQL server?
> Someone is executing this
> > > command and you need to hunt that person down and ask why...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > news:1AFC4C74-927F-40DB-AD0C-D002E76DF3C7@.microsoft.com...
> > > > Thank you for your response. I have verifed several times that that
> > > > maintenance plans are not set up to perform transaction log backups.
> This
> > > > msg just started occurring in the logs after months of running error
> free.
> > > > No changes have been made to the DB or the maintenance plan.
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > > > Seems you have a maint plan where you have included transaction log
> backups. and one of your
> > > > > database is in simple recovery mode. You cannot perform log backup
> for a database in simple
> > > recovery
> > > > > mode.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "paldba" <paldba@.discussions.microsoft.com> wrote in message
> > > > > news:EF18B7A5-F0CA-4925-8306-D7317C474B58@.microsoft.com...
> > > > > > I currently have maintenance plans defined to run backups for all
> DB's. For
> > > > > > one specific DB I have the reovery model set to simple. When the
> backup runs
> > > > > > for this DB I am receiving the following msg in the log: Backup
> Failed to
> > > > > > Complete the Command Dump Transaction xxxx With Truncate Only'.
> The backup
> > > > > > of the database completes successfully. There is not an error
> code
> > > > > > associated with the message. I have researched with little
> success. Any
> > > > > > thoughts as to what is occurring?
> > > > > >
> > > > > > Thank you...paldba
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
Wednesday, February 15, 2012
DTS to truncate excel and load fresh data
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve thisCan you try again?
I'm not sure I follow you...
Can you put the steps you do down in bullets...and what the final; result would be
1. I DTS in to a table
2. ect
I expect: x|||You want to "truncate" the records in the excel spreadsheet... correct ?|||1 - I truncate TableA in SQL
2 - I select few fields from TableB and insert into TableA
3 - I select everything from TableA and extract it to .xls (here i need to clear all fields in this .xls if there was any so that I can populate this .xls with new results)|||Thanks I got the answer. One more problem, when I drop the .xls table everytime I create it again and load it with data then it leave cloums which had last data and put data on the last line. Lets say I had 4 lines before and dropped the table and reated it again, and load data, it will load data from the 5th line onwards, how can I resolve this|||I'd like to know how you managed to truncate your Excel file. When I execute my DTS package, data keeps getting added to the Excel file, appended on to existing data. I want to wipe everything clean first. How do you do it?|||I'm also facing the same kind of situation..plz let me know if you've found a solution|||I have done this by recreating the Excel spreadsheet. The following VBScript code should do this for you.
'************************************************* *********
' Visual Basic ActiveX Script
'************************************************* *********
Function Main()
Dim oFSO
Dim xlApp
Dim wkbNewBook
Dim strBookName
Const xlNormal = -4143
Const READ_WRITE = -1
' ****************
' Remove existing Excel Workbook
' **************
Set oFSO = CreateObject("Scripting.FileSystemObject")
If Not oFSO.FolderExists("C:\folderName") Then
oFSO.CreateFolder ("C:\folderName")
End If
If oFSO.FileExists("C:\folderName\FileName.xls") Then
oFSO.DeleteFile ("C:\folderName\FileName.xls")
End If
' ****************
' Create new Excel Workbook
' **************
' Create object
Set xlApp = CreateObject("Excel.Application")
' Add new workbook to Workbooks collection.
Set wkbNewBook = xlApp.Workbooks.Add
' Specify path to save workbook.
strBookName = "C:\folderName\FileName.xls"
' ****************
' Format new Excel Workbook
' **************
With wkbNewBook
.Sheets(1).Select
.Sheets(1).Name = "SheetName"
.Sheets(1).Range("A1").FormulaR1C1 = "Field1Name"
.Sheets(1).Range("B1").FormulaR1C1 = "Field2Name"
.Sheets(1).Range("C1").FormulaR1C1 = "Field3Name"
.SaveAs strBookName, xlNormal ,,,READ_WRITE
.Close
End With
Set wkbNewBook = Nothing
Set xlApp = Nothing
End Function
##############################33
Originally posted by msenoelo
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this
I'm not sure I follow you...
Can you put the steps you do down in bullets...and what the final; result would be
1. I DTS in to a table
2. ect
I expect: x|||You want to "truncate" the records in the excel spreadsheet... correct ?|||1 - I truncate TableA in SQL
2 - I select few fields from TableB and insert into TableA
3 - I select everything from TableA and extract it to .xls (here i need to clear all fields in this .xls if there was any so that I can populate this .xls with new results)|||Thanks I got the answer. One more problem, when I drop the .xls table everytime I create it again and load it with data then it leave cloums which had last data and put data on the last line. Lets say I had 4 lines before and dropped the table and reated it again, and load data, it will load data from the 5th line onwards, how can I resolve this|||I'd like to know how you managed to truncate your Excel file. When I execute my DTS package, data keeps getting added to the Excel file, appended on to existing data. I want to wipe everything clean first. How do you do it?|||I'm also facing the same kind of situation..plz let me know if you've found a solution|||I have done this by recreating the Excel spreadsheet. The following VBScript code should do this for you.
'************************************************* *********
' Visual Basic ActiveX Script
'************************************************* *********
Function Main()
Dim oFSO
Dim xlApp
Dim wkbNewBook
Dim strBookName
Const xlNormal = -4143
Const READ_WRITE = -1
' ****************
' Remove existing Excel Workbook
' **************
Set oFSO = CreateObject("Scripting.FileSystemObject")
If Not oFSO.FolderExists("C:\folderName") Then
oFSO.CreateFolder ("C:\folderName")
End If
If oFSO.FileExists("C:\folderName\FileName.xls") Then
oFSO.DeleteFile ("C:\folderName\FileName.xls")
End If
' ****************
' Create new Excel Workbook
' **************
' Create object
Set xlApp = CreateObject("Excel.Application")
' Add new workbook to Workbooks collection.
Set wkbNewBook = xlApp.Workbooks.Add
' Specify path to save workbook.
strBookName = "C:\folderName\FileName.xls"
' ****************
' Format new Excel Workbook
' **************
With wkbNewBook
.Sheets(1).Select
.Sheets(1).Name = "SheetName"
.Sheets(1).Range("A1").FormulaR1C1 = "Field1Name"
.Sheets(1).Range("B1").FormulaR1C1 = "Field2Name"
.Sheets(1).Range("C1").FormulaR1C1 = "Field3Name"
.SaveAs strBookName, xlNormal ,,,READ_WRITE
.Close
End With
Set wkbNewBook = Nothing
Set xlApp = Nothing
End Function
##############################33
Originally posted by msenoelo
I have done DTS that export data from SQL to .xls, it works perfect, my problem is my table from SQL get truncated everytime before i load data but my .xls file always come with previous records which I don't want. i.e. if my Sql table had 3 rows , when i finish to execute the dts, my .xls come with 3 rows, when I exec again, my table get truncated and my .xls add another 3 rows. How can I solve this
Subscribe to:
Posts (Atom)