MS SQL Server

Test code for the backp

April 25, 2023

Hello this is the code test

we are now adding tsql code test here [code type="CSS"]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', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','DBCC')[/code] Code content Editable content enabled cursor movement


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', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','DBCC')


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', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','DBCC')

How to find SQL database Backup or Restore Percentage query

December 04, 2022 Backup , Restore

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

Understanding SQL Server Database Fragmentation Check

October 09, 2022 Index , Performance

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

Scripts to get SQL agent jobs information

July 05, 2022 Jobs

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

Windows server 2019 clustering

June 03, 2020 1 AlwaysON , Cluster

I have implemented windows server 2019 clustering by using the below methods,

Installed windows 2019 standard evaluation edition on 3 machines.
SRVRDC (will act as a domain controller), SRVR1, and SRVR2 (these two will act as nodes)
Disabled firewall and UAC on 3 machines.
Done IP configuration on all three machines as below.

Network IP configuration is done as shown below

Public IP address is configured as shown blelow

Private IP configuration is done as shown below

IP configuration for SRVRDC

192.168.1.110

255.255.255.0

192.168.1.110

IP configuration for SRVR1

192.168.1.111

255.255.255.0

192.168.1.110

- Private network

192.168.2.11

IP configuration for SRVR2

192.168.1.112

255.255.255.0

192.168.1.110

Private network

192.168.2.12

Need to add .net 3.5 and failover cluster feature on SRVR1 and SRVR2
On SRVRDC enable iSCSI initiator from the server manager file share. and create storage.
Once storage is created add them from the iSCSI drives. capacity is given as below.

UserDB R = 8 GB
UserDBLog S = 6 GB
Tempdb T = 2 GB
MSDTC M = 1 GB
Quorum Q = 1

Once storage created initiate them from node1 and node2 respectively

Script to take database backup via SQL job

May 05, 2020 Backup , Jobs

we have use below TSQL script to take all database backup jobs

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @DeleteDate DATETIME = Dateadd(hh, -22, Getdate());
-- specify database backup directory
SET @path =
'\\servername\foldername\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20), Getdate(), 112) + '_'
+ Replace(CONVERT(VARCHAR(20), Getdate(), 108), ':', '')
DECLARE db_cursor CURSOR read_only FOR
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME NOT IN ( 'master', 'model', 'msdb', 'tempdb'
) -- exclude these databases
OPEN db_cursor
FETCH next FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH copy_only, noformat, noinit
,
skip, norewind, nounload, compression, stats = 2
FETCH next FROM db_cursor INTO @name
END
EXEC master.sys.Xp_delete_file
0,
@path,
'BAK',
@DeleteDate,
0;
CLOSE db_cursor
DEALLOCATE db_cursor

How to get last restore date and exact database name

March 20, 2020 msdb , Restore

we can use below script to get last database name and date by executing below script

SELECT bus.database_name Org_DBName,
restored_to_dbname,
last_date_restored
FROM msdb..backupset bus
INNER JOIN (SELECT backup_set_id,
restored_to_dbname,
last_date_restored
FROM msdb..restorehistory
INNER JOIN (SELECT rh.destination_database_name
Restored_To_DBName,
Max(rh.restore_date)
Last_Date_Restored
FROM msdb..restorehistory rh
GROUP BY rh.destination_database_name) AS
InnerRest
ON destination_database_name =
restored_to_dbname
AND restore_date = last_date_restored) AS
RestData
ON bus.backup_set_id = RestData.backup_set_id

How to check database restore percentage

February 26, 2020 Backup , Database , Restore

we can use the below script to check database backup restore percentage details and many more.

use master;

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')

Script to take database users and respective roles backup

February 12, 2020

It is very important to take a database users and the respective privileges backup while you perform a database restore, as once the database restore complete we must replace the same access rights before so that application activities never disturb.

to achieve this requirement we can use below script to take the specific database users and the roles.

[code type="SQL"] Use <'database name here'> go SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) + ') CREATE USER ' + QuoteName(dp.name) + IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') + IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid WHERE dp.type in ('G','U','S') go SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';' FROM sys.database_role_members r1 inner join sys.database_principals r2 on r1.member_principal_id = r2.principal_id inner join sys.database_principals r3 on r1.role_principal_id = r3.principal_id GO [/code]

Test code given below:

Use <'database name here'>

SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' +

QuoteName(dp.name, char(39)) +

') CREATE USER ' + QuoteName(dp.name) +

IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') +

IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT

FROM sys.database_principals dp

LEFT JOIN sys.server_principals sp

ON sp.sid = dp.sid

WHERE dp.type in ('G','U','S')

SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';'

FROM sys.database_role_members r1

inner join sys.database_principals r2

on r1.member_principal_id = r2.principal_id

inner join sys.database_principals r3

on r1.role_principal_id = r3.principal_id

MS SQL Server

Labels

Technology

Latest Post

Test code for the backp

How to find SQL database Backup or Restore Percentage query

Understanding SQL Server Database Fragmentation Check

Scripts to get SQL agent jobs information

Windows server 2019 clustering

IP configuration for SRVRDC

IP configuration for SRVR1

IP configuration for SRVR2

Script to take database backup via SQL job

How to get last restore date and exact database name

How to check database restore percentage

Script to take database users and respective roles backup

Author Name

Contact Form