1

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.

Improve this question
0

2 Answers 2

Reset to default
2

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

Improve this answer
0

"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.

Improve this answer
1
  • 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
    Commented Nov 18, 2012 at 16:14