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...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]

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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Dump Transaction Log

Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
C
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default...b;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default...kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C

Dump Transaction Log

Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default.aspx?scid=kb;EN-US;62866
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> Csql

Dump Transaction Log

Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recov
ery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...b;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/defaul...=kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C

DUMP TRANSACTION database WITH NO_LOG

Hello there,
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing dat
a
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. MixterMick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:

> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take m
y
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or large
r
> than the MDF files themselves, and I am concernced with preserving hard di
sk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they point
ed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committe
d
> to the database yet. Am I worrying needlessly? Or am I actually losing d
ata
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer tha
t
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter

DUMP TRANSACTION database WITH NO_LOG

Hello there,
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing data
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. Mixter
Mick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:

> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take my
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or larger
> than the MDF files themselves, and I am concernced with preserving hard disk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they pointed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committed
> to the database yet. Am I worrying needlessly? Or am I actually losing data
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer that
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter

DUMP TRANSACTION database WITH NO_LOG

Hello there,
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing data
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. MixterMick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:
> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take my
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or larger
> than the MDF files themselves, and I am concernced with preserving hard disk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they pointed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committed
> to the database yet. Am I worrying needlessly? Or am I actually losing data
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer that
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter

DUMP TRANSACTION

I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
other Microsoft documentation but it doesn't seem to work. Is it still
supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
Rom Reis,
If you want to truncate the inactive portion of the Transaction Log of your
database, in SQL SERVER 2000 you use the following statement:
Backup Log DBNAME with TRUNCATE_ONLY
The " Dump Transaction " is a 6.x SQL Version Command.
Best Regards,
Paulo Conde?a.
"Tom Reis" wrote:

> I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
> other Microsoft documentation but it doesn't seem to work. Is it still
> supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
>
>
|||"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23aWjbJZCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
>other Microsoft documentation but it doesn't seem to work. Is it still
>supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
DUMP TRANSACTION is still supported, but is deprecated. Define "doesn't
seem to work". Does the equivalent BACKUP command do anything differently?
sql

DUMP TRANSACTION

Hi !!!
What the following command do?
DUMP TRANSACTION mydatabase WITH NO_LOG
--
The Desperate Microsoft´s NewbieDesperate
It is a SQL 6.5 command equivalent to for BACKUP LOG mydatabase WITH
TRUNCATE_ONLY (which you can read about in the books online for SQL Server
if you have SQL 7 or 200).
It makes a backup of the transaction log, but doesn't record the fact that
it made the backup, and is only done when the log is so full absolutely
nothing else can be written to it. It is an emergency measure and not to be
done as part of your normal scheduled log backups.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"The Desperate Microsoft´s Newbie" <ms.newbiew@.brfree.com.br> wrote in
message news:#BxGbg23DHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi !!!
> What the following command do?
> DUMP TRANSACTION mydatabase WITH NO_LOG
>
> --
> The Desperate Microsoft´s Newbie
>
>|||Hi,
NO_LOG option is used only when you have run out of space in the database
and cannot use
DUMP TRANSACTION WITH TRUNCATE_ONLY to purge the log.
The NO_LOG option removes the inactive part of the log without making a
backup copy
of it, and it saves space by not logging the operation.
Thanks
Hari
MCDBA
"The Desperate Microsoft´s Newbie" <ms.newbiew@.brfree.com.br> wrote in
message news:#BxGbg23DHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi !!!
> What the following command do?
> DUMP TRANSACTION mydatabase WITH NO_LOG
>
> --
> The Desperate Microsoft´s Newbie
>
>

DUMP TRANSACTION

I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
other Microsoft documentation but it doesn't seem to work. Is it still
supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.Rom Reis,
If you want to truncate the inactive portion of the Transaction Log of your
database, in SQL SERVER 2000 you use the following statement:
Backup Log DBNAME with TRUNCATE_ONLY
The " Dump Transaction " is a 6.x SQL Version Command.
Best Regards,
Paulo Condeça.
"Tom Reis" wrote:
> I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
> other Microsoft documentation but it doesn't seem to work. Is it still
> supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
>
>|||"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23aWjbJZCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
>other Microsoft documentation but it doesn't seem to work. Is it still
>supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
DUMP TRANSACTION is still supported, but is deprecated. Define "doesn't
seem to work". Does the equivalent BACKUP command do anything differently?

DUMP TRANSACTION

I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
other Microsoft documentation but it doesn't seem to work. Is it still
supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.Rom Reis,
If you want to truncate the inactive portion of the Transaction Log of your
database, in SQL SERVER 2000 you use the following statement:
Backup Log DBNAME with TRUNCATE_ONLY
The " Dump Transaction " is a 6.x SQL Version Command.
Best Regards,
Paulo Conde?a.
"Tom Reis" wrote:

