473,380 Members | 1,259 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,380 software developers and data experts.

does this backup sequence commit all data to the database

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 ?

Jul 8 '08 #1
5 3470
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
<le*********@natpro.comwrote:
>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 ?
Jul 8 '08 #2
On Jul 8, 5:06*pm, "Roy Harvey (SQL Server MVP)" <roy_har...@snet.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...@natpro.comwrote:
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
Jul 9 '08 #3
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
<le*********@natpro.comwrote:
>On Tue, 8 Jul 2008 15:14:51 -0700 (PDT), Roger

<lesperan...@natpro.comwrote:
>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
Jul 9 '08 #4
On Jul 9, 10:46*am, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.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...@natpro.comwrote:
On Tue, 8 Jul 2008 15:14:51 -0700 (PDT), Roger
<lesperan...@natpro.comwrote:
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 ?
Jul 9 '08 #5
On Wed, 9 Jul 2008 10:45:07 -0700 (PDT), Roger
<le*********@natpro.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
Jul 9 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Simon | last post by:
Hi, I am having a little problem with my PHP - MySQl code, I have two tables (shown below) and I am trying populate a template page with data from both. <disclaimer>Now I would like to say my...
0
by: Matt | last post by:
I run SQL Server 2000 and use thier database maintenance plans to backup my databases. My questions is what happens if a change is being made to a database table while a backup is running? Should I...
3
by: Andy Davis | last post by:
Hi Is there any way of carrying out an automatic daily/weekly backup of an Access 2003 database without the user having to manually remember to do it themselves? Thanks in advance. Dawn
0
by: sajijkumar | last post by:
How can I take backup of the database to flash drive. I used a Dir and Drive list box controls to select the perticular dir, and Wrote the code, Shell "command.com /c copy " & App.Path &...
0
by: Softhideki | last post by:
Good Day!!! For alll I have problem for create a script to copy a sequence of way automatic to copy a sequence of oracle database for other oracle database. Anyone help me , how can...
3
by: =?Utf-8?B?V2FsaWQ=?= | last post by:
Hello: I have migrated a site from IIS5 to IIS6. The site is configured and the we app is installed on the new IIS site. I can get to the web page but when I try to login, this is what I get...
3
by: saurabhsingh | last post by:
Hi I need to take automatic backup of my sqlserver2005 database per day. And I don't want to replace old backup,a new backup should be made per day. Thanks in advance
2
by: puT3 | last post by:
Hi, anyone can help me? My problem is data from Form of Asp does not appear in access database when i open the database. Why does this happen? My table name:Cuba ID PK Nama Umur
0
by: raazju | last post by:
I've to backup and restore data into MS SQL Server database? I've to restore previous data into database server of MS SQL Server. anyone can help to solve this problem?
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.