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