About Transaction Log

 Q1.Why the log space cannot grow even if the recovery mode is full-recovery?

A: This is because that the full-recovery mode takes effect only when the user set the full-recovery mode and do the full-backup operation.  This means that, when user create a new database and set the recovery mode to full or change the recovery mode of a database to full from simple, the behavior of the transaction log is the same as the simple recovery mode.

Q2: Why the transaction log is growing abnormally?

A: There are many reasons of rapid growth of the transaction log. The most common reasons are listed below:

(1) There is at least one transaction closed abnormally. For example, aborting a long running  transaction in SQL-SERVER Management Studio and don't execute "commit" or "rollback" command manually. The SQL-SERVER Management Studio will not automatically commit the transaction when user abort a transaction.

(2) Under full-recovery mode, did not backup log for a long time.

Here, we explain the log record mechanism simply.

When recording log to the file, log space of the log file are divided into small parts which is called Virtual Log Files(VLFs). Log records are written into VLF and mark the VLF as "active". VLFs that are not active can be reused. SQL-SERVER uses a process call log-truncation to check every VLFs are "active" or not. The VLFs are inactive is marked as truncated and can be reused. VLFs becomes inactive only when:

1. The transaction of which it is part has committed.
2. The database pages it changed have all been written to disk by a checkpoint.
3. The log record is not needed for a backup (full, differential, or log).
4. The log record is not needed for any feature that reads the log (such as database mirroring or replication).

The occurrence of the log truncation is depends on the recovery mode of the database:

1. Under simple recovery mode, the log truncation occurs after every checkpoint.

(More information about the checkpoint can be found here: https://msdn.microsoft.com/en-us/library/ms189573.aspx). 

2. Under full and bulk-update recovery mode, the log-truncation occurs only after the log-backup and occurred a checkpoint.

So,  log space only can be reused after log-backup under full-recovery mode. If the log space cannot be reused and there is no "free" space in the current log file, the database have to increase the size of the log file.

Q3: How can I know what is the recovery mode of my database?

 A:Use following statement :

SELECT [name] ,[database_id] ,[log_reuse_wait] ,[log_reuse_wait_desc] FROM [sys].[databases]

The value of "recovery_mode_desc" column represent the recovery mode of the database.

However, note that, the behavior of the logging is not fully depend on the value of this column. As described in the above section, if you have not done a full-backup operation yet, the logging behavior of full-recovery mode is just the same as simple mode. So, we should look at the "log_reuse_wait_desc" column. After a full backup  on site_manager11, and execute some DDL operations on this database, we can see the value of "log_reuse_wait_desc" becomes "LOG_BACKUP" which means, only after log backup, the space of the log file can be reused.

Q4: How can I know the used percentage of the log-file?

A:

(1) Use following statement:

DBCC SQLPERF (LOGSPACE)

(2)We can see the percentage and available free size of the log space when shrinking.

Q5: How can I see the VLFs status?

A: Use following statement:

use site_manager11;

DBCC LOGINFO;

"2" in "Status" column represent that this VLF is active and "0" represent this VLF can be truncated.

Q6: How can I see the content of the log file?

A: Generally, we use third-party tools to export the transaction log. For example ApexSQL tool etc. But, generally, these tools are not free to use.

We can use the "fn_dblog" to show the transaction log of a database. The " fn_dblog "  is one of the  undocumented functions of the SQL-SERVER.

SELECT * FROM fn_dblog(NULL, NULL)

We can see the information about operations and transaction_ids etc.

Also, you can specify the columns to be displayed.

SELECT [Current LSN], [Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], [Transaction Name], [Description] FROM fn_dblog(NULL, NULL)