Cached Query Plan

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GO
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE objtype = 'Adhoc'; 

Change Data Capture for Large table in SQL Server

If you are planning to transfer the data from one db server to another db server regular basis, You should plan to design Change Data Capture (CDC) which allow you to move changed data from source db to Destination server. I have implemented and tested completely with below sql scripts. You can also free to use this one.

-- STEP 1

-- Enable Database for CDC

USE [AdventureDB]

GO

EXEC sys.sp_cdc_enable_db

GO

use [AdventureDB]

GO

ALTER TABLE dbo.[Employee]

ENABLE CHANGE_TRACKING  WITH (TRACK_COLUMNS_UPDATED = ON)  

GO 

 

 --STEP2

USE [ChangeDB]

GO

CREATE TABLE [dbo].[CHG_Employee](

[EMP_ID] [varchar](12) NOT NULL,

[SYS_CHANGE_OPERATION] [char](1) NOT NULL,

[SYS_CHANGE_VERSION] [bigint] NOT NULL,

[Created_Date] [datetime] NOT NULL,

[Upload_Status] [char](1) NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CHG_Employee] ADD  DEFAULT (getdate()) FOR [Created_Date]

GO

ALTER TABLE [dbo].[CHG_Employee] ADD  DEFAULT ('N') FOR [Upload_Status]

GO

-- STEP3

USE [ChangeDB]

GO 

CREATE PROCEDURE [dbo].[usp_CT_CHG_Employee]

AS

/*Description : Change Tracking on [AdventureWorksDB] database to Employee table  */

BEGIN

Declare @last_synchronization_version bigint;

Declare @ChangNum bigint ;

SELECT @ChangNum = max(SYS_CHANGE_VERSION) FROM [ChangeDB].dbo.[CHG_Employee]

-- print @ChangNum

IF Exists (select 1  FROM  [ChangeDB].[dbo].[CHG_Employee] )  

   Begin

    -- PRINT '1 ONE'

INSERT INTO [ChangeDB].dbo.CHG_Employee  (EMP_ID, SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION)

SELECT

CT.EMP_ID,

CT.SYS_CHANGE_OPERATION,

CT.SYS_CHANGE_VERSION

FROM

CHANGETABLE(CHANGES AdventureDB.dbo.Employee, @last_synchronization_version) AS CT 

WHERE CT.SYS_CHANGE_VERSION > @ChangNum

   End

ELSE

   Begin

    --  PRINT '0 ZERO'

INSERT INTO [ChangeDB].dbo.CHG_Employee (EMP_ID ,SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION)

SELECT

CT.EMP_ID,

CT.SYS_CHANGE_OPERATION,

CT.SYS_CHANGE_VERSION 

FROM

CHANGETABLE(CHANGES [AdventureDB].dbo.Employee, @last_synchronization_version) AS CT 

End

END

GO

-- exec [dbo].[usp_CT_CHG_Employee]

Find Database Role with Objects and its Permissions

Finding a Database role with permission for Objects like (Table/View) to ensure the users got appropriates access permission within the database.

I have written this query to find database role (Users) with Object permissions.

USE [UserDatabase]

go

SELECT  

sp.[state_desc] ,

sp.[permission_name],

'ON'as Col1, 

ss.[name] AS [Schema_name], 

so.[name] as [Table_View_name], 

--so.[Type],

'TO' as Col2,

 dr.[Name]

FROM    sys.objects as so

INNER JOIN sys.schemas as ss ON so.schema_id = ss.schema_id

INNER JOIN sys.database_permissions AS sp ON sp.major_id    = so.object_id

INNER JOIN sys.database_principals  AS dr ON dr.principal_id = sp.grantee_principal_id

where  so.[Type] ='V' 

Order by  dr.[Name]

Announcing SQL Server 2025 (preview): The AI-ready enterprise database from ground to cloud

https://www.microsoft.com/en-us/sql-server/blog/2025/05/19/announcing-sql-server-2025-preview-the-ai-ready-enterprise-database-from-ground-to-cloud/

