SQLSERVER中的log block校验（译）
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
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
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to solve the problem that the object named already exists in the sqlserver database
Apr 05, 2024 pm 09:42 PM
For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.
How to import mdf file into sqlserver
Apr 08, 2024 am 11:41 AM
The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.
What to do if the sqlserver service cannot be started
Apr 05, 2024 pm 10:00 PM
When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.
How to check sqlserver port number
Apr 05, 2024 pm 09:57 PM
To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.
Where is the sqlserver database?
Apr 05, 2024 pm 08:21 PM
SQL Server database files are usually stored in the following default location: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data The database file location can be customized by modifying the database file path setting.
How to recover accidentally deleted database in sqlserver
Apr 05, 2024 pm 10:39 PM
If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.
How to solve Java connection SqlServer error
May 01, 2023 am 09:22 AM
The problem was found that this time I was using the SqlServer database, which I had not used before, but the problem was not serious. After I connected the SqlServer according to the steps in the requirements document, I started the SpringBoot project and found an error, as follows: At first I thought it was a SqlServer connection. There was a problem, so I went to check the database and found that everything was normal. I first asked my colleagues if they had such a problem, and found that they did not, so I started my best part, facing Baidu. programming. The specific error message I started to solve was this, so I started Baidu error reporting: ERRORc.a.d.p.DruidDataSource$CreateCo
How to delete sqlserver if the installation fails?
Apr 05, 2024 pm 11:27 PM
If the SQL Server installation fails, you can clean it up by following these steps: Uninstall SQL Server Delete registry keys Delete files and folders Restart the computer
