SQLSERVER中的log block校验（译） 来自： http://sankarreddy.com/2010/03/transaction-log-block-checksum/ 阅读下文之前可以先看这篇文章：再谈SQL Server中日志的的作用 从SQL2005开始，checksum校验被增加为页面校验的一个选项，最广为人知的两个checks
SQLSERVER中的log block校验（译）
来自：http://sankarreddy.com/2010/03/transaction-log-block-checksum/
阅读下文之前可以先看这篇文章：再谈SQL Server中日志的的作用
从SQL2005开始，checksum校验被增加为页面校验的一个选项，最广为人知的两个checksum选项就是备份时候的checksum选项和页面校验checksum选项
另外一个比较少人知道的选项就是log block checksum
什么是log block?
Log Block在宋沄剑写的文章里已经介绍的很清楚了
Log Block是日志写入持久化存储的最小单位，Log Block的大小从512字节到60K不等，这取决于事务的大小，
那些在内存还未被写入持久化存储的Log Block也就是所谓的In-Flight日志。以下两个因素决定Log Block的大小：
事务提交或回滚 Log Block满60K会强制Flush到持久化存储，以保证WAL 因此当一个事务很大时（比如说大面积update），
每60K就会成为一个Log Block写入持久化存储。而对于很多小事务，提交或回滚就会成为一个Block写入持久化存储，因此根据事务的大小，
LOG Block的大小也会不同。
大家知道page checksum就是校验写入磁盘的页面，而log block checksum其实就是校验写入磁盘的log block
因为当log block写入磁盘的时候也有可能损坏的，所以也需要校验，本人觉得log block checksum校验的重要性不亚于页面校验
因为SQLSERVER靠日志来保证事务一致性，如果日志损坏了，还如何靠这些日志进行redo，undo log？？
我如何打开log block checksum这个选项？
页面checksum可以使用
<span>1</span> <span>ALTER</span> <span>DATABASE</span> xx PAGE_VERIFY CHECKSUM
Log block CHECKSUM选项会在当页面校验选项被设置为checksum的时候自动开启，即当执行上面那条语句的时候SQLSERVER已经
帮您自动开启了log block checksum。注意：当开启过页面校验选项被设置为checksum之后
后来又转回页面校验选项“TORN_PAGE_DETECTION”时候并不会影响log block checksum
log block CHECKSUM的工作方式
当SQLSERVER提交一个事务并且发出一个事务日志写，在log block写入磁盘之前，SQLSERVER就是计算出这个log block的checksum值
并附加在log block的头部，实际上这个结构跟数据页面非常相似。当在备份和还原（还有其他的活动）期间读取事务日志的时候，checksum值
就会被重新计算并且检查先前写入到log block头部的那个checksum值
大家可以参考一下页面checksum：
SQL 2005中的checksum功能
http://blogs.msdn.com/b/apgcdsd/archive/2012/04/09/sql-2005-checksum.aspx
log block CHECKSUM有什么帮助？
如果存储在log block头部中的checksum值跟在读取log block时用相同的方法再计算一次的checksum值不匹配
那么说明IO子系统已经对您的数据造成一定的破坏。这个时候你应该马上检查IO子系统找出问题的根源
TSQL里面的checksum函数、页面checksum、备份checksum、log block checksum是使用相同的算法吗？
答案是：NO
TSQL里的 CHECKSUM 函数算法是不同于PAGE/BACKUP/Log Block CHECKSUM的，
PAGE/BACKUP/Log Block CHECKSUM使用简单和更有效的算法相比于TSQL的checksum函数
如有不对的地方，欢迎大家拍砖o(∩_∩)o
What are stored procedures in MySQL?May 01, 2025 am 12:27 AMStored procedures are precompiled SQL statements in MySQL for improving performance and simplifying complex operations. 1. Improve performance: After the first compilation, subsequent calls do not need to be recompiled. 2. Improve security: Restrict data table access through permission control. 3. Simplify complex operations: combine multiple SQL statements to simplify application layer logic.
How does query caching work in MySQL?May 01, 2025 am 12:26 AMThe working principle of MySQL query cache is to store the results of SELECT query, and when the same query is executed again, the cached results are directly returned. 1) Query cache improves database reading performance and finds cached results through hash values. 2) Simple configuration, set query_cache_type and query_cache_size in MySQL configuration file. 3) Use the SQL_NO_CACHE keyword to disable the cache of specific queries. 4) In high-frequency update environments, query cache may cause performance bottlenecks and needs to be optimized for use through monitoring and adjustment of parameters.
What are the advantages of using MySQL over other relational databases?May 01, 2025 am 12:18 AMThe reasons why MySQL is widely used in various projects include: 1. High performance and scalability, supporting multiple storage engines; 2. Easy to use and maintain, simple configuration and rich tools; 3. Rich ecosystem, attracting a large number of community and third-party tool support; 4. Cross-platform support, suitable for multiple operating systems.
How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AMThe steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.
What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AMMySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.
What is MySQL clustering?Apr 30, 2025 am 12:28 AMMySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe
How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AMOptimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.
How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AMTooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
SublimeText3 English version
Recommended: Win version, supports code prompts!
Zend Studio 13.0.1
Powerful PHP integrated development environment
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Notepad++7.3.1
Easy-to-use and free code editor
