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

　　首先解释一下数据库的版本是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.分离数据库，在数据库上右键-【任务】-【分离】，将数据库删除连接。

 

　　

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

　　

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

　　

　　

 

 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') --收缩数据库

 

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

 

 

 

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