sqlserver利用存储过程，收缩数据库文件，释放磁盘空间

查看当前数据库文件使用率信息：

USE 数据库名

GO

SELECT a.name as 逻辑文件名, size/128 [totalspace文件大小(兆)],

   FILEPROPERTY(a.name, 'SpaceUsed')/128 [usedspace已用空间(兆)],

   size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128 [未用空间(兆)],

   FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size [使用率(%)]

FROM sys.database_files a cross join (select recovery_model_desc, log_reuse_wait,log_reuse_wait_desc,is_auto_shrink_on  from sys.databases where name=DB_NAME())b

WHERE type=0;

　创建存储过程

CREATE PROCEDURE dbo.ShrinkDataFile

   @logicalFileName NVARCHAR(128)

AS

BEGIN

   SET NOCOUNT ON;

   DECLARE @initialSize INT;

   DECLARE @targetSize INT;

   DECLARE @shrinkSize INT;

   DECLARE @usedSpace INT;

   DECLARE @minSize INT;

   SELECT @initialSize = size / 128,

          @usedSpace = FILEPROPERTY(name, 'SpaceUsed') / 128

   FROM sys.master_files

   WHERE name = @logicalFileName;

   SET @minSize = @usedSpace + 1000; -- 可收缩最大空间+1000

   SET @shrinkSize = 1024;    -- 每次释放1G空间

   SET @targetSize = @initialSize - @shrinkSize;

   IF @targetSize < @minSize

       SET @targetSize = @minSize;

   DBCC SHRINKFILE (@logicalFileName, @targetSize);

   SELECT size / 128 AS CurrentSizeMB

   FROM sys.master_files

   WHERE name = @logicalFileName;

END

GO

调用存储过程

EXEC dbo.ShrinkDataFile @logicalFileName = 'test';

logicalFileName 为第一条查询语句得出的结果。