Check logblock in SQLSERVER)

Source: Internet
Author: User
The logblock check in SQLSERVER is from sankarreddy. com201003transaction-log-block-checksum can read the following before reading this article: Talk about the role of SQL server logs from SQL2005, checksum check is added to a page check option, the most widely known two checks

SQL Server in the log block check (translated) from: http://sankarreddy.com/2010/03/transaction-log-block-checksum/ read before the following can read this article: Talk about the role of SQL Server logs from SQL2005, checksum verification is added as an option for page verification, the two most widely known checks

Check log block in SQLSERVER)

From:Http://sankarreddy.com/2010/03/transaction-log-block-checksum/

Before reading the following, let's first read this article: Let's talk about the role of logs in SQL Server.

From SQL2005, checksum verification has been added as an option for page verification. The two most widely known checksum options are the checksum option and page verification checksum option during Backup.

Another option that few people know isLog block checksum

What is log block?

The Log Block has been clearly described in the article written by Song xiaojian.

Log Block is the minimum unit for writing logs to persistent storage. The size of Log Block ranges from 512 bytes to 60 kb, depending on the transaction size,

Log blocks that are not written to persistent storage In the memory are also called In-Flight logs. The following two factors determine the Log Block size:

When the transaction commit or rollback Log Block is 60 k full, it will force Flush to persistent storage to ensure that WAL is used when a transaction is large (for example, large-area update ),

Every 60 k is written into a Log Block for persistent storage. For many small transactions, commit or rollback will become a Block write persistent storage, so according to the transaction size,

The LOG Block size is also different.

As you know, page checksum is used to verify the page written to the disk, while log block checksum is used to verify the log block written to the disk.

Because when a log block is written to a disk, it may be damaged, so it also needs to be verified. I think the checksum verification of log block is no less important than page verification.

Because SQLSERVER relies on logs to ensure transaction consistency. If the logs are corrupted, how can we rely on these logs for redo and undo logs ??

How do I enable the log block checksum option?

Page checksum can be used

1 ALTER DATABASE xx PAGE_VERIFY CHECKSUM

The Log block CHECKSUM option is automatically enabled when the page check option is set to checksum. That is, when the preceding statement is executed, SQLSERVER has

Log block checksum is automatically enabled for you. Note: After page verification is enabled, it is set to checksum.

Later, the page verification option TORN_PAGE_DETECTION does not affect log block checksum.

How log block CHECKSUM works

When SQLSERVER commits a transaction and sends a transaction log write, SQLSERVER calculates the checksum value of the log block before the log block is written to the disk.

And appended to the log block header. In fact, this structure is very similar to the data page. When the transaction log is read during Backup and restoration (and other activities), the checksum Value

It will be re-computed and the checksum value previously written to the log block header will be checked.

You can refer to the checksum page:

Checksum in SQL 2005
Http://blogs.msdn.com/ B /apgcdsd/archive/2012/04/09/sql-2005-checksum.aspx

What is the help of log block CHECKSUM?

If the checksum value stored in the log block header is the same as that used to read the log block, the calculated checksum value does not match.

It indicates that the IO subsystem has damaged your data. At this time, you should immediately check the IO subsystem to find out the root cause of the problem.

Does the checksum function, page checksum, backup checksum, and log block checksum in TSQL use the same algorithm?

The answer is:NO

The CHECKSUM function algorithms in TSQL are different from those in PAGE/BACKUP/Log Block CHECKSUM,

PAGE/BACKUP/Log Block CHECKSUM is a simple and more effective algorithm than the checksum function of TSQL.

If anything is wrong, you are welcome to make a brick o

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.