SQLSERVER2000 数据页面的LSN和数据库还原
SQLSERVER2000 数据页面的LSN和数据库还原
sql2000技术内幕
构造一个脏页表：constructs a dirty page table (DPT)
活动事务表：active transaction table（ATT）
- Phase 1: Analysis The first phase is a forward pass starting at the last checkpoint record in the transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might have been dirty at the time of the crash (or when SQL Server stopped). An active transaction table is built that consists of uncommitted transactions at the time of the crash.
- Phase 2: Redo This phase repeats history by returning the database to the state it was in at the time of the crash. The starting point for this forward pass is the minimum of all the LSNs in the DPT. The DPT is used to avoid reading pages that don't need recovering and to avoid overwriting nonlogged changes.
- Phase 3: Undo This phase moves backward from the end of the log, following the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time of the crash is undone so that none of its changes are actually reflected in the database.
Page LSNs and Recovery
Every database page has an LSN in the page header that uniquely identifies it, by version, as rows on the page are changed over time. This page LSN reflects the location in the transaction log of the last log entry that modified a row on this page. During a redo operation of transactions, the LSN of each log record is compared to the page LSN of the data page that the log entry modified; if the page LSN is less than the log LSN, the operation indicated in the log entry is redone, as shown in Figure�3-7.
Because recovery finds the last checkpoint record in the log (plus transactions that were still active at the time of the checkpoint) and proceeds from there, recovery time is short and all changes committed before the checkpoint can be purged from the log or archived. Otherwise, recovery can take a long time and transaction logs become unreasonably large. A transaction log cannot be purged beyond the point of the earliest transaction that is still open, no matter how many checkpoints might have occurred subsequently. If a transaction remains open, the log must be preserved because it's still not clear whether the transaction is done or ever will be done. The transaction might ultimately need to be rolled back or rolled forward.
Some SQL Server administrators have noted that the transaction log seems unable to be purged to free up space, even after the log has been archived. This problem often results from some process having opened a transaction, which it then forgets about. For this reason, from an application development standpoint, you should ensure that transactions are kept short. Another possible reason for this problem relates to a table being replicated using transactional replication when the replication log reader hasn't processed it yet. This situation is less common, though, because typically a latency of only a few seconds occurs while the log reader does its work. You can use DBCC OPENTRAN to look for the earliest open transaction, or oldest replicated transaction not yet processed, and then take corrective measures (such as killing the offending process or running the sp_repldone stored procedure to allow the replicated transactions to be purged).
每一个数据页面都有一个LSN号在页面头部，这个lsn号能唯一标识页面的版本，能反映过去时间页面的变化
在sql6.5没有lsn号，只有一个timestamp时间戳能标识页面的变化。这个页面LSN影响事务日志中最后的页面行数据修改的日志入口的位置。
在事务日志重做期间，事务日志里的每行日志记录会比较数据页面的LSN号，以便验证数据页面是否有修改；如果数据页面的LSN小于事务日志记录的LSN，那么做一个重做日志的操作
因为还原数据库会检查在事务日志里的最后的ckpt记录（加上在做ckpt的时候事务依然是活动事务），那么从哪里开始处理，还原时间是比较短的，所有的在ckpt前已经提交的修改在事务日志文件ldf中能
清理掉。否则，还原会花费很长时间和事务日志会变得很大。当一个老事务还在打开状态的时候是不能被清理的，不管随后做了多少次ckpt。如果一个事务保持打开状态，那么日志必须要保存的因为
这个事务依然没有清除无论事务已经提交还是将来会提交。事务最终会被回滚或重做
一些sqlserver DBA已经注意到事务日志似乎不能释放空余空间当清除了的时候，尽管事务日志已经被归档。这个问题是通常是因为某些进程打开了一个事务，忘记了关闭事务
对于这个原因，站在开发者的角度，你应该确保那个事务保持短小的。对于这个问题的另一个可能原因是关系到表的复制使用事务复制，当日志读取代理还没有处理这个日志的时候
这种情况是比较少，因为典型只会延迟几秒钟当日志读取代理处理他的工作的时候，你可以使用DBCC OPENTRAN（隐式事务看不到）去检查最早的事务或者最老的还没处理的复制事务
然后采取正确的措施，例如杀死进程或者运行 sp_repldone 存储过程去运行复制事务的清除
