文档引子
最近，SQLserver环境中的SQL always on 因事务爆满 导致磁盘持续告警， 通过这次事件，记载下SQLserver AG的事务日志处理的正确方式，同时也把Oracle以及PG的相关的checkpoint问题一并做个简单的总结，并且只从结果的角度给出过程，至于具体的理论，请移步官方文档查阅。
SQLserver检查点
-- 首先明确一点，SQL AG 就是SQLmirroring的升级版，同时在这两种架构下， 数据库模式都是full，而不是simple，也不能改成simple（注意甄别网上论说的）
那么 正确的truncate log的方法是： 备份事务日志，同时确保 log_reuse_wait_desc 必须是 【0】
同时通过以下命令查看，不能收缩的原因
DBCC SQLPERF(LOGSPACE)
GO
SELECT name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc
FROM sys.databases
GO
DBCC loginfo()，可以看到该数据库的所有VLF的状态都为2，也就是active状态。
-- 官方提供很完善的处理方式
https://learn.microsoft.com/zh-cn/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-2017
（介绍了log_reuse_wait的13个参数的意义和对应的解决方案）
-- 摘抄点老外写的原著
If you are in simple recovery model
Just shrink the file! If the file doesn't shrink to the size you want, then try again, and again. If it still doesn't shrink to the size you want, then read this (the "Shrinking of transaction log file" section).
If you are in full recovery model
... and this is where you want to be, then you have to make a decision:
Is it OK to break the transaction log backup chain?
By this we mean the chain of log backups you may have. If you don't have any prior log backups (perhaps not even a prior database backup), then it is OK to break the chain of log backups. If you do have earlier log backups and want to be able to restore from them up to any point in time then it isn't OK to break the chain of log backups.
OK to break the chain of log backups. This is easiest:
Set recovery model to simple.
Do the shrink according to the steps above (for a database in simple recovery model).
Set recovery model to full.
Perform a full database backup, so your scheduled log backups have somewhere to begin from.
Not OK to break the log backup chain. Then you backup the log (which can produce a huge log backup file, and consequently take some time), and then shrink the file. If the file doesn't shrink to the size you want, then backup log and shrink again, and again. If it still doesn't shrink to the size you want, then read this (the "Shrinking of transaction log file" section).
ORACLE的检查点
-- checkpoint 官方文档介绍：https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/LOG_CHECKPOINT_INTERVAL.html
-- checkpoint 需要和 SCN 等理论，移步google或者https://docs.oracle.com查阅
-- 这里我们只讲 checkpoint的意义和什么时候最好是有这么一个操作。
Oracle checkpoint 的意义在于保持数据库中的内存数据都能即使写入磁盘，
Oracle checkpoint 有多种自动触发方式，如日志自动切换时/shutdown immediate时/表空间offline/数据库执行冷备份时/删除extend等
也有手动触发方式，如alter system switch logfile ,alter system checkpoint
Oracle checkpoint 需要手动执行的情况一般发生在 使用数据泵进行数据迁移时， 为了保证正在运行的数据库能保持数据的一致性 在 expdp中加入参数 flashback_time=systimestamp
PostgreSQL检查点