Re: does this backup sequence commit all data to the database
- From: "Roy Harvey (SQL Server MVP)" <roy_harvey@xxxxxxxx>
- Date: Wed, 09 Jul 2008 12:46:26 -0400
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
<lesperancer@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
- Follow-Ups:
- References:
- does this backup sequence commit all data to the database
- From: Roger
- Re: does this backup sequence commit all data to the database
- From: Roy Harvey (SQL Server MVP)
- Re: does this backup sequence commit all data to the database
- From: Roger
- does this backup sequence commit all data to the database
- Prev by Date: Re: does this backup sequence commit all data to the database
- Next by Date: Re: Multiple attribute design that need to be searchable
- Previous by thread: Re: does this backup sequence commit all data to the database
- Next by thread: Re: does this backup sequence commit all data to the database
- Index(es):
Relevant Pages
|
