SQL SERVER 查询（备份、还原等）执行进度的脚本

最新推荐文章于 2024-04-02 08:55:17 发布

WilliamZhou_Vyung

最新推荐文章于 2024-04-02 08:55:17 发布

阅读量690

点赞数

分类专栏： SQL SERVER 文章标签： sql sqlserver 数据库

本文链接：https://blog.csdn.net/zhoumjx/article/details/123002478

版权

SQL SERVER 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

此SQL查询用于监控数据库的备份和恢复活动，包括会话ID、数据库名、状态、阻塞会话ID、命令、SQL文本、完成百分比、开始时间、CPU时间、已分配查询内存、读取、逻辑读取、写入、预计完成时间、已过去的时间和剩余预计时间等关键信息。筛选条件为会话ID大于50且命令包含'BACKUPDATABASE'、'BACKUPLOG'、'RESTOREDATABASE'、'RESTORELOG'。

摘要由CSDN通过智能技术生成

USE master
GO
 
SELECT 
	req.session_id, 
	database_name = db_name(req.database_id),
	req.status,
	req.blocking_session_id, 
	req.command,
	[sql_text] = Substring(txt.TEXT, (req.statement_start_offset / 2) + 1, (
				(
					CASE req.statement_end_offset
						WHEN - 1 THEN Datalength(txt.TEXT)
						ELSE req.statement_end_offset
					END - req.statement_start_offset
					) / 2
				) + 1),
	req.percent_complete,
	req.start_time,
	cpu_time_sec = req.cpu_time / 1000,
	granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),
	req.reads,
	req.logical_reads,
	req.writes,
	eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),
	elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),
	remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),
	eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),
	wait_type,
	wait_time_sec = wait_time/1000, 
	wait_resource
FROM sys.dm_exec_requests as req WITH(NOLOCK)
	CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as txt 
WHERE req.session_id>50
	AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')