Re: does this backup sequence commit all data to the database

On Jul 8, 5:06 pm, "Roy Harvey (SQL Server MVP)" <roy_har...@xxxxxxxx>
wrote:
does this backup sequence commit all data to the database

No, the data was committed when the application transactions were
committed.  That could be an explicit COMMIT when an explicit BEGIN
TRAN was used, or an implicit COMMIT when each insert, update and
delete was executed outside an explicit transaction.

and does the shrinkfile command reduce the size of the ldf ?

Possibly, but I would not count on it.  The BACKUP command was
directed at the database, not the log.  If the database is in SIMPLE
recovery mode there is no reason to backup the log.  In that case the
SHRINKFILE against the log file might reduce the size unless there is
a really big uncommitted transaction open.  If the database is in FULL
or BULK LOGGED  recovery mode the log will keep growing until backed
up (or truncated).  In that case the chance of the log file shrinking
is smaller since all log data since the last backup will be preserved,
which is to say the data in the log represents the minimum size to
which it can be shrunk.

I strongly suggest you review the documentation on recovery models and
log management.

Roy Harvey
Beacon Falls, CT

On Tue, 8 Jul 2008 15:14:51 -0700 (PDT), Roger



<lesperan...@xxxxxxxxxx> wrote:
backup log testdb with truncate_only
DBCC SHRINKFILE (testdb_log, 100) WITH NO_INFOMSGS
backup database testdb to disk = '\\DC01\Backups\DB01\testdb.bak' with
init

and does the shrinkfile command reduce the size of the ldf ?- Hide quoted text -

- Show quoted text -

the db is in 'full recovery mode', and I thought
backup log testdb with truncate_only

would flush all committed log transactions to the mdb, leaving only
open transactions in the log file

I understand that "backup log testdb to disk = '...'" will backup the
log transactions
which I'm during throughout the day

But at night, I want to flush the log file of committed transactions
to the db and then backup the db.... doesn't this do that ?
backup log testdb with truncate_only
DBCC SHRINKFILE (testdb_log, 100) WITH NO_INFOMSGS
backup database testdb to disk = '\\DC01\Backups\DB01\testdb.bak'
with
init
.

Relevant Pages

  • Re: does this backup sequence commit all data to the database
    ... Then you shrink the log file and backup the database. ... space taken up by committed transactions is freed, ... I understand that "backup log testdb to disk = '...'" will backup the ...
    (comp.databases.ms-sqlserver)
  • Re: (not entirely...) OT: OPINION... chicken entrails, runic stones, and crystal balls... WAS CoBOL
    ... the commit or "end" verb does end the transaction. ... the 8:00 PM run by "backing out" transactions. ... backup and re-apply all transactions from the backup time until 8:00 PM. ...
    (comp.lang.cobol)
  • Re: Backups and Transaction Log file size
    ... It sounds like a classic case of a long running open transaction. ... Find the client and either commit or roll it back. ... If you know it is a garbage connection you can kill the SPID and it will roll back any changes that the SPID may have open and allow you to backup and truncate properly. ... Once the committed trans have been ...
    (microsoft.public.sqlserver.setup)
  • Re: transaction log backup & file size
    ... of 'system transactions' (only guessing, ... Need smaller SQL2K backup files? ... > alter database BBH set single_user with rollback immediate ... > /*that one breakes log sequence as I understand, ...
    (microsoft.public.sqlserver.server)
  • Re: Backups and Transaction Log file size
    ... Find the client and either commit or roll it back. ... that a FULL Backup didn't do the Log file, ... Even after I added Transaction Log backups, ... Once the committed trans have ...
    (microsoft.public.sqlserver.setup)