MSSQL清理日志\删除数据\收缩数据库

红太狼 2021-01-09 03:59 506阅读 0赞

首先解释一下数据库的版本是SQL Server 2012。清除的数据库800多G，磁盘空间就剩10多G，数据量最多的表有2亿。目的就是清楚去年的数据（2017年之前），遇到了一些问题，总结起来就是三方面问题：

1.如何清理日志文件。

2.删除千万级别的数据。

3.数据库的收缩操作。

一、清除日志文件

刚开始直接用delete语句删除给报了两次错误：“The transaction log for database 'DB' is full due to 'ACTIVE\_TRANSACTION'. ”。我看了一下原来是数据库磁盘空间不足了（还剩9M），数据库日志文件的空间是不够的，所以要先清一下日志文件，腾出来点空间。

1.分离数据库，在数据库上右键-【任务】-【分离】，将数据库删除连接。

![662616-20170410161652969-312551043.png][]

2.找到数据库DB的所对应的文件日志--DB\_log.ldf，之后把这个文件删掉。

![662616-20170410162859547-2013507177.png][]

3.附加数据库，在数据库上右击-【附加】。

![662616-20170410163418735-1041910033.png][]

![662616-20170410163730704-837667203.png][]

4。假如直接选择数据库文件附加上去，就会直接报上图的错误。

这个时候我们就要新建一个日志文件，执行下边命令即可。

这个地方用命令执行。

use master; exec sp_detach_db @dbname='DB'; exec sp_attach_single_file_db @dbname='DB',@physname='C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DB.mdf'

至此，数据库就能附加上去了，这样节省出来空间20多个G。

问题二：删除大量数据（千万级别）

以前删除数据都是用Delete直接删除，Delete操作会被记录到日志里，所以说会很耗硬盘空间和时间。如果删除中间发生中断,一切删除会回滚。

所以说，对于这种情况有两种做法，一种是把数据分成多个批次进行删除；第二种是将需要的数据给查出来存到新的临时表里，之后将原来的数据表删除，最后将临时表的名字改成原来的名字。

--方法一、通过top方法 WHILE 1 = 1
    BEGIN
      DELETE TOP (5000) FROM Orders WHERE orderdate < '20170101'; IF @@rowcount < 5000 BREAK; END
    GO

--方法二 --①先读取需要的数据到临时表
     Select * into  ordersnew from Orders Where orderdate>='2017-01-01'
    
    --②先清空，后删除。Truncate这个不会有日志记录。或者直接用图形界面删。
    Truncate table Orders delete from Orders --③对ordersnew 表进行重命名
    exec  sp_rename 'ordersnew' ，'Orders'

注意：第二种方法，数据表的主键，外键，索引这些东西会消失，所以说，清完数据后，要重新建立。

**三：数据库的收缩**

在对几个大表进行数据清除后，发现磁盘还是10个G左右，并没有像想象中的增大。原来是因为删除数据（delete或truncate）是不会收缩文件的物理空间的。删除数据后的空闲空间，将会留给以后数据增长时使用。如果想要收缩文件的物理空间大小，可以使用收缩数据库和收缩文件。对应是DBCC SHRINKDB和DBCC SHRINKFILE两个命令。

DBCC  SHRINKDATABASE('DB') --收缩数据库

或者用图形界面操作【任务】-【收缩】-【数据库】。不过因为数据量比较大，所以时间还是比较长的。

![662616-20170411174026235-603969671.png][]

![662616-20170411175643704-1295329446.png][]

总结：对于数据库还有很多不太清楚的地方，如收缩数据库语句，日志的清理语句。以后慢慢研究吧

[662616-20170410161652969-312551043.png]: /images/1610164633923.png
[662616-20170410162859547-2013507177.png]: /images/1610164653049.png
[662616-20170410163418735-1041910033.png]: /images/1610164671104.png
[662616-20170410163730704-837667203.png]: /images/1610164689873.png
[662616-20170411174026235-603969671.png]: /images/1610164710370.png
[662616-20170411175643704-1295329446.png]: /images/1610164730184.png