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.分离数据库,在数据库上右键-【任务】-【分离】,将数据库删除连接。

  

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

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

  

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

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

  

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

  

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

 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操作会被记录到日志里,所以说会很耗硬盘空间和时间。如果删除中间发生中断,一切删除会回滚。

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

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

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

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

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

--方法二
--①先读取需要的数据到临时表
 Select * into  ordersnew from Orders Where orderdate>='2017-01-01'

--②先清空,后删除。Truncate这个不会有日志记录。或者直接用图形界面删。
Truncate table Orders 
delete from Orders

--③对ordersnew 表进行重命名
exec  sp_rename 'ordersnew' ,'Orders'

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

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

三:数据库的收缩

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

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

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

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

 

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

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

MSSQL清理日志\删除数据\收缩数据库 - 神雕爱大侠 - 博客园

上一篇:centos8安装docker


下一篇:vmware 安装 centos8 步骤