How to apply patches on SQL Database Mirror Servers

The following list of steps can be performed against Database Mirror servers to apply patches

1. Take backup of all dbs in Principal Database Server
2. Pause Mirroring on Principal Database Server for all mirrored dbs
3. The Principal server databases becomes (Principal,Suspended)
3. Stop all SQL services on Mirror Database Server (Including WMI service)
4. Apply CU patch on mirrored servers
5. Unpause Mirrors (wait for them to catch up)
6. Failover the databases (the mirror server becomes Principal)
7. Pause Mirroring
8. Hand-over to Application users to test and wait for their confirmation to proceed.
9. Stop all SQL services on Mirror Server (Including WMI service)
10. Apply CU patch on Principal Database servers
11. Unpause mirrors
12. Failback databases.

Find Backup History from SQL Server Database Server

The below scripts which allow you to find SQL Server Database Last night backup history. If you want see more files in this list please change date.

SELECT 

 [Server_name],

 [Database_name],

 [Type] AS Backup_type,

 case

    when [type]= 'D' THEN 'Full Backup'

when [type]= 'I' THEN 'Incremental Backup'

when [type]= 'L' THEN 'Log Backup'

 end  as Backup_types ,

 CAST(backup_size /1048576 AS DECIMAL (10,2))  AS [Backup_Size (MB)],

 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))  

AS  [Compressed_Backup_Size (MB)],

 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))/1024  AS  [Compressed_Backup_Size (GB)],

 100- ((compressed_backup_size/backup_size)*100)   AS 'Compressed%',   

 [backup_start_date],

 [backup_finish_date]

FROM msdb.dbo.backupset

WHERE [Type]='D'

  and backup_finish_date> getdate()-1 -- '2025-04-01 00:00:00.000'

ORDER BY backup_set_id desc

Could not find the Database Engine startup handle. Error code: 0x851A0019

Installing SQL Server 2022 on Azure Virtual machine I have encountered an issue

Action required:
Use the following information to resolve the error, uninstall this feature, and then run the setup process again.

Feature failure reason:
An error occurred during the setup process of the feature.

Error details:
§ Error installing SQL Server Database Engine Services Instance Features

Could not find the Database Engine startup handle.
Error code: 0x851A0019

Solution:

Run cmd  prompt on this below command check Drive sectorinfo

C:\Fsutil fsinfo sectorinfo E:

Make sure to have 512 bytes to 4096 bytes allocated for the Drive which master.mdf file is installed

Shrining Database Transaction Log file is not working Transaction Log file size from 42 GB to 100MB

It is interesting to fix one of major database issue which causing a problem to reduce Transaction Log file size from 42 GB to 100MB.

Database file (MDF) 760MB

Database Log file (LDF) 42GB

 -- STEP 1

DBCC Shrinkfile ('Adventuredb_Log', 100)

GO

-- not working above statement as we expected

I took database backup followed by Transaction Log backup then again tried not working neither T-SQL nor SSMS.

What a day

 DBCC Shrinkfile ('Adventuredb_Log', 100)

GO

 -- STEP 2

  DBCC OPENTRAN()

Transaction information for database 'Adventuredb'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (73283:414:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- STEP 3

select log_reuse_wait_desc,* from sys.databases  where name = 'Adventuredb'

log_reuse_wait_desc 

REPLICATION

EXEC sp_removedbreplication  'Adventuredb' 

--Commands completed successfully.

select log_reuse_wait_desc from sys.databases where name = 'Adventuredb'

log_reuse_wait_desc

NOTHING

Finally i have managed shrink the log file to 100MB

Microsoft SQL Server 2025

 Announcing Microsoft SQL Server 2025: Enterprise AI-ready database from ground to cloud

Announcing Microsoft SQL Server 2025: Enterprise AI-ready database from ground to cloud - Microsoft SQL Server Blog

Scripting All Agent Jobs Using SQL Server Management Studio

You have to Press the F7 key so the Object Explorer Details window appears like below. Select jobs you want to scripts and Right click to Generate scripts.