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

On Jul 9, 10:46 am, "Roy Harvey (SQL Server MVP)"
<roy_har...@xxxxxxxx> wrote:
I missed the line in the original message where the log was truncated,
and now you have supplied some new information, so let me start over.

As I understand it, you are saying:

1) The database is in FULL recovery mode.

2) During the day you BACKUP the LOG to files.

3) At night you TRUNCATE the log "to flush the log file of committed
transactions".  Then you shrink the log file and backup the database.

One thing to understand is that every time you BACKUP the log the
space taken up by committed transactions is freed, at least up to the
start of the oldest open transaction.  The log file size is not
changed, but space inside the log file is freed up.  So there is no
special need to flush the log if the log is being backed up.

Another important issue is that shrinking the log should NOT be a
regularly scheduled event.  You can read the details behind that here:http://www.karaszi.com/sqlserver/info_dont_shrink.asp

The log file should be made large enough in the first place so that it
does not have to grow, and then it should be backed up frequently
enough that it never fills up.

So what I would suggest for your nightly process is to backup the log
one last time, then backup the database.  No truncate, no shrink.

Roy Harvey
Beacon Falls, CT

On Wed, 9 Jul 2008 09:19:23 -0700 (PDT), Roger



<lesperan...@xxxxxxxxxx> wrote:
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- Hide quoted text -

- Show quoted text -

ok... so I want the log file to be a certain size for performance
reasons so I don't want to always shrink it

and my hourly / daily backups (log / db) will free up log space,
keeping the log file within a certain size, reusing freed space

so the only time I want to shrink my log file, is if my backups aren't
run for a few days and my log files get to be too large correct ?
.

Relevant Pages

  • Re: Database/Logs dont shrink with Maintenance Task
    ... Only a shrink file can do that but it may require a backup to get ... Other wise it has to keep growing to make room for more transactions. ... the log file smaller--whether you shrink or not? ...
    (microsoft.public.sqlserver.setup)
  • Re: How to PURGE a transaction log?
    ... This doesn't shrink the actual file size. ... Make sure you understand backup and restore architecture for 6.5 *really* ... make sure you understand the database architecture regarding database ... doing dummy transactions and DUMP TRANSACTION until the log have moved ...
    (microsoft.public.sqlserver.setup)
  • Transaction log problems
    ... just before you backup the ... database change the recovery model to ... Simple, shrink the log file then change it back to Full, ... >transaction log and it is causing us some problems. ...
    (microsoft.public.sqlserver.server)
  • Re: Unable to attach the database
    ... BUT NEVER DELETE A LOG FILE! ... If you have a clean backup of the database, ... good understanding of SQL Server and how to work from this situation. ...
    (comp.databases.ms-sqlserver)
  • Re: Detach - Attach in SQL Server 7
    ... >> It is not rquired to detach and attach the database to shrink the file. ... If the log file really huge make the databse single user after ... >> DBCC SHRINKFILE('Logical_ldf_name','truncateonly' ...
    (microsoft.public.sqlserver.server)