SQL Server 数据文件收缩和查看收缩进度的步骤_Sql Server

SQL Server 数据文件收缩和查看收缩进度的步骤

2023-03-30 16:13VicLW Sql Server

这篇文章主要介绍了SQL Server 数据文件收缩和查看收缩进度,SQL Server在删除数据后，会重新利用这部分空间，所以如果不是空间紧张的情况下，可以不回收，本文给大家介绍的非常详细，需要的朋友可以参考下

SQL Server在删除数据后，会重新利用这部分空间，所以如果不是空间紧张的情况下，可以不回收。

回收一般先回收日志文件，因为这个回收速度非常快，可以短时间内清理出一部分可用空间。

回收步骤：

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，需要对数据库逐个执行

									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、查看数据文件大小

				?

									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

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

				?

									--查询当前数据库备份进度

									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')