Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?

Is it possible to have SQL Server 2008 R2 reset the transaction log for certain databases when the database is backed up?

Scenario:

Auto backup job in SQL Server takes a backup of database XYZ and also of it's transaction log. When this backup has completed SQL Server will reset the transaction log (log file size will be increased to starting point)

Thank you.

share|improve this question
add comment

migrated from stackoverflow.com Oct 19 '12 at 12:05

This question came from our site for professional and enthusiast programmers.

marked as duplicate by jcolebrand Dec 10 '12 at 14:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

Quoting Paul Randal here:

In the FULL or BULK_LOGGED recovery models, the only thing that clears the log is a log backup.

http://www.sqlskills.com/blogs/paul/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx

share|improve this answer
add comment

"Reseting" the transaction log is basically what happens when SQL Server backups a database either during full or log. Read here to get more detail: http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx

The backup basically takes all that used space in the transaction log and clears any inactive log space that it can, so SQL will be able to reuse it instead of growing the log file out. The more frequent you do log backups, the more it clears that inactive space. Most people will refer to this as log management, with regards to SQL Server.

share|improve this answer
3  
The first statement of this answer is misleading. Full backups themselves do not clear the transaction log in any recovery model. Only transaction log backups (FULL and BULK_LOGGED recovery) and CHECKPOINT operations (SIMPLE recovery) do. The confusion lies in that a CHECKPOINT occurs in a database before a full backup happens (all recovery models), so the first statement is only true in result for SIMPLE recovery. –  Jon Seigel Nov 18 '12 at 16:14
add comment