(go) queries on SQL Server database transaction logs

This article was reproduced from the blog http://www.cnblogs.com/lyhabc/archive/2013/06/10/3130856.html of the BirchQueries on SQL Server database transaction logs

I can't touch SQL Server.

Experimental environment: SQLSERVER2005 SP4,WINDOWS7

I was not in the mood to write articles, anyway, no one to read, about my article mentioned in the question, some can be from the end of the MSDN Supplement to find the answer, and some have no answer

According to the CSDN blog article, you can take a look first, and then continue to look down, because the following will be quoted in the CSDN blog some of the content

Introduction to SQL Server transaction logs

The first question: Why do I make a full backup of the database, why does the number of transaction log records increase, then back up again and again, and then back up again?

After the backup, it becomes 100 rows of records.

Sometimes not fixed will add 50 rows of records, and sometimes when you back up the time will be changed back to 50 rows of records

And the number of virtual log files will be reduced, from the original 45 into 35

After backup

That's how I backed it up.

When I back up the transaction log and truncate the log, there are 10 rows left, the backup transaction log does not reduce the physical size of the transaction log, and it needs to shrink the transaction log.

When I perform a full backup every time, log space used will grow

1 DBCC SQLPERF (logspace)

And according to the article I provided above, "SQL Server transaction log Introduction"

When SQL Server takes virtual log files 1 and 2 as a reusable zone, the transaction log is truncated accordingly (Truncate)

It is important to note that the physical log size changes as well. If the database is running under a full or bulk log recovery model, then the area from LSN45 to 49 will be deleted (delete),

And until the transaction log is backed up, the space in this area is reused.

But I also backed up the transaction log and truncated the log, but the log space used and the logfile physical size did not change

SQL Server should use the virtual log file as the unit, and if there are no LSN in a virtual log file, truncating the transaction log will

LSN deletion in the part of the virtual log file without any LSN

Here's a bit of truncation and reuse .

truncation : If a backup transaction log is performed and the log is truncated, the LSN log records in this part of the virtual log file are deleted, but the physical size of the entire transaction log file does not become smaller

Reuse : You need to perform a backup of the transaction log and truncate the log, and this part of the virtual log file is reused so that you do not have to increase the size of the transaction log file

That is, you want to back up the transaction log and truncate the log, and then the virtual log files that were deleted by the LSN log records can be reused, but will SQL Server delete the virtual log files?

MSDN does not say that MSDN simply says that SQL Server creates and extends virtual log files, but does not mention that virtual log files are automatically deleted

Reuse back to the previous space and recycle it over and over again.

Question two: But how many LSN per virtual log file is loaded, I don't know, it could be random?

And when reused, SQL Server should not be discontinuous.

Like the following situation

LSN LSN, LSN lsn LSN LSN, LSN-"

1 2 3 10 5 6 7

LSN 1, 2, 3 belong to a virtual log file a

LSN 5, 6, 7 are owned by another virtual log file B

LSN 10 belongs to a virtual log file C

Because the LSN in the virtual log file C is not deleted, the LSN 10 is skipped when the log is reused and then the log is reused, but I don't think SQL Server is like this.

I think the SQL Server reuse log should be the following

LSN LSN, LSN lsn LSN LSN, LSN-"

1 2 3 4 5 6 7

It's going to go on and on. Some virtual log files are not deleted in the middle, ensuring the sequence of the LSN sequence

After I backed up the transaction log and truncated, the physical size and the space used did not change.

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

First LSN: The first LSN after the second checkpoint

Last LSN: The LSN before the last checkpoint

Checkpoint LSN: Same as the first LSN

Full LSN: Same as the first LSN

The above figure is when I restore the database interface, you can back up the database, and then restore the database, look at the restored backup set is not the same time you just back up

It's easy to draw a picture.

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

1 DBCC LOG ([Dlgpos])

Here's the operation field in the transaction log, and the Operation field shows what the LSN is doing.

For example: Lop_begin_ckpt,ckpt:checkpoint, if you see operation is LOP_BEGIN_CKPT, indicates that the LSN is doing a checkpoint operation

Because SQL Server will do checkpoint when backing up a database, you will see that the first LSN is associated with the LSN of the lop_begin_ckpt operation.

The first LSN is the same when you translate the 16 binary into 10 and restore the database.

And the current LSN just shows the LSN number of each log record, no special meaning, so yesterday I said, Currentlsn equals the first LSN is wrong

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

There is a "location" East, Position field and restore the database when the location is one by one corresponding, when I every full backup database will increase one, or back up the transaction log once will add a

