SqlServer 磁盘空间的清理

• SqlServer 磁盘空间的清理

ALTER DATABASE InglassConvertFileDBProd SET RECOVERY SIMPLE  --清空日志
DBCC SHRINKFILE (InglassConvertFileDBProd_Log, 1)  --收缩数据库到1MB

循环删除数据

DECLARE @cnt INT = 0;
WHILE @cnt < 231
BEGIN
   delete top(100000) FROM [InglassConvertFileDBProd].[cap].[Received]
   ALTER DATABASE [InglassConvertFileDBProd] SET RECOVERY SIMPLE
   DBCC SHRINKFILE (InglassConvertFileDBProd_Log, 1)
   PRINT 'Finish '+convert(varchar(10),@cnt);
   SET @cnt = @cnt + 1;
END;

在清理数据后，发现磁盘空间仍然没有降下来，可以执行如下语句进行清理。

truncate table [InglassConvertFileDBProd].[cap].[Received]
truncate table [InglassConvertFileDBProd].[cap].[Published]
DBCC SHRINKDATABASE([InglassConvertFileDBProd])