SQL Server 資料檔案收縮和檢視收縮排度的步驟

--適用於RDS For SQL Server2012
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

SELECT [name] ,[log_reuse_wait_desc]
 
FROM master.sys.databases
 
WHERE [name]='資料庫名【第1步獲取】'

DBCC SHRINKFILE(logicalName【第1步獲取】)

常見的紀錄檔等待型別是
LOG_BACKUP，紀錄檔還沒有備份，所以不能截斷
解決方案：
ACTIVE_TRANSACTION，有活躍事務阻塞了紀錄檔截斷
解決方案：
執行 DBCC OPENTRAN ，獲取下長時間的活躍事務的SPID
然後執行 DBCC INPUTBUFFER(SPID) 檢視下這個請求SQL，考慮是否可以kill阻塞源，kill後再查下log_reuse_wait，嘗試shrink

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

declare @usedspace int ,@totalspace int
select @usedspace= xxx,@totalspace =yyy
while @totalspace> @usedspace
begin
set @totalspace= @totalspace-5 *1024
DBCC SHRINKFILE( 邏輯檔名,@totalspace )
end

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

--查詢當前資料庫備份進度
SELECT   DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]  
FROM sys.dm_exec_requests AS er  
WHERE er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE')  --DB_NAME(er.[database_id]) in ('ky2011') and
--檢視資料庫收縮排度
SELECT DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m] 
FROM sys.dm_exec_requests as er WHERE command in ('DbccFilesCompact','AUTOSHRINK')