2022

December 04, 2022 ,
Monitoring Estimated Completion Times for Backup, Restore, and DBCC Commands in SQL Server In the world of database administration, long-running operations like backups, restores, and DBCC commands can leave us staring at the screen, wondering how much longer they’ll take. To alleviate this uncertainty, I’ve got you covered with a script that monitors the progress and provides estimated completion times for these critical tasks. 

 [code type="SQL"] SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id, s.text, start_time, percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time, CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.command in ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')[/code] 
This code can be executed SQL server 2008 or later.


Tsql code: 

SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id,
s.text,
start_time,
percent_complete, 
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in  ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')

Read more »

October 09, 2022 ,
Understanding SQL Server Database Fragmentation Check Fragmentation is most important topic, Let see how to identify and address index fragmentation in SQL Server. Index fragmentation affects query performance, and maintaining optimal indexes is crucial for efficient database operations. 

What Is Index Fragmentation? Index fragmentation occurs when the logical order of index pages doesn’t match the physical order of data pages. As data is modified (through inserts, updates, or deletes), free space can lead to page order mismatches, resulting in fragmentation. High fragmentation negatively impacts query execution time and resource utilization. 

Identifying Fragmentation To identify fragmentation, we’ll use the sys.dm_db_index_physical_stats dynamic management view (DMV). Here’s an efficient T-SQL statement to retrieve fragmentation details:



[code type="SQL"] SELECT db_name(database_id) as dbname, dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID('database name here'), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id ORDER BY indexstats.page_count desc [/code]

You can execute above command to get the required details.

SELECT db_name(database_id) as dbname,
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID('database name here'), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
ORDER BY indexstats.page_count desc 


Read more »

July 05, 2022

In this blog post, we’ll get the information of SQL Server Agent job schedules. This T-SQL commands and explore how they retrieve information about job schedules, recurrence patterns, and execution times.

This code has two parts one will gives a Daily schedulers of SQL server agent jobs and the other one gives a Weekly schedulers. 

Part 1: Daily Schedules

The first part of the script focuses on daily schedules.

Part 2: Weekly Schedules

The second part of the script deals with weekly schedules.


SELECT sysjobs.NAME      job_name,
       sysjobs.enabled   job_enabled,
       sysschedules.NAME schedule_name,
       sysschedules.freq_recurrence_factor,
       CASE
         WHEN freq_type = 4 THEN 'Daily'
       END               frequency,
       'every '
       + Cast (freq_interval AS VARCHAR(3))
       + ' day(s)'       Days,
       CASE
         WHEN freq_subday_type = 2 THEN ' every '
                                        + Cast(freq_subday_interval AS VARCHAR(7
                                        ))
                                        + ' seconds' + ' starting at '
                                        + Stuff(Stuff(RIGHT(Replicate('0', 6) +
                                        Cast(
                                        active_start_time AS VARCHAR(6)), 6), 3,
                                        0, ':'
                                        ), 6, 0, ':')
         WHEN freq_subday_type = 4 THEN ' every '
                                        + Cast(freq_subday_interval AS VARCHAR(7
                                        ))
                                        + ' minutes' + ' starting at '
                                        + Stuff(Stuff(RIGHT(Replicate('0', 6) +
                                        Cast(
                                        active_start_time AS VARCHAR(6)), 6), 3,
                                        0, ':'
                                        ), 6, 0, ':')
         WHEN freq_subday_type = 8 THEN ' every '
                                        + Cast(freq_subday_interval AS VARCHAR(7
                                        ))
                                        + ' hours' + ' starting at '
                                        + Stuff(Stuff(RIGHT(Replicate('0', 6) +
                                        Cast(
                                        active_start_time AS VARCHAR(6)), 6), 3,
                                        0, ':'
                                        ), 6, 0, ':')
         ELSE ' starting at '
              + Stuff(Stuff(RIGHT(Replicate('0', 6) + Cast(active_start_time AS
              VARCHAR
              (6)), 6), 3, 0, ':'), 6, 0, ':')
       END               time
FROM   msdb.dbo.sysjobs
       INNER JOIN msdb.dbo.sysjobschedules
               ON sysjobs.job_id = sysjobschedules.job_id
       INNER JOIN msdb.dbo.sysschedules
               ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE  freq_type = 4 

--Below is the script for weekly schedulers of SQL agent jobs..

SELECT sysjobs.NAME
       job_name
       ,
       sysjobs.enabled
       job_enabled,
       sysschedules.NAME
       schedule_name,
       sysschedules.freq_recurrence_factor,
       CASE
         WHEN freq_type = 8 THEN 'Weekly'
       END
       frequency,
       Replace (CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END + CASE
                WHEN
                freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN
                freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN
                freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN
                freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN
                freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN
                freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END, ', ', '')
       Days,
       CASE
         WHEN freq_subday_type = 2 THEN ' every '
                                        + Cast(freq_subday_interval AS VARCHAR(7
                                        ))
                                        + ' seconds' + ' starting at '
                                        + Stuff(Stuff(RIGHT(Replicate('0', 6) +
                                        Cast(
                                        active_start_time AS VARCHAR(6)), 6), 3,
                                        0, ':'
                                        ), 6, 0, ':')
         WHEN freq_subday_type = 4 THEN ' every '
                                        + Cast(freq_subday_interval AS VARCHAR(7
                                        ))
                                        + ' minutes' + ' starting at '
                                        + Stuff(Stuff(RIGHT(Replicate('0', 6) +
                                        Cast(
                                        active_start_time AS VARCHAR(6)), 6), 3,
                                        0, ':'
                                        ), 6, 0, ':')
         WHEN freq_subday_type = 8 THEN ' every '
                                        + Cast(freq_subday_interval AS VARCHAR(7
                                        ))
                                        + ' hours' + ' starting at '
                                        + Stuff(Stuff(RIGHT(Replicate('0', 6) +
                                        Cast(
                                        active_start_time AS VARCHAR(6)), 6), 3,
                                        0, ':'
                                        ), 6, 0, ':')
         ELSE ' starting at '
              + Stuff(Stuff(RIGHT(Replicate('0', 6) + Cast(active_start_time AS
              VARCHAR
              (6)), 6), 3, 0, ':'), 6, 0, ':')
       END
       time
FROM   msdb.dbo.sysjobs
       INNER JOIN msdb.dbo.sysjobschedules
               ON sysjobs.job_id = sysjobschedules.job_id
       INNER JOIN msdb.dbo.sysschedules
               ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE  freq_type = 8
ORDER  BY job_enabled DESC 

Read more »
Subscribe to: Posts (Atom)

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.