Change Tracking Data 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]

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

Data Read Access to All the users in the specific database after refresh

 After you refresh database from Production to UAT environments make sure to run below scripts to drop production users first then grant data read access to UAT users.

use [Database_Name]
go

select  'DROP USER' , '['+ name+']'  

from dbo.sysusers

where name LIKE '%PRD-%' 

GO

use [Database_Name]
go
select 'EXEC sys.sp_addrolemember '   ,   ''' db_datareader '''   ,   '['+ name+']'
from master.dbo.syslogins

GO

Generate Foreign Key constraints for all table in Database

SELECT 'ALTER TABLE [dbo].[' +''+  object_name(fk.parent_object_id)+']' AS  ParentTableName, 

   'WITH NOCHECK ADD  CONSTRAINT'+' ['+  fk.[name] +']' ,    

   'FOREIGN KEY([' +''+  COL_NAME(fc.parent_object_id,fc.parent_column_id) +'])' AS  ParentColName ,

   'REFERENCES [dbo].[' +''+  object_name(fk.referenced_object_id) +']' AS  RefTableName,

   '([' +''+    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) +'])' AS  RerfColName ,

   ' NOT FOR REPLICATION'

FROM sys.foreign_keys fk

 INNER JOIN   sys.foreign_key_columns AS fc        

        ON fk.OBJECT_ID = fc.constraint_object_id

 INNER JOIN   sys.tables t 

      ON t.OBJECT_ID = fc.referenced_object_id

--WHERE fc.parent_object_id = object_id('Table_name')

Check DATABASEPROPERTYEX ()

If you are managing more than 'n' of SQL Server database estate in your organisation then you may required to check some times  a specified database current setting in SQL Server to understand.

This below function returns the current setting of the specified database option or property.

DATABASEPROPERTYEX ( database , property )

-- You should defined database name for each function to retrieve the property details

SELECT 
DATABASEPROPERTYEX('AdventureWorks2022', 'Collation') AS Collation, 
DATABASEPROPERTYEX( 'AdventureWorks2022' , 'IsAutoShrink') AS IsAutoShrink  ,
DATABASEPROPERTYEX('AdventureWorks2022', 'Recovery') AS Recovery_State

--Alternatively you can use db_name function for the current database to  retrieve the property details

USE [db_name]
GO
SELECT   
DATABASEPROPERTYEX( db_name() , 'Collation') AS Collation,
DATABASEPROPERTYEX( db_name() , 'IsAutoShrink') AS IsAutoShrink  ,
DATABASEPROPERTYEX( db_name() , 'Recovery') AS Recovery_State

-- Check with Sys.databases to see all the database related property values

SELECT * FROM sys.databases;

Microsoft SQL Server 2022

Happy new year  2024 to everyone. Thank you so much for visiting this blog to learn new things in your life. I wish you all the best.

I always happy to share the new Microsoft SQL Server related features here and I have installed SQL Server 2022 Developer Edition in my laptop to explore new features in Server level and T-SQL too.

Update Stats for Table

 SET NOCOUNT  ON 

 DECLARE  @SQLcommand NVARCHAR(512), 

          @Table      SYSNAME 

 DECLARE CurAllTables CURSOR  FOR 

  SELECT table_schema + '.' + table_name 

FROM information_schema.tables T 

       INNER JOIN sys.sysindexes SSI 

ON t.TABLE_NAME = object_name(ssi.id) 

WHERE SSI.rowcnt > 500

    AND SSI.NAME LIKE '%_EN'

 OPEN CurAllTables 

FETCH NEXT FROM CurAllTables 

INTO @Table 

WHILE (@@FETCH_STATUS = 0) 

  BEGIN 

    PRINT N'UPDATING STATISTICS FOR TABLE: ' + @Table 

    SET @SQLcommand = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN' 

    EXEC sp_executesql @SQLcommand 

    FETCH NEXT FROM CurAllTables 

    INTO @Table 

  END 

CLOSE CurAllTables 

DEALLOCATE CurAllTables 

SET NOCOUNT  OFF 

GO

Find Index Fragmentation in Table

 SELECT 

S.name as 'Schema_Name',

T.name as 'Table_Name',

I.name as 'Index_Name',

