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 if the user set the Full-recovery mode and do the full-  Backup operation. This means if user create a new database and set the recovery mode to full or change the recovery mode of a Databa The behavior of the transaction log is the same as the simple recovery mode.

Q2:why The transaction log is growing abnormally?

A:There is many reasons of rapid growth of the transaction log. The most common reasons is 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 would not be 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 logs to the file, log space of the log file was divided into small parts which is called Virtual log Files (V LFs). Log Records is written into VLF and mark the VLF as "active". VLFs that is not active can is reused. Sql-server uses a process call log-truncation to check every VLFs is "active" or not. The VLFs is inactive was marked as truncated and can be reused. VLFs becomes inactive only when:

1. The transaction of which it is part have committed.
2. The database pages it changed has 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 a 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 on 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 is only can is reused after log-backup under Full-recovery mode. If The log space cannot be reused and there are no "free" space in the current log file, the database has 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 are not fully depend on the value of this column. As described in the above sections, if you had 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_reu Se_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, "This", "represent" is active and "0", 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 is not a 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], [Descripti On] from fn_dblog (null, NULL)

About Transaction Log