SQL?Server數(shù)據(jù)文件收縮和查看收縮進(jìn)度的方法是什么

這篇文章主要介紹“SQL Server數(shù)據(jù)文件收縮和查看收縮進(jìn)度的方法是什么”，在日常操作中，相信很多人在SQL Server數(shù)據(jù)文件收縮和查看收縮進(jìn)度的方法是什么問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”SQL Server數(shù)據(jù)文件收縮和查看收縮進(jìn)度的方法是什么”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

SQL Server在刪除數(shù)據(jù)后，會(huì)重新利用這部分空間，所以如果不是空間緊張的情況下，可以不回收。

回收一般先回收日志文件，因?yàn)檫@個(gè)回收速度非?？?，可以短時(shí)間內(nèi)清理出一部分可用空間。

回收步驟：

1、查看日志文件大小【一般回收比較大的】 

--適用于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，需要對(duì)數(shù)據(jù)庫(kù)逐個(gè)執(zhí)行
USE 數(shù)據(jù)庫(kù)名
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、查看日志文件空間是否可回收【只有l(wèi)og_reuse_wait_desc是NOTHING狀態(tài)才可回收】

SELECT [name] ,[log_reuse_wait_desc]
 
FROM master.sys.databases
 
WHERE [name]='數(shù)據(jù)庫(kù)名【第1步獲取】'

 3、回收日志文件空間

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

常見的日志等待類型是
LOG_BACKUP，日志還沒有備份，所以不能截?cái)?
解決方案：
ACTIVE_TRANSACTION，有活躍事務(wù)阻塞了日志截?cái)?
解決方案：
執(zhí)行 DBCC OPENTRAN ，獲取下長(zhǎng)時(shí)間的活躍事務(wù)的SPID
然后執(zhí)行 DBCC INPUTBUFFER(SPID) 查看下這個(gè)請(qǐng)求SQL，考慮是否可以kill阻塞源，kill后再查下log_reuse_wait，嘗試shrink

 4、查看數(shù)據(jù)文件大小

USE 數(shù)據(jù)庫(kù)名
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

 5、收縮數(shù)據(jù)文件【按照經(jīng)驗(yàn)，最好每5G循環(huán)收縮，如果影響業(yè)務(wù)，隨時(shí)中斷，不會(huì)回滾】

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步的結(jié)果集獲取

6、查看收縮進(jìn)度【預(yù)估值】 

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

--查詢當(dāng)前數(shù)據(jù)庫(kù)備份進(jìn)度
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
--查看數(shù)據(jù)庫(kù)收縮進(jìn)度
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')

到此，關(guān)于“SQL Server數(shù)據(jù)文件收縮和查看收縮進(jìn)度的方法是什么”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！