But the full backup is separate from the transaction log backups, see

1 Use  [msdb]2 GO3 SELECT  [backup_set_id], [backup_finish_date], [database_name], [first_lsn],4         last_lsn, [Checkpoint_lsn], [is_copy_only],[position]5 from    [dbo].[ Backupset] WHERE [database_name]= ' Dlgpos ' 6 ORDER by [Backup_finish_date]

Question 3: But why did you suddenly return to 9 from 64? Why does it break? I don't know what the position field is for.

Reference articles:

Http://msdn.microsoft.com/zh-cn/library/ms190925.aspx

http://msdn.microsoft.com/zh-cn/library/ms189085 (v=sql.90). aspx

http://msdn.microsoft.com/zh-cn/library/ms179355 (v=sql.105). aspx

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

From the MSDN Supplement .

In one of the reference articles above

Transaction Log Physical Architecture

I'll extract the important parts.

The SQL Server database engine internally divides each physical log file into multiple virtual log files. The virtual log file does not have a fixed size, and the number of virtual log files contained in the physical log file is not fixed. The database engine dynamically chooses the size of the virtual log file when creating or extending a virtual log file. The database engine tries to maintain a small number of virtual log files. After the virtual log file is extended, the size of the virtual log file is the sum of the existing log size and the new file increment size. Administrators cannot configure or set the size or number of virtual log files.

Virtual log files can affect system performance only if the log files are defined with a smaller size and growth_increment value. If these log files grow to a large number of small increments, they will have many virtual log files. This reduces the speed of database startup and log backup and restore operations. We recommend that you assign a size value that is close to the final desired size for the log file, and also assign a relatively large growth_increment value

The transaction log is a wrapped file

Look at this picture.

Question 4: Can minlsn be the same as the first LSN? And is the first LSN likely to be in the same position as MINLSN or in the back of the first LSN?

I think it's possible, like the <sqlserver transaction log introduction > inside, assuming LSN48 is a checkpoint, then MinLSN is ranked behind the first LSN

Why is LSN50 a minlsn?

According to MSDN, MinLSN is the boundary point that defines the truncation log

MinLSN in front of the log records can be truncated, the log records after MINLSN are not truncated

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

Correct what you said today, regardless of whether you are making a full or differential backup or a log backup, all the logs in the transaction log file will be saved in the Bak file

No matter how many times you make a database backup or restore, these transaction logs will not disappear and shrink the transaction log files.

Unless you are backing up the transaction log and truncating the log

So there is no only part that is saved to checkpoint, but when you execute the backup command, the last LSN is also saved to the Bak file!!!!!!!

If you don't believe it, you can execute the following SQL statement before you fully backup the database, and then execute it after the backup is complete to see if the transaction log records are the same

1 Use [adventureworks]2 GO3 DBCC LOG ([AdventureWorks])

and truncate the transaction log to see if it's the same as before.

MSDN says physical log files, logical log files, virtual log files, Lsn,checkpoint should look like this

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

It's very annoying that some books in the country copy the MSDN content, when it comes to the transaction log, copy MSDN, originally MSDN is very general

I can only say "fuck" to them.

Because there are so few documents and related articles, I can't go into it, even the Rmaio heroes in the MSDN forum say that there are very few documents.

With regard to the usage of Fn_dblog , no one can give, just say that Microsoft is not publicly

http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/271b9073-61c5-4049-b918-6ab5dbb26d3e

http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/19546d80-6cf7-449c-bacd-421a1d32ab5f

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

20130611 Supplement

MSDN article: Transaction log architecture and management

The log sequence number (LSN) of this first log record, called the minimum Recovery LSN (MinLSN)

So is the first LSN a minlsn? Let's do an experiment.

Run the following statement first

1 use [Gposdb]--the database that needs to view MINLSN log information 2 GO3--DBCC log is the same as the number of records [Fn_dblog], but [Fn_dblog] is more verbose than DBCC log 4 SELECT * from [SYS]. [Fn_dblog] (null,null) 5 DBCC LOG ([Gposdb])

Then perform a full database backup

You'll find that the backup was different from the MinLSN after the backup and looked

Meaning MinLSN = First LSN

I made some adjustments to the results of fn_dblog and more interesting things.

1 SELECT * into # #TDBLOG from SYS. [Fn_dblog] (null,null) 2 Select  * FROM [# #TDBLOG]3 SELECT  [Current LSN], [operation], [Checkpoint Begin], [Checkpoint end],4         [Minimum LSN], [Dirty Pages], [SPID], [Transaction name],5         [Description]6 from    [# #TDBLOG] ORDER by [current LSN] ASC

