SQL Server磁盤空間清理

SQL Server在删除資料後，會重新利用這部分空間，是以如果不是空間緊張的情況下，可以不回收。

回收一般先回收日志檔案，因為這個回收速度非常快，可以短時間内清理出一部分可用空間。

回收步驟：

1、檢視日志檔案大小【一般回收比較大的】 

--适用于RDS For SQL Server2012\2016

SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [Logical Name],[Physical_Name] AS [Physical Name],((size * 8) / 1024) AS [Size(MB)]

FROM sys.master_files 

ORDER BY [Size(MB)] DESC

--适用于RDS For SQL Server2008R2，需要對資料庫逐個執行

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=1

 2、檢視日志檔案空間是否可回收【隻有log_reuse_wait_desc是NOTHING狀态才可回收】

SELECT [name] ,[log_reuse_wait_desc] 

FROM master.sys.databases

WHERE [name]='資料庫名【第1步擷取】'

 3、回收日志檔案空間

DBCC SHRINKFILE(logicalName【第1步擷取】

常見的日志等待類型是

>>LOG_BACKUP，日志還沒有備份，是以不能截斷

解決方案：

>>ACTIVE_TRANSACTION，有活躍事務阻塞了日志截斷

執行 DBCC OPENTRAN ，擷取下長時間的活躍事務的SPID

然後執行 DBCC INPUTBUFFER(SPID) 檢視下這個請求SQL，考慮是否可以kill阻塞源，kill後再查下log_reuse_wait，嘗試shrink

4、檢視資料檔案大小

    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

5、收縮資料檔案【按照經驗，最好每5G循環收縮，如果影響業務，随時中斷，不會復原】

declare @usedspace int ,@totalspace int

select @usedspace= xxx,@totalspace =yyy

while @totalspace> @usedspace

begin

set @totalspace= @totalspace-5 *1024

DBCC SHRINKFILE( 邏輯檔案名,@totalspace )

end

 注：邏輯檔案名，usedspace，totalspace從第4步的結果集擷取

6、檢視收縮進度【預估值】

SELECT DB_NAME(database_id) as dbname,

session_id, request_id, start_time

, percent_complete

, dateadd(mi ,estimated_completion_time/60000,getdate ()) as ETC

 FROM sys.dm_exec_requests where percent_complete<>0