Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL backups and truncation Expand / Collapse
Author
Message
Posted Monday, December 17, 2007 2:34 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
I would like to know if doing a database backup also involves truncating the log file? I would assume so, but would like to know for certain.


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #434062
Posted Monday, December 17, 2007 2:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 23,396, Visits: 32,224
It "Depends". What type of backup and what recovery model is the database using?

A full backup and differential backup do not truncate the transaction log. Enough of the transaction log is backed up during these backups to provide a consistant backup.

The transaction log is truncated (when a database is in bulk-logged of full recovery model) when a transaction log backup is completed.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #434066
Posted Monday, December 17, 2007 4:06 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
We're using Full for the recovery model in SQL Server 2005. So, does that mean that the log file won't be truncated when a backup occurs?

In our test database server we have all databases' recovery models set to Simple. Would those get truncated after a backup?


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #434096
Posted Monday, December 17, 2007 4:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 23,396, Visits: 32,224
Simple recovery model, the transaction log is truncated when the data is checkpointed. In full recovery model, the transaction log is only truncated when a transaction log backup is completed. The transaction log is not truncated by either a full or differential backup.

Books On Line covers this pretty well if you need to read more about the recovery models and backups.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #434097
Posted Monday, December 17, 2007 6:29 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:47 PM
Points: 3,244, Visits: 538
If your database is set to FULL recovery mode - you MUST do transaction log backups. If you don't, your transaction log will continue to grow until there is no room left for it to expand. You can't just shrink the tlog file either, it has to be backed up.

-SQLBill



Post #434114
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse