从 SQL Server 回收驱动器空间

任何工作过程中，大事小事无时不在，驾驶空间也不例外。对许多人来说，拥有最美好回忆的驾车地点确实令人恼火和烦恼。我们的客户——中国领先的垂直市场软件供应商，为有限的公共和私人市场的客户提供服务，我们的战略合作伙伴，努力管理驾驶空间，导致速度缓慢，从而降低工作效率。 

拥有5年以上经验的高级.NET开发人员高文豪先生加入了西贡科技根据言鼎科技的客户服务模型为我们的客户建立的离岸开发团队，成功地彻底解决了这种情况，并按照步骤进行了具体说明下面，这些信息可能对您有用： 

 问题

我们最近收到来自监控系统的警报，通知我们数据库服务器运行速度极慢……第一个警报到达时，SQL Server 所依赖的磁盘已接近满，紧接着第二个警报出现。

 解决方案之旅

首先，我尝试RD到服务器，惊奇地发现SQL Server安装在D盘，总共只有10Gb左右。

确定服务器上的数据库大小

我使用下面的 SQL 脚本列出了所有服务器数据库的大小： 

与fs

AS (选择database_id ,

           类型,

           大小* 8.0 / 1024 大小，

           文件编号

    从系统。主文件）

选择 名称，

       (

           选择总和（大小）

           从fs

           其中 类型= 0

                 和fs 。数据库 ID =数据库。数据库编号

       )数据文件大小MB ,

       (

           选择总和（大小）

           从fs

           其中 类型= 1

                 和fs 。数据库 ID =数据库。数据库编号

       )日志文件大小MB

从系统。数据库数据库

ORDER BY DataFileSizeMB DESC ;

正如我们所见，msdb 已占用 5 Gb，而磁盘仅占用 10 Gb。

msdb数据库（SQL Server Agent Service）是用于SQL Server服务器各个组件的系统数据库。除了 SQL Server 代理设置和任务信息之外，复制、日志传送和维护计划数据都保存在 msdb 数据库中。

接下来，我们将确定数据库中每个表的大小。

确定 msdb 中的表大小

我们可以使用下面的脚本来获取 msdb 数据库中每个表的大小。

使用[msdb] ;

去

选择 顶部( 10 )

       --o.[object_id],

       obj = SCHEMA_NAME ( o . [schema_id] ) + '.' +哦。名字，

       哦。[类型] ,

       我。总行数，

       我。总大小

从系统。对象o

    加入

    (

        选择我。[object_id] ,

               total_size = CAST ( SUM ( a . total_pages ) * 8. / 1024 AS DECIMAL ( 18 , 2 )),

               total_rows = SUM ( CASE  

                                       当我。index_id IN ( 0 , 1 )

                                            和一个。[类型] = 1那么

                                           p . [行]

                                   结尾

                               )

        从系统。指数我

            加入系统。分区p

                在我。[object_id] = p 。[object_id]

                   和我。index_id = p 。索引号

            加入系统。allocation_units一

                上页。[partition_id] =一个。容器编号

        我在哪里 is_disabled = 0

              和我。is_hypothetical = 0

         按i分组。[object_id]

    )我

        开o 。[object_id] =我。[object_id]

在哪里？[类型] IN ( 'V' , 'U' , 'S' )

由我订购。total_size DESC ;

以上结果表明，大部分记录都是备份历史的一部分。

现在让我们看看两个表，它们是什么？他们现在是什么？

dbo.backupset为每个备份集包含一行。备份集包含来自单个成功备份操作的备份。

dbo.backupfile为数据库的每个数据或日志文件包含一行。

此外，我们还可以通过阅读几个知识点站点来了解更多或查找有关其他表的更多信息。  

减少备份和恢复历史表的大小

通过删除备份条目，我们可以最小化这些表的大小。幸运的是，微软为我们提供了一个存储过程来处理这些工作，我们不需要在每个数据库中手动删除数据。

假设我们要删除超过 3 个月的备份记录，那么脚本是：

使用数据库；  

去  

-- 删除所有超过3个月的历史日志订单

声明@oldest_date DATETIME = DATEADD ( MONTH , - 3 , GETDATE ())

EXEC sp_delete_backuphistory @oldest_date ;   

让我们看看执行脚本后的结果。

表的大小从 5Gb 显着减少到不到 400Mb。好的！

尽管如此，这还不是结束！

我们知道，在每次备份或还原操作之后，备份和还原历史表中都会添加更多的行，并且每天都会扩展。因此，通常需要sp_delete_backuphistory 。

这次将实施维护计划。现在让我们继续配置历史记录的清理任务。 

SQL Server 维护计划历史清理任务

我们要清理历史。可以通过Wizard维护计划和Designer维护计划来设计。要设计它，让我们使用向导。

在 SSMS 中遵循以下过程以启动向导：

转到Management > Maintenance Plans并右键单击，然后选择Maintenance Plan Wizard：

点击“下一步”

填写任务的名称和描述，然后单击“更改... ”以设置计划。

选择您合适的时间表，然后单击“确定”

仔细检查计划信息是否正确，然后单击“下一步”

之后，我们显然选择“ Clean Up History ”任务，点击“ Next ”：

在下一个窗口中，我们设置任务的配置。正如我们所看到的，我们可以选择需要清理哪种历史数据备份和还原历史记录、SQL Server 代理作业历史记录和维护计划历史记录。此外，我们可以设置要保留的数据量：

我们将从msdb中清除所有提到的超过三个月的历史数据记录类型。然后，我们点击“下一步”

最后，我们点击完成创建计划：

这样，我们的清理任务就创建成功了：

如果我们刷新 Maintenance Plans 和 SQL Server Agent Jobs，我们可以找到新创建的计划和对应的作业。

从此以后，我们再也不用担心产生不必要的信息而导致磁盘又满了。