F.Avg_Fragmentation_in_Percent,

F.Page_Count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as F

INNER JOIN sys.tables  as T    ON T.object_id = F.object_id

INNER JOIN sys.schemas as S ON T.schema_id = S.schema_id

INNER JOIN sys.indexes as I   ON I.object_id = F.object_id

AND F.index_id = I.index_id

WHERE F.database_id = DB_ID()

AND I.name IS NOT NULL

AND F.avg_fragmentation_in_percent > 0

ORDER BY F.avg_fragmentation_in_percent DESC

How to add more partitions to existing table

 --STEP 1 : Find File group

SELECT name AS AvailableFilegroups
FROM sys.filegroups WHERE type = 'FG'

--AvailableFilegroups
-------------------
--PRIMARY


--- STEP 2 : Create Partition Range Function & Schema

SELECT * FROM sys.partition_functions WHERE [name] = N'PF_CREATED_DATE'  
SELECT * FROM sys.partition_schemes   WHERE [name] = N'PS_PURCHASE_ORDER' 
  

CREATE PARTITION FUNCTION [PF_CREATED_DATE](Date) 
AS RANGE RIGHT FOR VALUES (
N'2019-08-01',
N'2019-08-02',
N'2019-08-03',
N'2019-08-04',
N'2019-08-05',
N'2019-08-06',
N'2019-08-07',
N'2019-08-08',
N'2019-08-09',
N'2019-08-10',
N'2019-08-11',
N'2019-08-12',
N'2019-08-13',
N'2019-08-14',
N'2019-08-15')

CREATE PARTITION SCHEME [PS_PURCHASE_ORDER] AS PARTITION [PF_CREATED_DATE]  TO  ( 
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ) 


--- STEP 3 : Create TABLE & Index

 SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PURCHASE_ORDER](
[PURCHASE_ORDER_ID] [bigint] NOT NULL,
[CREATED_DATE] [date]  NOT NULL,
[LOAD_ID] [int] NOT NULL
) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


