[스크랩] 로그백업을 해도 로그 shrink가 안 되는 이유?

그 때 아마 log_reuse_wait_desc를 보면 상태가 NOTHIN이 아니라 LOG_BACKUP 으로 되어 있을거다.

로그백업 상태 설명

(log_reuse_wait_desc 값 설명)

https://technet.microsoft.com/en-us/library/ms345414(v=sql.105).aspx

2

LOG_BACKUP

A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). Note Log backups do not prevent truncation. When the log backup is completed, the head of the log is moved forward, and some log space might become reusable.

The following table briefly describes the values of the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view.

log_reuse_wait value	log_reuse_wait_desc value	Description
0	NOTHING	Currently there are one or more reusable virtual log files.
1	CHECKPOINT	No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). This is a routine reason for delaying log truncation. For more information, see Checkpoints and the Active Portion of the Log.
2	LOG_BACKUP	A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). Note Log backups do not prevent truncation. When the log backup is completed, the head of the log is moved forward, and some log space might become reusable.
3	ACTIVE_BACKUP_OR_RESTORE	A data backup or a restore is in progress (all recovery models). A data backup works like an active transaction, and, when running, the backup prevents truncation. For more information, see "Data Backup Operations and Restore Operations," later in this topic.
4	ACTIVE_TRANSACTION	A transaction is active (all recovery models). A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. For more information, see "Long-Running Active Transactions," later in this topic. A transaction is deferred (SQL Server 2005 Enterprise Edition and later versions only). A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions.
5	DATABASE_MIRRORING	Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only). For more information, see "Database Mirroring and the Transaction Log," later in this topic.
6	REPLICATION	During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only). For more information, see "Transactional Replication and the Transaction Log," later in this topic.
7	DATABASE_SNAPSHOT_CREATION	A database snapshot is being created (all recovery models). This is a routine, and typically brief, cause of delayed log truncation.
8	LOG_SCAN	A log scan is occurring (all recovery models). This is a routine, and typically brief, cause of delayed log truncation.
9	OTHER_TRANSIENT	This value is currently not used.

--------------------------------------------------------------------------------------------------------------------------------------------

Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/

By: Paul Randal

Posted on: November 13, 2013 12:33 pm

Here’s an interesting situation that sometimes crops up: you’re performing log backups, your transaction log isn’t growing, and yet the value of log_reuse_wait_desc in sys.databases for your database says LOG_BACKUP.

What’s going on?

I was actually just teaching this stuff today here in our last class of the year in Chicago.

Firstly, what is log_reuse_wait_desc? It’s a field in sys.databases that you can use to determine why the transaction log isn’t clearing (a.k.a truncating) correctly. Usually you query it when a log file is growing and you want to know why, using code like:

?

1 2 3 4

SELECT [log_reuse_wait_desc]     FROM [master].[sys].[databases]     WHERE [name] = N'Company'; GO

You can read about all the possible values in the Books online topic Factors That Can Delay Log Truncation.

One important thing to bear in mind is that the value returned is the reason that log clearing did not clear any VLFs (Virtual Log Files – see Understanding Logging and Recovery in SQL Server) that last time that log clearing was attempted. For instance, you may see a value of ACTIVE_BACKUP_OR_RESTORE but you know that your full backup has completed. This means that the last time log clearing was attempted, the backup was still running.

Back to the original question. If you have a transaction log that is not growing, and you’re taking regular log backups, but the log_reuse_wait_desc remains LOG_BACKUP, this is because zero VLFs were cleared when the previous log backup was performed.

How can that happen?

Imagine a database where there’s very little insert/update/delete/DDL activity, so in between your regular log backups there are only a few log records generated, and they’re all in the same VLF. The next log backup runs, backing up those few log records, but it can’t clear the current VLF, so can’t clear log_reuse_wait_desc. As soon as there are enough changes in the database that the current VLF fills up and the next VLF is activated, the next log backup should be able to clear the previous VLF and then the log_reuse_wait_desc will revert to NOTHING. Until the next log backup occurs and isn’t able to clear the current VLF, in which case it will go back to LOG_BACKUP again.

So LOG_BACKUP really means “either you need to take a log backup, or the log records that were backed up were all in the current VLF and so it could not be cleared.”

Hope this helps!