Capture SQL Server Important Details

Please find the below script to capture SQL Server Important Details before upgrade, migration etc. PRINT ‘***************** Server Name ********************’SET NOCOUNT ONselect @@SERVERNAMEgo PRINT ‘***************** Version ********************’SET NOCOUNT ONselect @@versiongo PRINT ‘***************** License Information********************’SET NOCOUNT ONSELECT SERVERPROPERTY (‘LicenseType’)go PRINT ‘***************** Number of Licenses********************’SET NOCOUNT ONSELECT… Read More

Find Percent Completion of Process

This script is to find the percent of completion of backup, restore, DBCC commands etc. SELECT req.session_id, sqltext.TEXT,req.status,req.command, req.wait_type, req.wait_time,(req.estimated_completion_time/1000/60) as est_min, req.percent_completeFROM sys.dm_exec_requests reqCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltextjoin sys.dm_exec_sessions ses on ses.session_id = req.session_idwhere req.session_id > 50

CPU_Usage_History of Past 4 hours

DECLARE @ts_now bigint SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info SELECT top 240 record_id, EventTime, CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization FROM ( SELECT… Read More