full drives full trans log version 7

What does one do?
I tried "backup log mb with no_log" to truncate the file. I've now
started a shrinkdatabase. I can't backup since the drive is full. I
can't disconnect because it's 7.
How does one actually reduce the log file size?
thx
md
PS isn't simple logging supposed to avoid this problem?
PSS is it possible to set a DTS to not log?
*** Sent via Developersdex http://www.codecomments.com ***See if this helps:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/EN-US/
AMB
"M D" wrote:

> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Hi,
Since you dont require the transaction log backup you could truncate the log
first
BACKUP LOG DBNAME with TRUNCATE_ONLY
After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
If the usage is very low than SHRINK the LDF file using DBCC
SHRINKFILE('logical ldf name',size)
Please refer the link Alejandro pointed out.
Thanks
Hari
SQL Server MVP
"M D" <mardukes@.aol.com> wrote in message
news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.codecomments.com ***|||I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:

> Hi,
> Since you dont require the transaction log backup you could truncate the l
og
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
>|||Also run a DBCC OPENTRAN agains the database in question to make sure there
are no pending transactions to be completed.
The trasaction log architecture is different in SS7.0 versus SS2K and can be
quite difficult to shrink, even when using BACKUP LOG ... WITH NO_LOG or
WITH TRUNCATE_ONLY, which are synonomous by the way.
Usually, you will have to kill all processes but your own within the
database you are concerned with.
Execute sp_detach_db.
Delete the Transaction log file(s) for the database in question.
Execute sp_attach_single_file_db.
The new transaction log will be the size of the transaction log for the
model database.
Also know that regardless of Recovery Model, which didn't exist in
SS7.0-there were database options: 'trunc. log on chkpt.' and/or 'select
into/bulk copy'-EVERY TRANSACTION IS WRITTEN TO THE TRANSACTION LOG. There
is no way to stop this. The recovery settings just tell SQL Server what to
do with "COMPLETED" transactions within the transaction log file if they
have not been backed up yet.
If you are using 'trunc. log on chkpt.', then completed transactions are
automatically purged on the next CHECKPOINT process. This allows you to
write over that freed up space with the next transaction. However, your
transaction log will grow to support the largest single active transaction
or concurrent set of transactions.
If transactions are open, not completed or rolled back, then new
transactions can not overwrite those regardless of the recovery settings, or
db options, as the case may be.
Sincerely,
Anthony Thomas
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:8E5D6182-C9B9-4999-BBD7-5074AA5BF78A@.microsoft.com...
I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:

> Hi,
> Since you dont require the transaction log backup you could truncate the
log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
>

full drives full trans log version 7