CREATE CLUSTERED INDEX [CI_CREATED_DATE] ON [dbo].[PURCHASE_ORDER]
(
[CREATED_DATE] ASC,
[PURCHASE_ORDER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


--- STEP 4 : Add new partitions

 -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-16')

  -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-17')

How do i change table schema to dbo

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName

Example : 1
ALTER SCHEMA dbo TRANSFER Archive.TableName


Example : 2
change schema from dbo to rpt

Source table : dbo.rpt.TableName ( Invalid table name with additional dot)

Step 1 : Rename table with _ under score.
                   dbo.rpt_TableName

Step 2 : Change schema to rpt
ALTER SCHEMA rpt TRANSFER dbo.rpt_TableName

    Now table name with fully qualified name    rpt.rpt_TableName

Step 3 : Rename table with correct name ( to remove rpt_ )
                      rpt.TableName

Cannot open user default database. Login failed. Login failed for user 'xx'

I have been very busy for last couple of months so i could not get chance to post earlier. I hope and continue to post more technical stuff on this blog this year 2014.

If you are sysadmin, Your default database set to one of the user database. If you decide to take offline for your default database before you set default to another database.

You will get the following error.
Cannot open user default database. Login failed. Login failed for user 'xx'

There are the steps below login to server to access master database and then you can set your default database.

Issue:
1. Connected as a sysadmin (windows authentication domain\username)
2. Connect to database server
3. Take offline  your database DB_WH_Report
-- I connected
4. Disconnect to Server
When you try to connect back
Error :
Cannot open user default database. Login failed. Login failed for user 'xx'

Solution:
1. Connect to Server
2. Click Option to expand  Connection Propertise
3. Connect to Database Type master
4. Click Connect button

Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

When you try to insert rows to identity column , you should include set identity_insert is ON 

SET IDENTITY_INSERT Table_Name ON 
GO 
INSERT INTO dbo.Table_Name ( col1, col2, col3) 
SELECT col1, Col2 col3 FROM dbo.Table2 
GO 
SET IDENTITY_INSERT Table_Name OFF 
GO 

Schedule to Rebuild Index for your user database

Here is the script you can use to Rebuild Index job to be scheduled to run specific time.


USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA Index Maintenance_MyDBTest',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=2,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'MyDBTest',
  @category_name=N'Database Maintenance',
  @owner_login_name=N'sa',
  @notify_email_operator_name=N'DBA_Mail', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ReIndex Maintenance_MyDBTest',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'USE MyDBTest
GO
-- Ensure a USE statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
-- 40 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 40.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
        IF @frag >= 40.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD WITH ( ONLINE = ON )'';
        IF @partitioncount > 1
            SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N''Executed: '' + @command;
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
exec sp_updatestats
go',
  @database_name=N'master',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'ReIndex Maintenance_MyDBTest',
  @enabled=1,
  @freq_type=8,
  @freq_interval=64,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=1,
  @active_start_date=20120116,
  @active_end_date=99991231,
  @active_start_time=201000,
  @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

 

How do you find SQL Server version info

There are five place you can find a SQL Server version information.

--1 select @@version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11:57:00 Copyright
(c) Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--2 exec sp
exec sys.sp_MSgetversion
10.50.1617.0 1 3

 -- 3 SQL Server Logs
Date 09/01/2013 07:12:16Log SQL Server (Current - 09/01/2013 17:18:00)Source
ServerMessage
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11
:57:00 Copyright
(c) Microsoft CorporationEnterprise Evaluation Edition
on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- 4 SERVERPROPERTY
SELECT SERVERPROPERTY('edition'),SERVERPROPERTY('ProductVersion'),SERVERPROPERTY('ProductLevel')Enterprise Evaluation Edition 10.50.1617.0 RTM

--5 exec sys.xp_msver

Still you can find sql server version information from Windows Registry but i do not want to expose more on it here.

SQL Server 2012 Express Editions

Microsoft also released a new set of SQL Server 2012 Express editions. SQL Server Express is a free edition of SQL Server, and it’s used as the internal database for hundreds of third-party products.
Although it's free, however, it also has several limitations. Compute-wise, the new SQL Server 2012 Express editions are limited to the lesser of one socket or four cores. The editions are also limited in size. The maximum memory for the database engine is limited to 1GB and the maximum relational database size is 10GB.
Although SQL Server Express is limited in scalability, a common misconception is that it’s limited to a single database or 10 connections. Neither of these is true. SQL Server Express can support multiple databases where each database can be up to 10GB. The myth about the number of connections is probably a holdover from the early days of MDSE (Microsoft Database Engine—the first version of the desktop SQL Server Engine that was released in the SQL Server 2000 timeframe.)
SQL Server 2012 Express has no limits on incoming connections or workload other than the CPU and memory limitations. And it comes in both 32-bit and 64-bit versions. You can download all of the SQL Server 2012 Express editions from Microsoft’s website.

Reference : http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx

[264] An attempt was made to send an email when no email session has been established

You need to enable mail profile located in the properties of the SQL Agent,
Alert System and choose Mail System and Mail profile from the drop down listbox.
Finally you must restart the SQL Server Agent and run your scheduled job to get email notification

Robocopy files to another Drive

Robocopy files to another Drive with estimated time to complete
If you ever plan to move the sql data(mdf) or log file (LDF) to another drive then try to use robocopy is much better than windows file copying and takes less time.

In that command prompt e.g:
F:\Robocopy  "E:\SQLData" "F:\SQLData" mydb.mdf mydb.mdf /ETA

E:\Robocopy  "D:\SQLLogs" "G:\SQLLogs" mydb_log.ldf mydb_log /ETA

How to import sql server profiler trace file into table

If you want to analyse the sql profiler output then import into table using a fn_trace_gettable.

USE Mydbname
GO
SELECT * INTO trace_table
FROM ::fn_trace_gettable('C:\Temp\myFile.trc', default) 

Find Drive size and free space

-- This query will run SQL Server 2008 R2 only
SELECT distinct vs.volume_mount_point AS DriveName,
vs.total_bytes/1073741824 as TotalSize_GB,
vs.available_bytes/1073741824 AS FreeSpace_GB,
(vs.total_bytes/1073741824) -
(vs.available_bytes/1073741824) AS SpaceUsed_GB,
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,3)) * 100 AS [SpaceUsed %]
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY vs.volume_mount_point