34,874
社区成员
发帖
与我相关
我的任务
分享为什么数据库日志文件这么大
微软亚太区数据库技术支持组 官方博客
为什么数据库日志文件这么大
我们经常遇到客户问和数据库日志相关的问题。比如：
1. 我的数据库只有10GB，为什么我的日志文件有40GB，而且还在增长？
2. 我无法对数据库进行更改操作了。报错说我的日志空间已满。
3. 我数据库的恢复模式明明是Simple，为什么还有日志，还那么大？
4. 我的数据库一直处于恢复状态，其他应用程序都报连接出错。
归根结底一句话，我数据库的日志的增长远远超过我的预期，而导致我的业务受到影响。数据库事务日志（Transaction Log），或简称日志文件（Log）文件的异常增长有时候对数据库的应用的影响是致命的。
想象一下，在你的业务最繁忙的时候，你的数据库由于日志文件占满整个磁盘空间，而无法使用。有人说，很简单啊！我把日志文件删掉。如果你真的这样做，你会发现数据库日志文件正被SQL Server进程锁定，而无法删除。于是，你停止SQL Server服务，然后很开心的将Transaction Log文件删除了，节省了大概200GB的空间。再于是，你很“开心”的发现你的数据库无法启动，因为缺少日志文件。如果你在日志文件原有的路径创建一个同名的日志文件，好吧，既然你那么坚决，SQL Server会告诉你日志文件格式已损坏。突然，你发现自己有个很好的习惯，当你删除那个200GB的日志文件的时候，你只是把它扔到了垃圾箱，于是你把它找了回来，重新启动你的SQL Server，很开心的发现，它成功的启动了。于是你松了一口气，泡上一杯咖啡，考虑是不是找你的Disk Vendor申请更多的磁盘空间。突然你的电话铃声大作，好多连接到你的数据库的应用程序的管理人员都向你抱怨他们无法连接到数据库。你打开SSMS，发觉那个数据库一直处于“恢复中”（In Recovery）状态，好像它永远不会终止。好吧，你是不是有想砸机器的冲动了，并且想气势汹汹的告诉全世界，这是微软SQL Server的另一个Bug？
如果，你认真的读完这篇文章，你会发现这只不过是SQL Server的另一个不恰当应用的一个案例。
什么是日志文件（Transaction Log）
简而言之，日志文件通过一个简化的格式记录了所有对数据库的修改操作，包括Insert， Update和Delete等能够帮助你重现对数据库内容修改的操作。日志文件的后缀名为*.LDF。
数据文件 （*.mdf）Vs. 日志文件 （*.ldf）
有人本以为数据文件是存储数据的“核心”文件，但是很诧异的发现，原来日志文件存储了所有的数据修改。有些人会想到，通过日志文件，其实我就可以把我的数据库恢复到某个特定时间点的状态。这句话，在某些限制条件下是对的，将在下文进行讨论。
那么，数据文件和日志文件的关系是什么呢？他们如何工作？
SQL Server是一个很依赖于内存（Memory）使用的系统。任何一个对于数据的读入/修改都是和内存进行交互。当一个修改操作发生时，修改的将是内存中所对应的数据页。然后，这个操作将会实时地被写入日志文件。但是，该修改被写入数据文件（*.mdf）的时间，只有在Checkpoint发生的时候。Checkpoint本身也会被写入日志文件。
Checkpoint是SQL Server的一个自动的行为。默认间隔为1分钟。http://msdn.microsoft.com/en-us/library/ms188748.aspx 所以，你的数据文件和日志文件的数据差异间隔会有1分钟。如果在某些情况下，Checkpoint并没有如预期那样每1分钟发生一次，数据差异间隔会更长。
日志文件如何影响我数据库的启动？
无论你的SQL Server启动，或者你将某数据库重备份中恢复，或者其他的一些情况，总之在你的数据库能够被正常使用之前，你的数据库都会进入 Recovery的状态。http://msdn.microsoft.com/en-us/library/ms190442.aspx 如果这一个步失败，那么你的数据库就会进入Suspect状态而无法正常使用。在一些特殊情况下，这个Recovery所花费的时间会很长。在数据库进入Online状态之前，我们都不能认为SQL Server可以被正常使用了。
那么在Recovery中做了什么呢？
我们可以在你的SSMS中运行如下语句。在运行之前，你可以先按下Ctrl+T来把结果转化成Plain Text格式。
sp_readerrorlog会返回自从最近一次SQL Server服务启动，或者Error Log被回收开始的SQL Server的Error Log信息。这里有两个词汇可能会让你感到疑惑。首先，这里的Log和我们上文所说的日志文件（Transaction Log）中的Log不是一件事情。其次，所谓的Error Log，并不是说这里出现了Error。SQL Server使用这个Error Log记录了很多诊断信息。所以，你可以认为这只是一个普通的记录了很多SQL Server相关信息的日志文件。
在结果返回中，你可能会找到上面的这些信息。引号中为在你当前数据库实例下的各个数据库名。
以数据库‘CaseLync’为例。由于Error Log会记录当前SQL Server实例下的所有数据库的相关信息，所以你可能会看到和这个数据库相关的日志被顺序地分布在日志的不同地方。在上图中，第一和第二行提及两个词：rolled forward和rolled back。这两个词和我们本文中所提及的Transaction Log息息相关。
在前文中，我们提到过数据修改的过程：先同步的被写入Transaction Log，然后再Checkpoint发生的时候被同步到Data File里。那么，如果我的事物（Transaction）没有被提交（Commit）或者回滚（Rollback），那么这个数据修改是否也在日志文件（Transaction Log）还是在数据文件里（Data File）？
答案是：都在！
SQL Server在记录数据改变时，并不会区分该语句是否有显示的进行事物操作（Begin Transaction，Commit / Rollback Transaction）, 或者该事务是否有完成。SQL Server会忠实地记录所有的修改操作。而Begin Transaction和Commit / Rollback Transaction本身也是日志文件需要记录的操作之一。
由于对于事物日志的修改要先于对于数据文件的修改，所以当你的数据库处于Recovery的状态时，那么Transaction Log就会从最近的一个Checkpoint点开始做如下操作：
1. 如果该操作已在在Transaction Log中，而不在Data File之中，并且如果它使用显式的Transaction操作符而且处于Commit状态，则会发生一次Rolled Forward操作，将该操作同步到Data File之中。
2. 如果该操作已在在Transaction Log中，而不在Data File之中，并且如果它使用显式的Transaction操作符而且处于Rollback Transaction状态，则会发生一次Rolled back操作，将Data File之中的相关数据修改回滚到Transaction发生之前。
3. 如果该操作已在在Transaction Log中，而不在Data File之中，并且如果它使用显式的Transaction在日志文件中即没有Commit操作，也没有Rollback操作，则会发生一次Rolled back操作，将Data File之中的相关数据修改回滚到Transaction发生之前。
4. 如果该操作已在在Transaction Log中，而不在Data File之中，并且如果它没有使用显式的Transaction操作符（Begin Transaction）, 则被认为是一个Rolled Forward操作。
5. 如上步骤都完成后，SQL Server会对该数据库做一个Checkpoint的标识，并写入Transaction Log，表示Data File和Transaction Log已经同步。这表明了数据库的Recovery完成。数据库将进入Online状态，并被正常使用。如果你的数据库在最近的一次Checkpoint到现今的修改操作足够多，在Error Log中也会看到SQL Server用百分比标识Recovery完成的进展。
问题：我的数据库一直处于 In Recovery状态，跑了好几个小时了。
解决方案：好吧，这个说明你的数据库自从上一次的Checkpoint到你现在的这个点对该数据库的修改足够多。这种情况所对应的症状是，你的事物日志文件一定很大。比如200GB？我们有客户跑了2个星期，所以…
其实这种状况完全可以避免。与其说这是SQL Server的一个Bug，这其实是对SQL Server的事物处理的理解有误，而并没有很恰当的使用SQL Server所致。请参考下文的“如何维护事务日志（Transaction Log）”。
问题：我已经理解了Recovery的工作模式，但是我真的很急，而且愿意承担Data File和Transaction Log之间的差异。我只是想让我的数据库快点上线。解决方案：第一件你需要考虑的是三思你是否确实要这样做。虽然也许Data File与Transaction Log之间的差异并非那么多，但是你可能会在你的Data File里留下没有被Commit的数据，从而在逻辑上影响你应用程序的使用。
如果你真的决定那么做，那么遵循以下步骤：
1． 设置单用户，设置紧急状态。
2． 获取Transaction Log的物理名和逻辑名
3． 重建Log
4． 将数据库设置成Online和Multi_User状态
如何维护事务日志（Transaction Log）
首先我们要理解SQL Server数据库的恢复模式。
SQL Server的数据库恢复模式分为3种http://msdn.microsoft.com/en-us/library/ms189275.aspx ：
• Full：完全记录事物日志。可以进行日志备份。
• Bulk-Logged：使用于批量操作的数据库。以更压缩的方式处理日志。可以进行日志备份。
• Simple：无法进行日志备份。意味着你并不在乎日志文件。但是，依然生成数据库日志文件。
虽然，日志文件记录了所有的数据库的更改操作，但是日志文件并非无限增长的，是因为SQL Server会按照一定的规则重用Log Space。
1. 首先，Log是顺序记录的。而来自不同Transaction的对于同一个数据库的Log可能是交错出现。
2. Transaction Log能够申请/释放的空间以Virtual Log File(VLF)为单位。
3. 在同时满足以下条件的前提下，Log Space可以被重用，即被覆盖
a. 最近一次Checkpoint之前。意味着Log File和Data File已经同步。
b. 最早一个Begin而没有Commit/Rollback的Transaction之前的Virtual Log File(VLF)。
c. 满足以上条件的Virtual Log File被标识为可重用。Full和Bulk-Logged恢复模式的数据库在备份日志时标识。Simple模式的数据库在做Checkpoint时标识可重用。
4. Transaction Log到文件尾以后，如果文件头的Virtual Log File(VLF)可以被重用，则会回溯到文件头。
对于日志文件的重用性，通常会有以下误区：
1． Simple模式下没有日志文件。
• 其实，任何模式下都有日志文件产生。只不过，Simple模式下通过Checkpoint自动地将可以重用的Virtual Log File(VLF)标识为可重用。如果有个很早就Begin Transaction，而忘记被Commit/Rollback，你依然可能看到一个很大的日志文件。
2． 完整备份（Full Backup）会打断日志备份链（Log Backup Chain）。
• 这个问题源自于Log Shipping：在一台做Log Shipping的Primary 机器上，是否可以做Full Backup？答案是可以的。假设你在做初始化的Full Backup1时的LSN是0-100, 其后周期性的Log Backup1, Log Backup2, Log Backup3的LSN分别是101-200， 201-300，301-400。而在做第二个Log Backup时，你在该机器上做了一次Full Backup2的LSN为：0-250。则当你从Full Backup2 开始做Restore过程是这样的：
i. 恢复 Full Backup2：0-250。
ii. 加载Log Backup2: 201-300.
iii. 恢复Log Backup2：251-300。
iv. 加载/恢复 Log Backup2: 301-400。
• 所以Log Backup2其实包含了完整的日志，但是在上述恢复的例子中，将从251开始恢复。
• Log Backup Chain会在以下情况下被打破。
i. 第一次做Full Backup。
ii. 恢复模式在Full/Bulk Logged和Simple之间进行转换。
3． 如果文件很大（Data File或者Transaction Log File）,我通过Shrink来收缩空间。
• 通常，我们并不推荐使用Shrink的方式来进行收缩。因为IO对于SQL Server而言是个非常昂贵的操作。你通过Shrink的方式收缩的磁盘空间，会在下次再次被SQL Server重新申请。而且在很多时候，你使用Shrink能够回收的磁盘空间并不那么理想。所以我们的目标是提高文件的重用性。
所以，对于维护日志来说主要需要注意的是以下几个方面：
1． 对于Full和Bulk-Logged，定期做日志备份。（在此之前最起码有1个完整备份。）两次备份的间隔是你能够容忍数据丢失的时间跨度。
2． Transaction尽可能短而快。避免长时间开启一个Transaction，或者Begin Transaction而忘记Commit/Rollback Transaction的事情发生。
问题：我的日志文件很大，而且收缩不下来。
解决方案：
首先，我们并不赞成通过自动/定期收缩的方式来控制文件的大小。我们建议通过建立良好的维护计划来提高日志文件空间的重用性而达到同样的目的。当然，如果你想把日志文件收缩（Shrink）到一个合理的程度，你可以参考如下步骤。
第一步，你需要查找的是，是否有个开了很长时间的Transaction，但是没有Commit/Rollback？可以通过以下语句查找该数据库下，跑了最长而没有被提交的Transaction。
如果有的话，你要么通知那个应用程序的管理人员来Commit/Rollback 这个Transaction。要么在SQL Server这边粗暴的杀死这个Session。下例中，1234为执行这个Transaction的Session ID（SPID）。
第二步，通过DBCC SQLperf(Logspace)检查Log文件被使用的情况。
在这里值得关注的是Status列。0 – 可以被Log所使用的VLF。 2- 已被使用，而且无法重用的VLF。通过备份Log的方式，将Status为2的行变成0。此时该空间已可被重用。如果，你真的想要进行日志收缩，可以在这步之后进行。则OS可以回收从最后一个0到最近一个2行的空间。（当前的Status=2的行标识当前的Log记录位置）
总结
其实如果有人问我20GB的Transaction Log大不大。其实这个问题只是个相对概念。理论上只要你有足够的磁盘空间，日志文件可以足够大。如果你有个400GB的数据库，20GB的日志文件也在合理的范畴。如果你的数据库只有5GB，那么你的应用程序对于Transaction的使用和日志的维护一定是有问题了。
微软亚太区数据库技术支持组 官方博客
为什么数据库日志文件这么大
我们经常遇到客户问和数据库日志相关的问题。比如：
1. 我的数据库只有10GB，为什么我的日志文件有40GB，而且还在增长？
2. 我无法对数据库进行更改操作了。报错说我的日志空间已满。
3. 我数据库的恢复模式明明是Simple，为什么还有日志，还那么大？
4. 我的数据库一直处于恢复状态，其他应用程序都报连接出错。
归根结底一句话，我数据库的日志的增长远远超过我的预期，而导致我的业务受到影响。数据库事务日志（Transaction Log），或简称日志文件（Log）文件的异常增长有时候对数据库的应用的影响是致命的。
想象一下，在你的业务最繁忙的时候，你的数据库由于日志文件占满整个磁盘空间，而无法使用。有人说，很简单啊！我把日志文件删掉。如果你真的这样做，你会发现数据库日志文件正被SQL Server进程锁定，而无法删除。于是，你停止SQL Server服务，然后很开心的将Transaction Log文件删除了，节省了大概200GB的空间。再于是，你很“开心”的发现你的数据库无法启动，因为缺少日志文件。如果你在日志文件原有的路径创建一个同名的日志文件，好吧，既然你那么坚决，SQL Server会告诉你日志文件格式已损坏。突然，你发现自己有个很好的习惯，当你删除那个200GB的日志文件的时候，你只是把它扔到了垃圾箱，于是你把它找了回来，重新启动你的SQL Server，很开心的发现，它成功的启动了。于是你松了一口气，泡上一杯咖啡，考虑是不是找你的Disk Vendor申请更多的磁盘空间。突然你的电话铃声大作，好多连接到你的数据库的应用程序的管理人员都向你抱怨他们无法连接到数据库。你打开SSMS，发觉那个数据库一直处于“恢复中”（In Recovery）状态，好像它永远不会终止。好吧，你是不是有想砸机器的冲动了，并且想气势汹汹的告诉全世界，这是微软SQL Server的另一个Bug？
如果，你认真的读完这篇文章，你会发现这只不过是SQL Server的另一个不恰当应用的一个案例。
什么是日志文件（Transaction Log）
简而言之，日志文件通过一个简化的格式记录了所有对数据库的修改操作，包括Insert， Update和Delete等能够帮助你重现对数据库内容修改的操作。日志文件的后缀名为*.LDF。
数据文件 （*.mdf）Vs. 日志文件 （*.ldf）
有人本以为数据文件是存储数据的“核心”文件，但是很诧异的发现，原来日志文件存储了所有的数据修改。有些人会想到，通过日志文件，其实我就可以把我的数据库恢复到某个特定时间点的状态。这句话，在某些限制条件下是对的，将在下文进行讨论。
那么，数据文件和日志文件的关系是什么呢？他们如何工作？
SQL Server是一个很依赖于内存（Memory）使用的系统。任何一个对于数据的读入/修改都是和内存进行交互。当一个修改操作发生时，修改的将是内存中所对应的数据页。然后，这个操作将会实时地被写入日志文件。但是，该修改被写入数据文件（*.mdf）的时间，只有在Checkpoint发生的时候。Checkpoint本身也会被写入日志文件。
Checkpoint是SQL Server的一个自动的行为。默认间隔为1分钟。http://msdn.microsoft.com/en-us/library/ms188748.aspx 所以，你的数据文件和日志文件的数据差异间隔会有1分钟。如果在某些情况下，Checkpoint并没有如预期那样每1分钟发生一次，数据差异间隔会更长。
日志文件如何影响我数据库的启动？
无论你的SQL Server启动，或者你将某数据库重备份中恢复，或者其他的一些情况，总之在你的数据库能够被正常使用之前，你的数据库都会进入 Recovery的状态。http://msdn.microsoft.com/en-us/library/ms190442.aspx 如果这一个步失败，那么你的数据库就会进入Suspect状态而无法正常使用。在一些特殊情况下，这个Recovery所花费的时间会很长。在数据库进入Online状态之前，我们都不能认为SQL Server可以被正常使用了。
那么在Recovery中做了什么呢？
我们可以在你的SSMS中运行如下语句。在运行之前，你可以先按下Ctrl+T来把结果转化成Plain Text格式。
sp_readerrorlog会返回自从最近一次SQL Server服务启动，或者Error Log被回收开始的SQL Server的Error Log信息。这里有两个词汇可能会让你感到疑惑。首先，这里的Log和我们上文所说的日志文件（Transaction Log）中的Log不是一件事情。其次，所谓的Error Log，并不是说这里出现了Error。SQL Server使用这个Error Log记录了很多诊断信息。所以，你可以认为这只是一个普通的记录了很多SQL Server相关信息的日志文件。
在结果返回中，你可能会找到上面的这些信息。引号中为在你当前数据库实例下的各个数据库名。
以数据库‘CaseLync’为例。由于Error Log会记录当前SQL Server实例下的所有数据库的相关信息，所以你可能会看到和这个数据库相关的日志被顺序地分布在日志的不同地方。在上图中，第一和第二行提及两个词：rolled forward和rolled back。这两个词和我们本文中所提及的Transaction Log息息相关。
在前文中，我们提到过数据修改的过程：先同步的被写入Transaction Log，然后再Checkpoint发生的时候被同步到Data File里。那么，如果我的事物（Transaction）没有被提交（Commit）或者回滚（Rollback），那么这个数据修改是否也在日志文件（Transaction Log）还是在数据文件里（Data File）？
答案是：都在！
SQL Server在记录数据改变时，并不会区分该语句是否有显示的进行事物操作（Begin Transaction，Commit / Rollback Transaction）, 或者该事务是否有完成。SQL Server会忠实地记录所有的修改操作。而Begin Transaction和Commit / Rollback Transaction本身也是日志文件需要记录的操作之一。
由于对于事物日志的修改要先于对于数据文件的修改，所以当你的数据库处于Recovery的状态时，那么Transaction Log就会从最近的一个Checkpoint点开始做如下操作：
1. 如果该操作已在在Transaction Log中，而不在Data File之中，并且如果它使用显式的Transaction操作符而且处于Commit状态，则会发生一次Rolled Forward操作，将该操作同步到Data File之中。
2. 如果该操作已在在Transaction Log中，而不在Data File之中，并且如果它使用显式的Transaction操作符而且处于Rollback Transaction状态，则会发生一次Rolled back操作，将Data File之中的相关数据修改回滚到Transaction发生之前。
3. 如果该操作已在在Transaction Log中，而不在Data File之中，并且如果它使用显式的Transaction在日志文件中即没有Commit操作，也没有Rollback操作，则会发生一次Rolled back操作，将Data File之中的相关数据修改回滚到Transaction发生之前。
4. 如果该操作已在在Transaction Log中，而不在Data File之中，并且如果它没有使用显式的Transaction操作符（Begin Transaction）, 则被认为是一个Rolled Forward操作。
5. 如上步骤都完成后，SQL Server会对该数据库做一个Checkpoint的标识，并写入Transaction Log，表示Data File和Transaction Log已经同步。这表明了数据库的Recovery完成。数据库将进入Online状态，并被正常使用。如果你的数据库在最近的一次Checkpoint到现今的修改操作足够多，在Error Log中也会看到SQL Server用百分比标识Recovery完成的进展。
问题：我的数据库一直处于 In Recovery状态，跑了好几个小时了。
解决方案：好吧，这个说明你的数据库自从上一次的Checkpoint到你现在的这个点对该数据库的修改足够多。这种情况所对应的症状是，你的事物日志文件一定很大。比如200GB？我们有客户跑了2个星期，所以…
其实这种状况完全可以避免。与其说这是SQL Server的一个Bug，这其实是对SQL Server的事物处理的理解有误，而并没有很恰当的使用SQL Server所致。请参考下文的“如何维护事务日志（Transaction Log）”。
问题：我已经理解了Recovery的工作模式，但是我真的很急，而且愿意承担Data File和Transaction Log之间的差异。我只是想让我的数据库快点上线。解决方案：第一件你需要考虑的是三思你是否确实要这样做。虽然也许Data File与Transaction Log之间的差异并非那么多，但是你可能会在你的Data File里留下没有被Commit的数据，从而在逻辑上影响你应用程序的使用。
如果你真的决定那么做，那么遵循以下步骤：
1． 设置单用户，设置紧急状态。
2． 获取Transaction Log的物理名和逻辑名
3． 重建Log
4． 将数据库设置成Online和Multi_User状态
如何维护事务日志（Transaction Log）
首先我们要理解SQL Server数据库的恢复模式。
SQL Server的数据库恢复模式分为3种http://msdn.microsoft.com/en-us/library/ms189275.aspx ：
• Full：完全记录事物日志。可以进行日志备份。
• Bulk-Logged：使用于批量操作的数据库。以更压缩的方式处理日志。可以进行日志备份。
• Simple：无法进行日志备份。意味着你并不在乎日志文件。但是，依然生成数据库日志文件。
虽然，日志文件记录了所有的数据库的更改操作，但是日志文件并非无限增长的，是因为SQL Server会按照一定的规则重用Log Space。
1. 首先，Log是顺序记录的。而来自不同Transaction的对于同一个数据库的Log可能是交错出现。
2. Transaction Log能够申请/释放的空间以Virtual Log File(VLF)为单位。
3. 在同时满足以下条件的前提下，Log Space可以被重用，即被覆盖
a. 最近一次Checkpoint之前。意味着Log File和Data File已经同步。
b. 最早一个Begin而没有Commit/Rollback的Transaction之前的Virtual Log File(VLF)。
c. 满足以上条件的Virtual Log File被标识为可重用。Full和Bulk-Logged恢复模式的数据库在备份日志时标识。Simple模式的数据库在做Checkpoint时标识可重用。
4. Transaction Log到文件尾以后，如果文件头的Virtual Log File(VLF)可以被重用，则会回溯到文件头。
对于日志文件的重用性，通常会有以下误区：
1． Simple模式下没有日志文件。
• 其实，任何模式下都有日志文件产生。只不过，Simple模式下通过Checkpoint自动地将可以重用的Virtual Log File(VLF)标识为可重用。如果有个很早就Begin Transaction，而忘记被Commit/Rollback，你依然可能看到一个很大的日志文件。
2． 完整备份（Full Backup）会打断日志备份链（Log Backup Chain）。
• 这个问题源自于Log Shipping：在一台做Log Shipping的Primary 机器上，是否可以做Full Backup？答案是可以的。假设你在做初始化的Full Backup1时的LSN是0-100, 其后周期性的Log Backup1, Log Backup2, Log Backup3的LSN分别是101-200， 201-300，301-400。而在做第二个Log Backup时，你在该机器上做了一次Full Backup2的LSN为：0-250。则当你从Full Backup2 开始做Restore过程是这样的：
i. 恢复 Full Backup2：0-250。
ii. 加载Log Backup2: 201-300.
iii. 恢复Log Backup2：251-300。
iv. 加载/恢复 Log Backup2: 301-400。
• 所以Log Backup2其实包含了完整的日志，但是在上述恢复的例子中，将从251开始恢复。
• Log Backup Chain会在以下情况下被打破。
i. 第一次做Full Backup。
ii. 恢复模式在Full/Bulk Logged和Simple之间进行转换。
3． 如果文件很大（Data File或者Transaction Log File）,我通过Shrink来收缩空间。
• 通常，我们并不推荐使用Shrink的方式来进行收缩。因为IO对于SQL Server而言是个非常昂贵的操作。你通过Shrink的方式收缩的磁盘空间，会在下次再次被SQL Server重新申请。而且在很多时候，你使用Shrink能够回收的磁盘空间并不那么理想。所以我们的目标是提高文件的重用性。
所以，对于维护日志来说主要需要注意的是以下几个方面：
1． 对于Full和Bulk-Logged，定期做日志备份。（在此之前最起码有1个完整备份。）两次备份的间隔是你能够容忍数据丢失的时间跨度。
2． Transaction尽可能短而快。避免长时间开启一个Transaction，或者Begin Transaction而忘记Commit/Rollback Transaction的事情发生。
问题：我的日志文件很大，而且收缩不下来。
解决方案：
首先，我们并不赞成通过自动/定期收缩的方式来控制文件的大小。我们建议通过建立良好的维护计划来提高日志文件空间的重用性而达到同样的目的。当然，如果你想把日志文件收缩（Shrink）到一个合理的程度，你可以参考如下步骤。
第一步，你需要查找的是，是否有个开了很长时间的Transaction，但是没有Commit/Rollback？可以通过以下语句查找该数据库下，跑了最长而没有被提交的Transaction。
如果有的话，你要么通知那个应用程序的管理人员来Commit/Rollback 这个Transaction。要么在SQL Server这边粗暴的杀死这个Session。下例中，1234为执行这个Transaction的Session ID（SPID）。
第二步，通过DBCC SQLperf(Logspace)检查Log文件被使用的情况。
在这里值得关注的是Status列。0 – 可以被Log所使用的VLF。 2- 已被使用，而且无法重用的VLF。通过备份Log的方式，将Status为2的行变成0。此时该空间已可被重用。如果，你真的想要进行日志收缩，可以在这步之后进行。则OS可以回收从最后一个0到最近一个2行的空间。（当前的Status=2的行标识当前的Log记录位置）
总结
其实如果有人问我20GB的Transaction Log大不大。其实这个问题只是个相对概念。理论上只要你有足够的磁盘空间，日志文件可以足够大。如果你有个400GB的数据库，20GB的日志文件也在合理的范畴。如果你的数据库只有5GB，那么你的应用程序对于Transaction的使用和日志的维护一定是有问题了。
微软亚太区数据库技术支持组 官方博客
...全文
请发表友善的回复…
发表回复
gogodiy 2011-12-29
- 打赏
- 举报
这贴太好了，非常感谢。
nzperfect 2011-12-28
- 打赏
- 举报
[Quote=引用 32 楼 michael1013 的回复:]
解决方案的话，可以尝试设置Min Server Memory。
[/Quote]
这个怎么理解？
32GB内存，分给SQL Server 27GB，其中MEMORYCLERK_SQLOPTIMIZER占到24GB，Data Page Cache占到3GB，设置Min Server Memory是为了解决什么呢？
解决方案的话，可以尝试设置Min Server Memory。
[/Quote]
这个怎么理解？
32GB内存，分给SQL Server 27GB，其中MEMORYCLERK_SQLOPTIMIZER占到24GB，Data Page Cache占到3GB，设置Min Server Memory是为了解决什么呢？
michael1013 2011-12-28
- 打赏
- 举报
[Quote=引用 30 楼 perfectaction 的回复:]
引用 29 楼 michael1013 的回复:
MEMORYCLERK_SQLOPTIMIZER: This clerk is used for query compilation. It uses lots of memory when batches that are submitted for compilation are too big.
Thanks,
1.这部分内存有没……
[/Quote]
从这段解释来看，MEMORYCLERK_SQLOPTIMIZER太大的原因是因为曾经有个很大的Batch SQL被编译。你可以从这个方向入手，看是哪个SQL那么大。
解决方案的话，可以尝试设置Min Server Memory。
引用 29 楼 michael1013 的回复:
MEMORYCLERK_SQLOPTIMIZER: This clerk is used for query compilation. It uses lots of memory when batches that are submitted for compilation are too big.
Thanks,
1.这部分内存有没……
[/Quote]
从这段解释来看，MEMORYCLERK_SQLOPTIMIZER太大的原因是因为曾经有个很大的Batch SQL被编译。你可以从这个方向入手，看是哪个SQL那么大。
解决方案的话，可以尝试设置Min Server Memory。
michael1013 2011-12-28
- 打赏
- 举报
[Quote=引用 30 楼 perfectaction 的回复:]
引用 29 楼 michael1013 的回复:
MEMORYCLERK_SQLOPTIMIZER: This clerk is used for query compilation. It uses lots of memory when batches that are submitted for compilation are too big.
Thanks,
1.这部分内存有没……
[/Quote]
从这段解释来看，MEMORYCLERK_SQLOPTIMIZER太大的原因是因为曾经有个很大的Batch SQL被编译。你可以从这个方向入手，看是哪个SQL那么大。
解决方案的话，可以尝试设置Min Server Memory。
引用 29 楼 michael1013 的回复:
MEMORYCLERK_SQLOPTIMIZER: This clerk is used for query compilation. It uses lots of memory when batches that are submitted for compilation are too big.
Thanks,
1.这部分内存有没……
[/Quote]
从这段解释来看，MEMORYCLERK_SQLOPTIMIZER太大的原因是因为曾经有个很大的Batch SQL被编译。你可以从这个方向入手，看是哪个SQL那么大。
解决方案的话，可以尝试设置Min Server Memory。
nzperfect 2011-12-28
- 打赏
- 举报
[Quote=引用 29 楼 michael1013 的回复:]
MEMORYCLERK_SQLOPTIMIZER: This clerk is used for query compilation. It uses lots of memory when batches that are submitted for compilation are too big.
[/Quote]
Thanks,
1.这部分内存有没有办法释放掉？
2.用sys.dm_exec_query_memory_grants这个dmv，每隔10s收集当前执行的T-sql申请的内存，这个方法是否可行？这个dmv中的granted_memory_kb字段，是否就是SQLOPTIMIZER？
MEMORYCLERK_SQLOPTIMIZER: This clerk is used for query compilation. It uses lots of memory when batches that are submitted for compilation are too big.
[/Quote]
Thanks,
1.这部分内存有没有办法释放掉？
2.用sys.dm_exec_query_memory_grants这个dmv，每隔10s收集当前执行的T-sql申请的内存，这个方法是否可行？这个dmv中的granted_memory_kb字段，是否就是SQLOPTIMIZER？
michael1013 2011-12-28
- 打赏
- 举报
[Quote=引用 28 楼 perfectaction 的回复:]
引用 26 楼 michael1013 的回复:
你这个问题有点复杂了。三言两语说不清楚。如果你有微软的Ticket的话，就给我们上个Case来研究吧。
你可以参考http://social.msdn.microsoft.com/Forums/en-IE/sqldatabaseengine/thread/5cff7258-9995-434e-888b-791bc743edca
那个……
[/Quote]
MEMORYCLERK_SQLOPTIMIZER: This clerk is used for query compilation. It uses lots of memory when batches that are submitted for compilation are too big.
引用 26 楼 michael1013 的回复:
你这个问题有点复杂了。三言两语说不清楚。如果你有微软的Ticket的话，就给我们上个Case来研究吧。
你可以参考http://social.msdn.microsoft.com/Forums/en-IE/sqldatabaseengine/thread/5cff7258-9995-434e-888b-791bc743edca
那个……
[/Quote]
MEMORYCLERK_SQLOPTIMIZER: This clerk is used for query compilation. It uses lots of memory when batches that are submitted for compilation are too big.
nzperfect 2011-12-28
- 打赏
- 举报
[Quote=引用 38 楼 michael1013 的回复:]
你说的是对的，但不尽然。不过对于Stolen Memory并非都是依赖于Min/Max Server Memory的设置。现在手头信息太少，无法做出进一步的诊断。最好从Optimizer Memory的成因入手解决问题。我们现在所能够看到的只是问题发生后的结果。
我们在这里发布一些SQL Server相关的一些技术文章，希望能够从一些理论和实践层面让广大MS SQL Server爱好者了解SQL Server的工作原理，同时提高微软亚太区数据库官方博客的影响力。对于一些直接了当的问题，我们也会抽时间来回答。如果您的问题比较复杂，有可能的话，请通过上Case的方式，我们会给你一个全面的诊断。
[/Quote]
你说的不尽然的情况我只能是相信，毕竟还牵涉到不同的Windows版本或SQL Server版本，对内存管理这块也可能会有不同，这些对于我们使用者来讲，全是黑盒，又没有能力去翻底层代码。
对于这个问题，再观察一段时间，目前对T-sql内存申请这块做了收集记录。
暂时把物理内存加倍后，倒是能缓解Data Page Cache这块的压力。
亚太博客关注很久了，文章很好，很有收获，希望多写一些。
你说的是对的，但不尽然。不过对于Stolen Memory并非都是依赖于Min/Max Server Memory的设置。现在手头信息太少，无法做出进一步的诊断。最好从Optimizer Memory的成因入手解决问题。我们现在所能够看到的只是问题发生后的结果。
我们在这里发布一些SQL Server相关的一些技术文章，希望能够从一些理论和实践层面让广大MS SQL Server爱好者了解SQL Server的工作原理，同时提高微软亚太区数据库官方博客的影响力。对于一些直接了当的问题，我们也会抽时间来回答。如果您的问题比较复杂，有可能的话，请通过上Case的方式，我们会给你一个全面的诊断。
[/Quote]
你说的不尽然的情况我只能是相信，毕竟还牵涉到不同的Windows版本或SQL Server版本，对内存管理这块也可能会有不同，这些对于我们使用者来讲，全是黑盒，又没有能力去翻底层代码。
对于这个问题，再观察一段时间，目前对T-sql内存申请这块做了收集记录。
暂时把物理内存加倍后，倒是能缓解Data Page Cache这块的压力。
亚太博客关注很久了，文章很好，很有收获，希望多写一些。
michael1013 2011-12-28
- 打赏
- 举报
[Quote=引用 36 楼 perfectaction 的回复:]
引用 35 楼 michael1013 的回复:
设置Min/Max Server Memory设置的时候，并不包含Stolen Memory的设置。因为这部分的内存，有些是无法通过SQL Server控制的。所以我们在这里讲的，更多的是Data Page Cache的内存。
通常情况下，我们会设置Max Server Memory来限制SQL Server使用过多的内存。在你这个情况下，我们……
[/Quote]
你说的是对的，但不尽然。不过对于Stolen Memory并非都是依赖于Min/Max Server Memory的设置。现在手头信息太少，无法做出进一步的诊断。最好从Optimizer Memory的成因入手解决问题。我们现在所能够看到的只是问题发生后的结果。
我们在这里发布一些SQL Server相关的一些技术文章，希望能够从一些理论和实践层面让广大MS SQL Server爱好者了解SQL Server的工作原理，同时提高微软亚太区数据库官方博客的影响力。对于一些直接了当的问题，我们也会抽时间来回答。如果您的问题比较复杂，有可能的话，请通过上Case的方式，我们会给你一个全面的诊断。
引用 35 楼 michael1013 的回复:
设置Min/Max Server Memory设置的时候，并不包含Stolen Memory的设置。因为这部分的内存，有些是无法通过SQL Server控制的。所以我们在这里讲的，更多的是Data Page Cache的内存。
通常情况下，我们会设置Max Server Memory来限制SQL Server使用过多的内存。在你这个情况下，我们……
[/Quote]
你说的是对的，但不尽然。不过对于Stolen Memory并非都是依赖于Min/Max Server Memory的设置。现在手头信息太少，无法做出进一步的诊断。最好从Optimizer Memory的成因入手解决问题。我们现在所能够看到的只是问题发生后的结果。
我们在这里发布一些SQL Server相关的一些技术文章，希望能够从一些理论和实践层面让广大MS SQL Server爱好者了解SQL Server的工作原理，同时提高微软亚太区数据库官方博客的影响力。对于一些直接了当的问题，我们也会抽时间来回答。如果您的问题比较复杂，有可能的话，请通过上Case的方式，我们会给你一个全面的诊断。
黄_瓜 2011-12-28
- 打赏
- 举报
[Quote=引用 21 楼 michael1013 的回复:]
有斑竹帮忙改个标题吗？我并非在提问，而是在提供Transaction Log过大的相关解决方案
请改为“数据库日志文件（Transaction Log）过大的相关解决方案”
[/Quote]
兄台好文章啊， 我已经转到空间去了 哈哈
有斑竹帮忙改个标题吗？我并非在提问，而是在提供Transaction Log过大的相关解决方案
请改为“数据库日志文件（Transaction Log）过大的相关解决方案”
[/Quote]
兄台好文章啊， 我已经转到空间去了 哈哈
nzperfect 2011-12-28
- 打赏
- 举报
[Quote=引用 35 楼 michael1013 的回复:]
设置Min/Max Server Memory设置的时候，并不包含Stolen Memory的设置。因为这部分的内存，有些是无法通过SQL Server控制的。所以我们在这里讲的，更多的是Data Page Cache的内存。
通常情况下，我们会设置Max Server Memory来限制SQL Server使用过多的内存。在你这个情况下，我们设置Min Server Memory来让Data Page Cache来“抢”更多的内存。
[/Quote]
有点怀疑，根据我的了解，Buffer Pool是包含Data Page Cache和Stolen内存的，而Max /Min Server Memory是控制Boffer Pool的，这样的话即使设置了Min Server Memory，也无法改变MEMORYCLERK_SQLOPTIMIZER的大小。
设置Min/Max Server Memory设置的时候，并不包含Stolen Memory的设置。因为这部分的内存，有些是无法通过SQL Server控制的。所以我们在这里讲的，更多的是Data Page Cache的内存。
通常情况下，我们会设置Max Server Memory来限制SQL Server使用过多的内存。在你这个情况下，我们设置Min Server Memory来让Data Page Cache来“抢”更多的内存。
[/Quote]
有点怀疑，根据我的了解，Buffer Pool是包含Data Page Cache和Stolen内存的，而Max /Min Server Memory是控制Boffer Pool的，这样的话即使设置了Min Server Memory，也无法改变MEMORYCLERK_SQLOPTIMIZER的大小。
michael1013 2011-12-28
- 打赏
- 举报
[Quote=引用 33 楼 perfectaction 的回复:]
引用 32 楼 michael1013 的回复:
解决方案的话，可以尝试设置Min Server Memory。
这个怎么理解？
32GB内存，分给SQL Server 27GB，其中MEMORYCLERK_SQLOPTIMIZER占到24GB，Data Page Cache占到3GB，设置Min Server Memory是为了解决什么呢？
[/Quote]
设置Min/Max Server Memory设置的时候，并不包含Stolen Memory的设置。因为这部分的内存，有些是无法通过SQL Server控制的。所以我们在这里讲的，更多的是Data Page Cache的内存。
通常情况下，我们会设置Max Server Memory来限制SQL Server使用过多的内存。在你这个情况下，我们设置Min Server Memory来让Data Page Cache来“抢”更多的内存。
引用 32 楼 michael1013 的回复:
解决方案的话，可以尝试设置Min Server Memory。
这个怎么理解？
32GB内存，分给SQL Server 27GB，其中MEMORYCLERK_SQLOPTIMIZER占到24GB，Data Page Cache占到3GB，设置Min Server Memory是为了解决什么呢？
[/Quote]
设置Min/Max Server Memory设置的时候，并不包含Stolen Memory的设置。因为这部分的内存，有些是无法通过SQL Server控制的。所以我们在这里讲的，更多的是Data Page Cache的内存。
通常情况下，我们会设置Max Server Memory来限制SQL Server使用过多的内存。在你这个情况下，我们设置Min Server Memory来让Data Page Cache来“抢”更多的内存。
nzperfect 2011-12-27
- 打赏
- 举报
[Quote=引用 26 楼 michael1013 的回复:]
你这个问题有点复杂了。三言两语说不清楚。如果你有微软的Ticket的话，就给我们上个Case来研究吧。
你可以参考http://social.msdn.microsoft.com/Forums/en-IE/sqldatabaseengine/thread/5cff7258-9995-434e-888b-791bc743edca
[/Quote]
那个贴子就是我发的了，Stephanie Lv回复的SQL，不能查到过去是由于什么语句或是某种操作造成MEMORYCLERK_SQLOPTIMIZER如此之大。
你这个问题有点复杂了。三言两语说不清楚。如果你有微软的Ticket的话，就给我们上个Case来研究吧。
你可以参考http://social.msdn.microsoft.com/Forums/en-IE/sqldatabaseengine/thread/5cff7258-9995-434e-888b-791bc743edca
[/Quote]
那个贴子就是我发的了，Stephanie Lv回复的SQL，不能查到过去是由于什么语句或是某种操作造成MEMORYCLERK_SQLOPTIMIZER如此之大。
nzperfect 2011-12-27
- 打赏
- 举报
hi michael1013，由于MEMORYCLERK_SQLOPTIMIZER这个问题暂时可以通过restart service来解决，都是F5下的一堆Server，这是一个朋友公司遇到的情况，估计他们早晚会向你们开case的。
michael1013 2011-12-27
- 打赏
- 举报
[Quote=引用 22 楼 perfectaction 的回复:]
hi michael1013,最近遇到几个奇怪的问题，是否可以给些建议，其中一个是在64bit SQL 2008 R2 企业版+Windows 2008 R2 企业版上，32GB内存 16CPU，27GB for SQL Server,发现stolen中的MEMORYCLERK_SQLOPTIMIZER占用了将尽24GB内存，致使data page只有3GB，内存中Page Life Expect……
[/Quote]
你这个问题有点复杂了。三言两语说不清楚。如果你有微软的Ticket的话，就给我们上个Case来研究吧。
你可以参考http://social.msdn.microsoft.com/Forums/en-IE/sqldatabaseengine/thread/5cff7258-9995-434e-888b-791bc743edca
hi michael1013,最近遇到几个奇怪的问题，是否可以给些建议，其中一个是在64bit SQL 2008 R2 企业版+Windows 2008 R2 企业版上，32GB内存 16CPU，27GB for SQL Server,发现stolen中的MEMORYCLERK_SQLOPTIMIZER占用了将尽24GB内存，致使data page只有3GB，内存中Page Life Expect……
[/Quote]
你这个问题有点复杂了。三言两语说不清楚。如果你有微软的Ticket的话，就给我们上个Case来研究吧。
你可以参考http://social.msdn.microsoft.com/Forums/en-IE/sqldatabaseengine/thread/5cff7258-9995-434e-888b-791bc743edca
michael1013 2011-12-27
- 打赏
- 举报
[Quote=引用 22 楼 perfectaction 的回复:]
hi michael1013,最近遇到几个奇怪的问题，是否可以给些建议，其中一个是在64bit SQL 2008 R2 企业版+Windows 2008 R2 企业版上，32GB内存 16CPU，27GB for SQL Server,发现stolen中的MEMORYCLERK_SQLOPTIMIZER占用了将尽24GB内存，致使data page只有3GB，内存中Page Life Expect……
[/Quote]
你这个问题有点复杂了。三言两语说不清楚。如果你有微软的Ticket的话，就给我们上个Case吧。
你可以参考这篇文章：http://social.msdn.microsoft.com/Forums/en-IE/sqldatabaseengine/thread/5cff7258-9995-434e-888b-791bc743edca
hi michael1013,最近遇到几个奇怪的问题，是否可以给些建议，其中一个是在64bit SQL 2008 R2 企业版+Windows 2008 R2 企业版上，32GB内存 16CPU，27GB for SQL Server,发现stolen中的MEMORYCLERK_SQLOPTIMIZER占用了将尽24GB内存，致使data page只有3GB，内存中Page Life Expect……
[/Quote]
你这个问题有点复杂了。三言两语说不清楚。如果你有微软的Ticket的话，就给我们上个Case吧。
你可以参考这篇文章：http://social.msdn.microsoft.com/Forums/en-IE/sqldatabaseengine/thread/5cff7258-9995-434e-888b-791bc743edca
nzperfect 2011-12-27
- 打赏
- 举报
建议版主标红，但不建议推荐，一推荐水军就会来把这个贴子水掉。
最近还有一个Memtoleave碎片的问题，搞得头疼的很。
最近还有一个Memtoleave碎片的问题，搞得头疼的很。
-晴天 2011-12-27
- 打赏
- 举报
因为记的东西多.
nzperfect 2011-12-27
- 打赏
- 举报
hi michael1013,最近遇到几个奇怪的问题，是否可以给些建议，其中一个是在64bit SQL 2008 R2 企业版+Windows 2008 R2 企业版上，32GB内存 16CPU，27GB for SQL Server,发现stolen中的MEMORYCLERK_SQLOPTIMIZER占用了将尽24GB内存，致使data page只有3GB，内存中Page Life Expectancy只有400，在做一个bulk insert时需要request5GB内存，发现根本没法申请了。
像这种情况，是否能在不restart sql service情况下，清除/释放掉MEMORYCLERK_SQLOPTIMIZER这块内存？
如何来排查是什么致使这个SQLOPTIMIZER这样大？
像这种情况，是否能在不restart sql service情况下，清除/释放掉MEMORYCLERK_SQLOPTIMIZER这块内存？
如何来排查是什么致使这个SQLOPTIMIZER这样大？
michael1013 2011-12-27
- 打赏
- 举报
有斑竹帮忙改个标题吗？我并非在提问，而是在提供Transaction Log过大的相关解决方案
请改为“数据库日志文件（Transaction Log）过大的相关解决方案”
请改为“数据库日志文件（Transaction Log）过大的相关解决方案”
nzperfect 2011-12-27
- 打赏
- 举报
[Quote=引用 19 楼 michael1013 的回复:]
引用 18 楼 perfectaction 的回复:
hi michael1013,的确如你所说，begin tran insert ..不commit tran的话，通过dbcc page发现数据页也已经完全发生变化，我比较了一个page在插入一行数据，在commit tran前后，除了bUse1有变化，其它完全一致，当stop SQL Server service后，删除log file，……
[/Quote]
的确是，非常感谢，重新认识了这个问题。
引用 18 楼 perfectaction 的回复:
hi michael1013,的确如你所说，begin tran insert ..不commit tran的话，通过dbcc page发现数据页也已经完全发生变化，我比较了一个page在插入一行数据，在commit tran前后，除了bUse1有变化，其它完全一致，当stop SQL Server service后，删除log file，……
[/Quote]
的确是，非常感谢，重新认识了这个问题。
加载更多回复（17）