> I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
> other Microsoft documentation but it doesn't seem to work. Is it still
> supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
>
>|||"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23aWjbJZCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>I am running SQL 2000 SP4. I see the DUMP TRANSACTION command in BOL and
>other Microsoft documentation but it doesn't seem to work. Is it still
>supported. The command I am using is DUMP TRANSACTION WITH NO_LOG.
DUMP TRANSACTION is still supported, but is deprecated. Define "doesn't
seem to work". Does the equivalent BACKUP command do anything differently?

DUMP TRANSACTION

Hi !!!
What the following command do?
DUMP TRANSACTION mydatabase WITH NO_LOG
The Desperate Microsofts NewbieDesperate
It is a SQL 6.5 command equivalent to for BACKUP LOG mydatabase WITH
TRUNCATE_ONLY (which you can read about in the books online for SQL Server
if you have SQL 7 or 200).
It makes a backup of the transaction log, but doesn't record the fact that
it made the backup, and is only done when the log is so full absolutely
nothing else can be written to it. It is an emergency measure and not to be
done as part of your normal scheduled log backups.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"The Desperate Microsofts Newbie" <ms.newbiew@.brfree.com.br> wrote in
message news:#BxGbg23DHA.1264@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi !!!
> What the following command do?
> DUMP TRANSACTION mydatabase WITH NO_LOG
>
> --
> The Desperate Microsofts Newbie
>
>

---

|||Hi,
NO_LOG option is used only when you have run out of space in the database
and cannot use
DUMP TRANSACTION WITH TRUNCATE_ONLY to purge the log.
The NO_LOG option removes the inactive part of the log without making a
backup copy
of it, and it saves space by not logging the operation.
Thanks
Hari
MCDBA
"The Desperate Microsofts Newbie" <ms.newbiew@.brfree.com.br> wrote in
message news:#BxGbg23DHA.1264@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi !!!
> What the following command do?
> DUMP TRANSACTION mydatabase WITH NO_LOG
>
> --
> The Desperate Microsofts Newbie
>
>

---

Dump log and move log to another drive

Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
WillWill Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:

> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>

Dump log and move log to another drive

Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
Will
Will Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:

> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>
|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>

Dump log and move log to another drive

Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
WillWill Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>

Dumb Transaction log question.

I know I've been asking a lot of dumb questions here lately but - One must
learn somewhere.
In my test lab cluster that I'm building, W2k3 Cluster admin put each of my
physical disks in its own group. Now I'm keep one for the cluster group, and
one for the MSDTC, which leaves me four. I'm going to use 1 disk for data
and 1 disk for tranaction logs for each instance of sql. Are there any
advantages in putting the disks in the same group. (that is putting 1 disk
for the transaction logs and 1 disk for the data in the same group). I don't
see any advantages, but I'll ask anyway.
Also on transaction logs, where do I go to move the default location of
transaction logs in each instance of SQL.
The physical disk resources need to be in the same group as the SQL server resource that will use them, and the SQL server resource needs to have a dependancy on both physical disk resources.
As to the default locations, they are specified on the "Database Settings" tab of the Server Properties dialog in EM. (Not the "Edit registration properties" near the top of the right-click menu, but the "properties" near the bottom)
If you want to move database files or transaction logs, you can detach the database, move the files, and reattach the database files by specifying the new paths in the attach dialog box. Right-click on the database and select "all tasks >> detach" to detach the database. To attach the database, right click on the "databases" container and select "all tasks >> attach database".
You can also use T-SQL to call sp_detach and sp_attach, but the GUI works fine.
Just make sure that the SQL server resource has the physical disk resource(s) as a dependancy(ies) or you will not be able to attach the files.
Play around until you are comfortable with this stuff before you go live. It's quite geeky fun!
Good Luck
jg

Quote:

Originally posted by Wayne
I know I've been asking a lot of dumb questions here lately but - One must
learn somewhere.
In my test lab cluster that I'm building, W2k3 Cluster admin put each of my
physical disks in its own group. Now I'm keep one for the cluster group, and
one for the MSDTC, which leaves me four. I'm going to use 1 disk for data
and 1 disk for tranaction logs for each instance of sql. Are there any
advantages in putting the disks in the same group. (that is putting 1 disk
for the transaction logs and 1 disk for the data in the same group). I don't
see any advantages, but I'll ask anyway.
Also on transaction logs, where do I go to move the default location of
transaction logs in each instance of SQL.

|||There is no dumb question.
Its a good question. Since you are using one disk for data and another disk for log, both the disk resources will need to be in SAME SQL Server group (along with other SQL Server resources). Infact, if they are on
seperate group SQL Server cannot use it. During installation, we can just specify one disk. After installation, one has to move the second disk to the SQL group, take SQL Server resource offline, add the second disk
as a dependency for SQL Server resource and then take the SQL Server resource online. After that you can use your normal steps/techniques to move the logs on the second disk.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Dual Transaction Log

I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
Nick
Hi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=re...t .com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>

Dual Transaction Log

