You may think that having a database set to FULL Recovery Model means that your database can be fully recovered. The key word in that previous sentence is 'can'. It is possible to fully recovery a database in FULL recovery, or to restore it to an arbitrary point in time, or up to the point of a system failure, IF you have been a good DBA and you have been taking your regular backups.
FULL Recovery means that all your changes to the database are fully logged, and you might think that also means that the log records stay in the log until the log records are safely stored away in a log backup.
But that is not completely correct. You database can be in FULL Recovery model, but also in a state called auto-truncate mode.
Most people are familiar with auto-truncation in SIMPLE Recovery, and of course that is one way to get a database into auto-truncate mode.
If your database is in auto-truncate mode, the log will be truncated every time a CHECKPOINT is run against the database. Checkpoints happen at very frequent intervals, which by default is usually about every minute. Truncation means that all log records in inactive parts of the log are marked as available to be overwritten if the log space is needed. Truncation does not physically shrink the log file, but it can keep it from physically growing.
So how do you get in auto-truncate mode? There are three ways:
1. As a mentioned above, if you put your database into SIMPLE Recovery, it will be go into auto-truncate mode.
2. If you backup the log without saving the log records, using BACKUP LOG ... WITH TRUNCATE_ONLY, no more log backups can be made, and the database is now in auto-truncate mode.
3. If you have never taken a FULL backup of your database, you can never take log backups, so there is no point in keep log records available. Your database is in auto-truncate mode until the first FULL backup of the database is made.
3a. If you have never taken a FULL backup after the last time you switched from SIMPLE to either FULL or BULK_LOGGED recovery. (Thanks to Hugo)
You should also be aware that taking a log backup will truncate the log, but taking a FULL database backup will not truncate the log.
So how can you tell if your database is in auto-truncate mode? The recovery_model_desc column in sys.databases only tells you what recovery model you have set, not what behavior your database is using. To see whether a database is in auto-truncate mode, you can query a undocumented system view called sys. recovery_recovery_status and look at a column called last_log_backup_lsn. If that value is NULL, it means the database is not maintaining a sequence of log backups and it is in auto-truncate mode:
SELECT db_name(database_id) as 'database', last_log_backup_lsn
FROM sys.database_recovery_status
Here's a whole script you can use to test this for yourself:
-- Check for auto-truncate mode
-- If last_log_backup_lsn is NULL, log is in auto-truncate modeCREATE DATABASE FOO
GO
-- Check status after first creating db
SELECT db_name(database_id) as 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
BACKUP DATABASE foo TO disk = 'C:\foo.bak'
GO
-- Check status after backing up db
SELECT db_name(database_id) AS 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
BACKUP LOG foo WITH TRUNCATE_ONLY
GO
-- Check status after truncating log
SELECT db_name(database_id) AS 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
DROP DATABASE foo
GO
Note that there is a change in behavior in SQL Server 2008. The BACKUP LOG ... WITH TRUNCATE_ONLY option is no longer available. If you want to force the log to be truncated (and enter auto-truncate mode) the recommended method is to change your recovery model to SIMPLE.
Have fun!
~Kalen
Source Click Here.
Post a Comment