What does one do?
I tried "backup log mb with no_log" to truncate the file. I've now
started a shrinkdatabase. I can't backup since the drive is full. I
can't disconnect because it's 7.
How does one actually reduce the log file size?
thx
md
PS isn't simple logging supposed to avoid this problem?
PSS is it possible to set a DTS to not log?
*** Sent via Developersdex http://www.developersdex.com ***See if this helps:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/EN-US/
AMB
"M D" wrote:
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Hi,
Since you dont require the transaction log backup you could truncate the log
first
BACKUP LOG DBNAME with TRUNCATE_ONLY
After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
If the usage is very low than SHRINK the LDF file using DBCC
SHRINKFILE('logical ldf name',size)
Please refer the link Alejandro pointed out.
Thanks
Hari
SQL Server MVP
"M D" <mardukes@.aol.com> wrote in message
news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.developersdex.com ***|||I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:
> Hi,
> Since you dont require the transaction log backup you could truncate the log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> > What does one do?
> >
> > I tried "backup log mb with no_log" to truncate the file. I've now
> > started a shrinkdatabase. I can't backup since the drive is full. I
> > can't disconnect because it's 7.
> >
> > How does one actually reduce the log file size?
> >
> > thx
> > md
> >
> > PS isn't simple logging supposed to avoid this problem?
> > PSS is it possible to set a DTS to not log?
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>|||Also run a DBCC OPENTRAN agains the database in question to make sure there
are no pending transactions to be completed.
The trasaction log architecture is different in SS7.0 versus SS2K and can be
quite difficult to shrink, even when using BACKUP LOG ... WITH NO_LOG or
WITH TRUNCATE_ONLY, which are synonomous by the way.
Usually, you will have to kill all processes but your own within the
database you are concerned with.
Execute sp_detach_db.
Delete the Transaction log file(s) for the database in question.
Execute sp_attach_single_file_db.
The new transaction log will be the size of the transaction log for the
model database.
Also know that regardless of Recovery Model, which didn't exist in
SS7.0-there were database options: 'trunc. log on chkpt.' and/or 'select
into/bulk copy'-EVERY TRANSACTION IS WRITTEN TO THE TRANSACTION LOG. There
is no way to stop this. The recovery settings just tell SQL Server what to
do with "COMPLETED" transactions within the transaction log file if they
have not been backed up yet.
If you are using 'trunc. log on chkpt.', then completed transactions are
automatically purged on the next CHECKPOINT process. This allows you to
write over that freed up space with the next transaction. However, your
transaction log will grow to support the largest single active transaction
or concurrent set of transactions.
If transactions are open, not completed or rolled back, then new
transactions can not overwrite those regardless of the recovery settings, or
db options, as the case may be.
Sincerely,
Anthony Thomas
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:8E5D6182-C9B9-4999-BBD7-5074AA5BF78A@.microsoft.com...
I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:
> Hi,
> Since you dont require the transaction log backup you could truncate the
log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> > What does one do?
> >
> > I tried "backup log mb with no_log" to truncate the file. I've now
> > started a shrinkdatabase. I can't backup since the drive is full. I
> > can't disconnect because it's 7.
> >
> > How does one actually reduce the log file size?
> >
> > thx
> > md
> >
> > PS isn't simple logging supposed to avoid this problem?
> > PSS is it possible to set a DTS to not log?
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>

full backup and transaction log truncate

Hi,
I'm about how the backup system works in SQL Server. I'm using
the BULK LOGGED recovery model, I do regular full backups but my log
file is growing very large.
BOL says:
Full database backup, which backs up the entire database including the
transaction log
and it also says the BACKUP LOG truncates the log file, so I assumed
that BACKUP DATABASE would truncate the log file as well, but it doesn't
seem to be doing that. So the question is:
Does doing a full backup using BACKUP DATABASE truncate the log file?
Although I might be by the statement in BOL I actually hope
that BACKUP DATABASE does not truncate the log file, that way I can do
full backups whenever I want, for whatever reason and these backups will
not interfere with an automated full/differential/trans log backup system.
Thanks.BACKUP DATABASE does not truncate the inactive log portion. This means that
full backups do not interrupt a log backup sequence. If your most recent
backup is bad or incomplete, you can use an older backup as a starting point
and restore more logs to get back to the desired recovery point in time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John" <no@.spam> wrote in message
news:edF0K$deGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log file
> is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed that
> BACKUP DATABASE would truncate the log file as well, but it doesn't seem
> to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope that
> BACKUP DATABASE does not truncate the log file, that way I can do full
> backups whenever I want, for whatever reason and these backups will not
> interfere with an automated full/differential/trans log backup system.
> Thanks.|||John,
You have to backup the log in order to allow sql server to re-use the VLF
before the MinLSN. If these VLFs can not be reused and we reach the end of
the transaction log, then sql server will have to expand it.
Truncating the Transaction Log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/?kbid=873235
AMB
"John" wrote:

> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log
> file is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed
> that BACKUP DATABASE would truncate the log file as well, but it doesn't
> seem to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope
> that BACKUP DATABASE does not truncate the log file, that way I can do
> full backups whenever I want, for whatever reason and these backups will
> not interfere with an automated full/differential/trans log backup system.
> Thanks.
>