I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
NickHi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=remove+log+file+in+sql+7&hl=en&lr=&ie=UTF-8&selm=us%24RwvUb%24GA.223%40cppssbbsa02.microsoft.com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>

Dual trans logs

Hello,
I have a server with two active transaction logs on 1
database and need to remove one, the problem arises when
I try to. Anyone advise on a procedure to do this?
Thank you in advance,
Tom
tww@.ccsiservices.comExactly how are you attempting this and what is the actual error?
--
Andrew J. Kelly
SQL Server MVP
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:009201c3a560$32807a30$a501280a@.phx.gbl...
> Hello,
> I have a server with two active transaction logs on 1
> database and need to remove one, the problem arises when
> I try to. Anyone advise on a procedure to do this?
> Thank you in advance,
> Tom
> tww@.ccsiservices.com|||Well I have not tried yet, though I spoke with someone
who was trying. I am off-site and will be going into the
office tomorrow to take a look. They opened database
properties in the Enterprise Manager and tried to just
delete the extra log file (which we are still trying to
figure out why it was put there) but it says something
about the file has to be empty to remove it..etc. If the
actual error is needed I can get it later today. I was
online and decided to post this query to see if anyone
had experience with this.
on a side note, the system is not secured and this extra
log file setting was setup on Wednesday this week, during
maintenance it was noticed. I have been out of the office
so I am still getting all the facts.
Thank you,
Tom
>--Original Message--
>Exactly how are you attempting this and what is the
actual error?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Tom" <anonymous@.discussions.microsoft.com> wrote in
message
>news:009201c3a560$32807a30$a501280a@.phx.gbl...
>> Hello,
>> I have a server with two active transaction logs on 1
>> database and need to remove one, the problem arises
when
>> I try to. Anyone advise on a procedure to do this?
>> Thank you in advance,
>> Tom
>> tww@.ccsiservices.com
>
>.
>|||OK that's more to go on. Take a look at DBCC SHRINKFILE and pay attention
to the EMPTYFILE option in BooksOnLine. You need to move any data to the
other file and it gets marked so as it doesn't fill again. Then you can use
ALTER DATABASE to drop the file.
--
Andrew J. Kelly
SQL Server MVP
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:01dd01c3a572$1a280130$a601280a@.phx.gbl...
> Well I have not tried yet, though I spoke with someone
> who was trying. I am off-site and will be going into the
> office tomorrow to take a look. They opened database
> properties in the Enterprise Manager and tried to just
> delete the extra log file (which we are still trying to
> figure out why it was put there) but it says something
> about the file has to be empty to remove it..etc. If the
> actual error is needed I can get it later today. I was
> online and decided to post this query to see if anyone
> had experience with this.
> on a side note, the system is not secured and this extra
> log file setting was setup on Wednesday this week, during
> maintenance it was noticed. I have been out of the office
> so I am still getting all the facts.
> Thank you,
> Tom
> >--Original Message--
> >Exactly how are you attempting this and what is the
> actual error?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Tom" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:009201c3a560$32807a30$a501280a@.phx.gbl...
> >> Hello,
> >>
> >> I have a server with two active transaction logs on 1
> >> database and need to remove one, the problem arises
> when
> >> I try to. Anyone advise on a procedure to do this?
> >>
> >> Thank you in advance,
> >> Tom
> >> tww@.ccsiservices.com
> >
> >
> >.
> >|||Great! Thanks a ton, That will save some time.
Have a great weekend.
Tom
>--Original Message--
>OK that's more to go on. Take a look at DBCC SHRINKFILE
and pay attention
>to the EMPTYFILE option in BooksOnLine. You need to
move any data to the
>other file and it gets marked so as it doesn't fill
again. Then you can use
>ALTER DATABASE to drop the file.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Tom" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01dd01c3a572$1a280130$a601280a@.phx.gbl...
>> Well I have not tried yet, though I spoke with someone
>> who was trying. I am off-site and will be going into
the
>> office tomorrow to take a look. They opened database
>> properties in the Enterprise Manager and tried to just
>> delete the extra log file (which we are still trying to
>> figure out why it was put there) but it says something
>> about the file has to be empty to remove it..etc. If
the
>> actual error is needed I can get it later today. I was
>> online and decided to post this query to see if anyone
>> had experience with this.
>> on a side note, the system is not secured and this
extra
>> log file setting was setup on Wednesday this week,
during
>> maintenance it was noticed. I have been out of the
office
>> so I am still getting all the facts.
>> Thank you,
>> Tom
>> >--Original Message--
>> >Exactly how are you attempting this and what is the
>> actual error?
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Tom" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:009201c3a560$32807a30$a501280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> I have a server with two active transaction logs on
1
>> >> database and need to remove one, the problem arises
>> when
>> >> I try to. Anyone advise on a procedure to do this?
>> >>
>> >> Thank you in advance,
>> >> Tom
>> >> tww@.ccsiservices.com
>> >
>> >
>> >.
>> >
>
>.
>