does this backup sequence commit all data to the database

[Roger]

backup log testdb with truncate_only
DBCC SHRINKFILE (testdb_log, 100) WITH NO_INFOMSGS
backup database testdb to disk = '\\DC01\Backups \DB01\testdb.ba k' with
init


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

[Roy Harvey (SQL Server MVP)]

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

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
<lesperancer@na tpro.comwrote:

>backup log testdb with truncate_only
>DBCC SHRINKFILE (testdb_log, 100) WITH NO_INFOMSGS
>backup database testdb to disk = '\\DC01\Backups \DB01\testdb.ba k' with
>init
>
>
>and does the shrinkfile command reduce the size of the ldf ?

[Roger]

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

On Jul 8, 5:06 pm, "Roy Harvey (SQL Server MVP)" <roy_har...@sne t.net>
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...@na tpro.comwrote:

backup log testdb with truncate_only
DBCC SHRINKFILE (testdb_log, 100) WITH NO_INFOMSGS
backup database testdb to disk = '\\DC01\Backups \DB01\testdb.ba k' 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.ba k'
with
init

[Roy Harvey (SQL Server MVP)]

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

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@na tpro.comwrote:

>On Tue, 8 Jul 2008 15:14:51 -0700 (PDT), Roger
>>
><lesperan...@n atpro.comwrote:

>backup log testdb with truncate_only
>DBCC SHRINKFILE (testdb_log, 100) WITH NO_INFOMSGS
>backup database testdb to disk = '\\DC01\Backups \DB01\testdb.ba k' 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.ba k'
>with
init

[Roger]

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

On Jul 9, 10:46 am, "Roy Harvey (SQL Server MVP)"
<roy_har...@sne t.netwrote:

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...@na tpro.comwrote:

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

>

<lesperan...@na tpro.comwrote:
backup log testdb with truncate_only
DBCC SHRINKFILE (testdb_log, 100) WITH NO_INFOMSGS
backup database testdb to disk = '\\DC01\Backups \DB01\testdb.ba k' 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.ba k'
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 ?

[Roy Harvey (SQL Server MVP)]

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

On Wed, 9 Jul 2008 10:45:07 -0700 (PDT), Roger
<lesperancer@na tpro.comwrote:

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

Essentially correct, but I would not get fixated on the size of the
log file. If I can expect the log to grow normally need to be x
megabytes, but I know that every few months it will have to grow to 3x
megabytes, then I would allocate the log at 4x megabytes and leave it
that way. There are far more important things to be doing that
fiddling with a bit of disk space, at least for me.

Roy Harvey
Beacon Falls, CT