Illustrates that the first LSN and the last LSN are in the middle of two checkpoint, noting Operation:lop_begin_ckpt and lop_end_ckpt

When checkpoint, there are 7 dirty pages written to the disk.

Let's run the following statement

1 SELECT  [current LSN], [operation], [Transaction id]2         [Minimum LSN], [Dirty Pages], [SPID], [Transaction name],3         [Description]4    from [# #TDBLOG] ORDER by [current LSN] ASC

Everyone, look, I'm in SSMs. The SPID of the query window for backup database is 59,sqlserver is the first to do checkpoint, and then do the backup database operation

How do I know which database the results of the fn_dblog output correspond to?

Because this function is just beginning to be used, I do not know how to specify the log information of a database of output, and this function has a field of [Database Name], but this field output is null

Then I looked at the SPID field and found that one of the rows had database-initiated information because I specified the following SQL statement in the Query window, use AdventureWorks,

Take a look at SQL ERROR LOG, really is SPID20 this process started the database, and then I use other databases, found the same

So you have to look at the log information of a database to use the database you want to view the log

1 Use [adventureworks]2 GO

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

As for question 3, this " location " means " log chain ".

Log Chain
A sequential sequence of log backups is called a "log chain." The log chain starts with a full backup of the database. Typically, a new log chain is started only when the database is backed up for the first time, or after the recovery model is switched from the simple recovery model to the full or bulk-logged recovery model. The existing log chain remains the same unless you choose to overwrite the existing backup set when you create a full database backup. When the log chain remains intact, you can restore the database from any full database backup in the media set, and then restore all subsequent log backups before the corresponding recovery point. The recovery point can be the end of the last log backup, or a specific recovery point in any log backup. For more information, see Transaction Log Backups (SQL Server).
To restore a database to a point of failure, you must ensure that the log chain is complete. In other words, a sequential sequence of transaction log backups must be able to continue to the point of failure. The start of this log sequence depends on the type of data backup that you restore: Database backup, partial backup, or file backup. For database or partial backups, the sequence of log backups must start at the end of a database backup or a partial backup. For a set of file backups, the sequence of log backups must be continued from the beginning of the entire set of file backups. For more information, see Apply transaction log backups (SQL Server).

Because YOURSQLDBA is installed on my computer, he automatically performs a daily transaction log backup with a full backup job, so this log chain is formed.

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

Add: In fact, Checkpoint also belongs to a log record, the picture should be like this

MinLSN: As you can see, the latest LSN is 148,147 is checkpoint, before this checkpoint transaction 1 has been completed, and transaction 2 has not been completed,

So the corresponding MINLSN should be the beginning of transaction 2, that is, 142.

From MINLSN to the logical end of the log, it is called the active log.

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

According to the Careyson Warrior in the garden, the first question

Why does the log record increase after a full backup, sometimes after the backup is over, and sometimes after the backup is done?

To say this, we must first look at this article:SQL Server 2008 storage structure of DCM, BCM

It says that the database has a System page DCM page that records which section of a file has been modified since the latest full database backup

We do an experiment, do a full database backup, and then do a full database backup, two times the full database backup, you do not do anything to the database

Then enter the following SQL statement in SSMs

1 DBCC TRACEON (3604,-1) 2 DBCC page ([dlgpos],1,6,3)--actually 7th page

You will see that even if you do not do anything to the database, some data pages in the database will still be modified.

However, no one knows how much data has been modified, but it is certain that SQL Server has modified some data so that the transaction log increases or decreases after the full backup

And in the log records, you'll see

Lop_set_bits->lcx_diff_map bitmap

Lop_file_hdr_modify->lcx_file_header Database File Header

LOP_MODIFY_ROW->LCX_BOOT_PAGE_CKPT Database Startup page

These are all modifications to the database system page

If you don't understand the DCM page, take a look at the article in Careyson.

A differential backup relies on a bitmap for maintenance, a bit corresponds to a zone, and since the last full backup, the modified zone is set to 1.

The area that is set to 1 in bitmap is backed up by a differential backup. After the next full backup, all the bits in the bitmap are reset to 0.

http://www.cnblogs.com/CareySon/archive/2012/02/17/2355200.html#2702773

There are many lop_lock_xact->lcx_null in the transaction log, which is estimated to be used to lock the system's reserved pages.

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

(GO) queries to the SQL Server database transaction log