T-SQL Script to check the database backup and restore progress and estimated completion time

 Script 1: 

select 

d.name as [Database Name],

Percent_complete, 

dateadd(second,estimated_completion_time/1000, 

getdate()) as EstimatedCompletionTime, Getdate() as Current_Time_now, 

CAST((datediff(minute, start_time, getdate())) as varchar(50)) +'  ' + 'Minutes' as CMD_Running_From, 

CAST((estimated_completion_time/1000/60) as varchar(50)) +'  ' + 'Minutes'  as Will_Complete_in,

start_time as CMD_Start_Time, 

command As Running_Command

from sys.dm_exec_requests req

inner join sys.sysdatabases d on d.dbid = req.database_id

where 

req.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')


Script 2:


select 

d.name,

percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as EstimatedCompletionTime, Getdate() as now, 

datediff(minute, start_time, getdate()) as running, estimated_completion_time/1000/60 as togo,

start_time, command 

from sys.dm_exec_requests req

inner join sys.sysdatabases d on d.dbid = req.database_id

where 

req.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Comments

Post a Comment

Popular posts from this blog

SQL Server DBA scripts for day-to-day tasks

   1. Check Database Size: SELECT name AS 'Database Name', size * 8 / 1024 AS 'Size (MB)' FROM sys.master_files WHERE DB_NAME(database_id) NOT IN ('master', 'tempdb', 'model', 'msdb'); 2. Check Fragmentation: SELECT OBJECT_NAME(ind.OBJECT_ID) AS 'Table', ind.name AS 'Index', indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC; 3. Backup Information: SELECT database_name, backup_start_date, backup_finish_date, compressed_backup_size/1024/1024 AS 'Backup Size (MB)' FROM msdb.dbo.backupset ORDER BY backup_start_date DESC; 4. Check Active Sessions: SELECT session_id, login_time, host_name, program_name FROM sys.dm_exec_session...
Read more

SQL Server Dynamic management views (DMV's)

  dm_audit_actions dm_audit_class_type_map dm_broker_activated_tasks dm_broker_connections dm_broker_forwarded_messages dm_broker_queue_monitors dm_cdc_errors dm_cdc_log_scan_sessions dm_clr_appdomains dm_clr_loaded_assemblies dm_clr_properties dm_clr_tasks dm_cryptographic_provider_algorithms dm_cryptographic_provider_keys dm_cryptographic_provider_properties dm_cryptographic_provider_sessions dm_database_encryption_keys dm_db_file_space_usage dm_db_index_operational_stats dm_db_index_physical_stats dm_db_index_usage_stats dm_db_mirroring_auto_page_repair dm_db_mirroring_connections dm_db_mirroring_past_actions dm_db_missing_index_columns dm_db_missing_index_details dm_db_missing_index_group_stats dm_db_missing_index_groups dm_db_partition_stats dm_db_persisted_sku_features dm_db_script_level dm_db_session_space_usage dm_db_task_space_usage dm_exec_background_job_queue dm_exec_background_job_queue_stats dm_exec_cached_plan_dependent_objects dm_exec_cached_plans dm_exec_connections...
Read more

A brief comparison of Microsoft SQL Server versions from SQL Server 2012 to SQL Server 2022

 SQL Server 2012: • Introduced AlwaysOn Availability Groups for high availability. • Enhanced columnstore indexes for better data warehousing performance. • Contained Databases for easier database portability. • Improved security features, including user-defined server roles. SQL Server 2014: • In-Memory OLTP (Hekaton) introduced for improved transaction processing. • Buffer Pool Extensions for SSD integration. • Enhanced backup to Azure for improved disaster recovery. • JSON support introduced. SQL Server 2016: • PolyBase introduced for querying across relational and non-relational databases. • Stretch Database feature for hybrid cloud scenarios. • Always Encrypted for enhanced security. • Dynamic Data Masking for controlling access to sensitive data. SQL Server 2017: • Introduced support for SQL Server on Linux. • Adaptive Query Processing for improved query performance. • Graph Database capabilities. • Automatic dat...
Read more