Change Data Capture (CDC)–An easy way to track data changes of a database–TIP #79

 

In last TIP #78  we have discussed Change tracker (CT) which was introduced in SQL SERVER 2008. CT feature only tracks which row is changes means on which row Insert/update/delete operation is performed but it does not track what exact value is changed.

If we want an audit of database means whatever changes occurred in database we want to capture those changes and later on see what are changes made then before CDC feature we have to right triggers but now in this case we can use Change Data capture (CDC) feature.

By the name it is clear that it capture the data which is changed.

Lets understand here step by step how to enable this feature

Step 1:- The first important step is to enable CDC on selected database. To enable CDC we have to write following command

EXEC sys.sp_cdc_enable_db

Note:- sometime you may get error while doing this so just cross check your database owner.

We can cross check whether CDC enable or not on database by following command

SELECT [name], database_id, is_cdc_enabled  FROM sys.databases  WHERE is_cdc_enabled = 1

Step 2:- Once we have enabled CDC setting on Database then we need to enable same setting for table which we need to capture.

For this we have to write following command

EXEC sys.sp_cdc_enable_table
    @source_schema ='dbo',
    @source_name ='tblStudentCDC',
    @role_name ='StudentCDCRole',
    @supports_net_changes = 1

Now When we run above command, we will find following items added in our database. We will find new tables created automatically in our database under CDC schema as shown in below figure

Whenever we enable CDC for a table a new table is created in System tables (if already not created) under a new schema which is “CDC” which denote change data capture.

a) CDC.captured_Columns:- This table contains all the captured columns of a CDC enabled tables.

As shown in below figure:

SELECT * FROM [cdc].[Captured_Columns]

b) CDC.Change_tables:- By the name it is clear that this will table will contain list of all the tables on which we have enabled CDC feature.

SELECT * FROM [cdc].[change_tables]

c)CDC.Index_Columns:-  This table keeps information of all the Indexes of a table

SELECT * FROM [CDC].[Index_Columns]

d) [cdc].[ddl_history]:-  This table contains all the information of schema changes of a CDC enabled table.

SELECT * FROM [CDC].[ddl_History]

e)[CDC]. [lsn_time_mapping]:-  This table keeps all the LSN ( Log Sequence Number)related information. The base of this table is whatever the transaction done on the CDC enabled table that will be capture in this table.

SELECT * FROM [CDC].[lsn_time_mapping]

On the same time few jobs also created which you can find in SQL SERVER agent as shown in below figure

Step 3:-We can also cross check all CDC enabled tables by using following command

SELECT [name], is_tracked_by_cdc  FROM sys.tables  WHERE is_tracked_by_cdc = 1

Step 4:-  Every captured table has suffix _CT and  the name is same as enabled CDC table. In current scenario we have enabled CDC feature on tblStudentCDC so the capture table is tblStudentCDC_CT.

Now we can find all the data change reference of a CDC enabled table by following command.

SELECT * FROM [cdc].[dbo_tblStudentCDC_CT]

Sometimes it is also called mirror table which keeps all the track.We will find following extra columns

_$Start_lsn,_$end_lst_$sequal,_$Opeartion,_$updatemask

_$Operation column track all the operations on the table and the different values denotes  different operations status as mention below
Like if value is 1 then it is a Delete Statement (means record deleted from main table)
if value is 2 then it is Insert Statement (means record inserted in main table)
if value is 3 then it denotes Value before Update Statement
if value is 4 then it denotes Value after Update Statement

So, with this easy steps we can enable CDC on database and tables and can enjoy this feature.

We can easily disable this feature which we will discuss in the next post.

Till than enjoy CDC.

Enjoy !!!

RJ !!!

Change Tracking–an easy way to track your changes TIP#78

 

I am pretty much sure by the title of this post you had idea of the post content. Although I am late to post this feature but anyways Change Tracking (CT) is a feature came in SQL SERVER 2008.

As the name mention it track the changes like DELETE, INSERT , UPDATE type DML operations which we can synch to another database. (Microsoft Synch Framework).

In simple word what ever we change in table will be track by Change Tracking feature.

The main constraint which I would like say here is there should be a primary key in table for which we have enabled this CT (Change tracking) feature.

Now your next question is how to use this feature so not to worry below is step by step way to use this feature.

Step 1:- First step is enable Change Tracking property of database. you can enable this property by right clicking database property as shown in below figure.

Or you can write following query to enable CT (Change tracking) feature on database

ALTER DATABASE IndiandotnetDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Now let me explain here what is CHANGE_RETENTION & AUTO_CLEANUP property here.

So change retention is feature which tell how many days we have to keep the change history. In above statement we have chosen for 3 days.

Now next feature is AUTO clean_up with this help of this feature we can clean garbage data from temporary storage where SQL SERVER keeping the data.

Step 2:- Now we have to enable same feature on SQL table which is tblStudentSource on which we have to track database.

If we see there are two property which is basically mapping  property for which we have enabled this property.

We can run following command as well

ALTER TABLE tblStudentSource
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

For tables which we want track we have to enable this feature. Currently I chosen only single table which is tblStudentSource.

Step 3:- We can cross check Whether feature is enabled or not on database and table by running following command

SELECT DB_Name(database_id), * FROM sys.change_tracking_databases
SELECT object_Name(object_Id), * FROM sys.change_tracking_tables

Step 4:- if we see result of step3 command we will find our CT (Change Tracking) feature is enabled on database & particular table.

Now if we make any changes in table tblStudentSource (or table on which we have enabled this feature).

Now suppose We have inserted,  deleted some records in tblStudentSource table

Before enable Change Tracking feature  we have following table data

Now once we have enabled this we have inserted a record and deleted a record from tblStudentSource

Now to track our changes we have to run following command

SELECT CTTable.StudentId ,
       ssource.FirstName , ssource.LastName,
       ssource.StudentId ,
       ssource.detail,
       ssource.Course,
       CTTable.SYS_CHANGE_OPERATION, CTTable.SYS_CHANGE_VERSION, CTTable.SYS_CHANGE_COLUMNS, CTTable.SYS_CHANGE_CONTEXT
FROM CHANGETABLE (CHANGES tblStudentSource, 1) AS CTTable
LEFT OUTER JOIN tblStudentSource AS ssource
ON ssource.StudentId = CTTable.studentId

 

Now if we see above snap we will find the all the changed row whether it is inserted or deleted is tracked by SQL SERVER and we can find it easily.

Here we would like to see some more important points.

So Whatever changes track by ChangeTracking feature of SQL Server is stored in a internal table which we can find with following command.

SELECT * FROM sys.internal_tables WHERE parent_object_id = OBJECT_ID('tblStudentSource')

If you see above query statement which we wrote to determine data which we have changed there is a function which we have used “CHANGETABLE”

The first parameter of this function is tablename with changes prefix and second parameter is Change tracking version number. Here Change tracking version number is nothing but a tracking of operation held on that particular version.

We can find the  current version by following command

SELECT  CHANGE_TRACKING_CURRENT_VERSION()

This version will helpful in synchronization.

I hope this might helpful to you somewhere in your project.

It is a good to know feature.

Thanks

Rj

Easy way to reseed identity column ? TIP #77

 

Sometimes, we may require to reseed identity column of a table due to various reason.

For example we have deleted a record from a Student which have an identity column StudentId.  Below is schema

Now it has 3 rows as shown below

Now suppose we have deleted record 3 which is studentID 3.

Now when we want to insert a new record StudentId start with 4 and we want it should be 3.

So how to check and reseed it ?

To achieve this task SQL SERVER provided DBCC CHECKIDENT facility.

DBCC CHECKIDENT has 2 commands

1) To Check current Identity value.

DBCC CHECKIDENT (TABLENAME, NORESEED)

2) To reseed an identity column we have following command

DBCC CHECKIDENT ( TABLENAME , RESEED, VALUE) 

Lets check student table identity to check current column value and current Identity value we will write following command

DBCC CHECKIDENT(tblStudentSource,NORESEED)

See below snap when after running this command

Now if you see 3 is current identity and column value 2 it means if I Insert a new row in tblStudentSource then that row will have studentId = 4 as a next identity column

Now I need to reseed it to 3 means if Insert a new record then studentId should be 3 to do this I have to reseed tblStudentSource.

DBCC CHECKIDENT(tblStudentSource,RESEED,2)

Now to cross check whether it is reseed or not I write NORESEED command again as get following result

It means identity value 2 and current column value is 2 means if now I insert a new record we will get studentId = 3 as a identity column this what we want actually.

So. We did NORESSED, RESSED an identity column.

I hope it may help you somewhere in your work.

Thanks

Rj

Sequence feature TIP #76

Although it is a old feature for those who knows ORACLE but for SQL server developers it is a new feature.

Let understand it by an example. Suppose we want an auto incremented column a part from primary key which is a identity column,

then to achieve this we can use sequence feature.

We can create  sequence feature by following command

“CREATE SEQUENCE StudentEnrollmentId AS INT
START WITH 2014000
INCREMENT BY 1”

so if you see above statement we have created a sequence with name StudentEnrollmentId which is an integer type sequence and first value means starting point is 2014000 and each time when we call sequence it will be incremented by 1.

We can create same sequence by screen also as shown in below figure

We have other option also  as shown in below

CREATE SEQUENCE SEQUENCE_NAME
AS DATA_TYPE
START WITH <constant>
INCREMENT BY <constant>
MINVALUE value
MAXVALUE value
CYCLE | NO CYCLE
CACHE int | NO CACHE

as shown in above option we can provide minimum & maximum for sequence. We have cycle option mean restart again after reaching maximum or minimum.

Now we can use it with following way

“SELECT NEXT VALUE FOR StudentEnrollmentId”

I hope this might help you somewhere.

Enjoy !!!

Rj !!!

How to determine free space on each fixed drive of server machine using SQL SERVER ? TIP #75

 

When you do  SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space .

Now what you need to do to get free space from each drive ?

Just create a simple job which run on daily basis which send you space report on daily basis.

This available space report help you to prepare yourself for next step.

The job will content a simple SQL statement which is

EXEC MASTER..xp_fixeddrives

When you run this command in your SQL Management Studio you will get a tabular result which have 2 columns which is drive & Free space in MB.

As shown in below figure

I hope this might help you somewhere.

Thanks & Enjoy!!!

RJ!!

How to disable constraints of a table ? TIP #74

 

You are reading this post just because of two reason

1) You are curious whether it is possible or not and why we require ?

2) You need to disable constraints 

So , let me share here that you can disable constraints at anytime of a table.

Sometime it is possible when you are doing bulk insert or you need to insert values in column and for this you need to disable constraints.

You can disable all the constraints of a table using following command

ALTER TABLE TABLENAME NOCHECK CONSTRAINT ALL ;

If you want to disable a specific constraint of a table then you can use following syntax

ALTER TABLE TABLENAME NOCHECK CONSTRAINT_NAME

For example suppose you want to disable all the constraints of  student table then you can write following syntax

ALTER TABLE dbo.Students NOCHECK ALL;

 

Below is very live example in Indian scenario

Suppose you added a check marks in last class more than 45% then only add student now due to some out side pressure you want to give admission to a student who has 40% percent then you need to disable percentage check.

ALTER TABLE dbo.Student NOCHECK chk_Student_Percentage_40

I hope this might help you somewhere.

Thanks

Rj!!!

How to determine Meta data or result set information of a stored procedure /trigger ? TIP # 73

 

Sometimes it may require that you don’t know what will be output of  a stored procedure ? what kind of result set it return ?

In such case SQL SERVER provided a new DMV statement which is sys.dm_exec_describe_first_result_set_for_object.

In other words if we want to know result set’s meta data then we can use it.

Lets understand this by an example.

Suppose we have an advertisementSelect stored procedure as shown below

As you see there are various column returning by the stored procedure.

Now let suppose we don’t have enough permission to view its definition or any other reason we are not able to view actual what is return in the stored procedure and now we want to know what is the result set then in such case we will use DMV command which is “SYS.DM_EXEC_DESCRIBE_FIRST_RESULT_SET_FOR_OBJECT”

We can use it as follows

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object
        (object_id('AdvertisementSelect'),0)

When we run it we will get result which we require as shown in below figure

If you see above screen you will find all the columns with their data type which will come as a result set of a stored procedure “AdvertisementSelect”

I hope this may help you somewhere.

Thanks

Rj!!!

How to replace a specific string from a large string with Write feature ? TIP #72

 

This is very interesting feature and I recently come to know this awesome feature.

Lets understand this by an example below

Suppose we have a student table with following structure  as shown in below image

 

We have a detail column with VARCHAR(MAX) .

Now as it is VARCHAR(MAX) column it may content a large amount of data currently it has following data as shown in below figure

Suppose we want to replace a specific string from this large column value like we want to replace “interested in” with “Always”  of  studentId = 1 then

we can use write function easily.

With the help of Write we can update a specific text/string  of a large column.

syntax of Write is as shown below

UPDATE TABLENAME

SET COLUMNNAME.WRITE (ReplacedWithString, startPosition, length)

WHERE clause

Now lets understand this by above student example where we want to replace “Interested in” which is doubled by mistake with “always”

So when you run above query you will get following result when you run select command.

great we have replaced the string which we want.

The main benefit of this the entire column value is not logged. suppose you have 2 GB data in your column then instead of logging 2 GB data at the time update only few KB will be logged.

I hope this might helpful to you somewhere.

Thanks !!!

RJ!!!

How explicitly insert default value ? TIP #72

 

Suppose, sometimes you just need  entry in the table with all the default values.

For example you have a Student table which looks like as below

CREATE TABLE #tmpStudent (StudentId INT IDENTITY(1,1),
                          FirstName VARCHAR(100) DEFAULT 'RAJAT',
                          Course    VARCHAR(100) DEFAULT 'MATHS')

 

Now , suppose for in some situation we need just default entry for example in my case I am just trying to insert 100 rows with default value so I need to write below statement

INSERT INTO #tmpStudent (FirstName ,Course ) VALUES (Default, default)

Now when I run the above statement. It insert default value of FirstName,course

I hope this might be helpful to you somewhere.

Enjoy!!!

RJ!!

How to pause execution for particular time or interval ? TIP #71

 

Suppose, We are working on a stored procedure in which some complex operation is going. We are fetching some value from a table and running some custom operations and after ending of this complex operation, we want a pause of suppose 10 seconds to run another job then in such situation we will use

“WAITFOR  DELAY HH:MM: SS” 

For example if we want to run select command (second statement) after 10 second delay of first command execution then we will use this as shown in below

INSERT INTO tblStudent (StudentId, StudentName)

VALUES (‘123’,’ABc’)

-- in the background we are running some cursor which would take aprox 10 seconds so we are waiting for 10 seconds here

WAITFOR  DELAY 00:00:10

SELECT * FROM tblStudentHistory

In other situation suppose you want to run specific command after particular time then in such cases we will use “WAITFOR TIME  HH: MM: SS”

suppose

“WAITFOR TIME 22:30:45 “   it means SQL statement will pause till 10:30:45 PM

I hope this tips may help you somewhere in your project.

Thanks

Enjoy!!!

RJ!!!

How to find all running SQL Server Instance on a machine ? TIP #70

Hello friends,

Many times it happened with me I forgot the instance name of SQL Server.

So here is a small trick which will help you in determining the entire running SQL Server instance on your machine.

You just need to run a simple command on Command prompt.


 C:\>SQLcmd -L

This simple command will provide entire running instance on your machine.

Enjoy this Trick.

Thanks & Best Regards,
RJ!!!

How to copy table structure only from a SQL Query ? Tip #69

 

Recently , one of my friends shared that some interviewer asked him a question “How to copy table structure only from a  SQL Query?”

So,

Below is simplest query to copy structure only of a table into another table.

SELECT *
Into #tmpStudentStructure
FROM tblStudentSource
WHERE 1= 0

in the above query we want to copy structure of tblStudentSource.

see below snap which help you to understand it more

I hope this might help you if some asked you this question.

Thanks & Enjoy!!!

RJ!!!

How easy it is to check which statements consuming most of the CPU & RAM–TIP #68

 

Problem:-  One of the most important question comes in our mind what is the cause of slow  SQL SERVER.

We always struggle with following questions

Which is highly CPU consuming query ?

Which is highly RAM consuming query ?

Who is blocking the transaction ?

and many more other performance dragging questions.

Believe me if you know who is culprit of making your system slow, you will win half battle.

Solution:-  SQL Server provided an easy way to clear your all doubts related to above questions. This easy way is “STANDARD REPORTS”.

You can access Standard Reports option by right clicking the SQL SERVER instance. These Standard reports contains not only performance related reports but other useful reports also.

If you see below image you will find there are many other reports option available.

Now, suppose I am interested to know which query consuming high CPU , to achieve this I clicked on   Performance Query – TOP Queries by Average CPU time or Performance query – Top queries  by total CPU time.

When I clicked on any of this option ,I got a report.

This report contained a bar graph & query detail in tabular format which contains query & CPU consuming time as shown below.

in similar way we can get answer of our other performance related query also.

I hope this may help you somewhere.

Thanks & Best Regards,

RJ!!

How to take Transaction Log backup ? TIP# 68

 

As we discussed in earlier tips how to take full backup and differential backup now in this tip we will see how to take transaction backup .

Before taking transaction backup the first and mandatory condition is that you should have a full backup.

Just right click the database and go to backup option as you will get following screen. Now we need to select Transaction Log   option in   backup type dropdown.

Once this is selected we need to give a transaction log backup name as shown below. The extension should be .TRN

 

 

Once you provided all the mandatory values just click on then you will get your  Transaction Log backup.

We can take Log backup with  SQL command also

For example :-

BACKUP LOG IndiandotnetDB
TO DISK ='E:\IndiandotnetTransaction.trn'

When you run above command you will get following screen and your log backup is done.

I hope this will help you.

Thanks & Enjoy!!!

RJ!!

How to take differential backup ? TIP #67

Friends,
We were discussing backup from last few post. Now lets take a look one more feature of backup which is differential backup.
Differential backup  is backup after last full backup. Before taking Differential backup  it is a mandatory condition to take full backup.
Differential backup can speed up your recovery process or we can say restore process.
In below example we would understand how to take differential backup
To take differential backup we can run following command
BACKUP DATABASE IndiandotnetDB
TO DISK = 'D:\IndiandotnetDB.dif'
WITH DIFFERENTIAL;
GO
Or we can take differential backup by following steps also
Right click the database select database backup option you will get same screen which we explain at the time of full backup.
if you see below screen you will find we have change backup type to Differential and  taking backup to d drive with name Indiandotnetdb.dif

Once you click OK button. your differential backup will be completed.
I hope this tip might help you somewhere.
Thanks & Enjoy!!!
RJ!!

Oh! I forgot When I took last Database Backup ? How to determine this? TIP # 66

 

In last few tips TIP #64 & TIP #65  we get information  how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup.

Problem:-   Sometimes , we are interested to know when last backup is taken for particular database ?

Solution:  The first thing I want to share here that  you should have proper maintenance plan to take backup of database and by job History you are able to know when last backup is taken but if this is not the case then not to worry SQL Server maintain itself information related to backup. to determine this you need to write a simple query as shown below

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
   END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

ORDER BY 
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

  When I run above command I get information related backup like databasename, backup start date, Backup finish date, expiry date(if any) , backup type like whether it is database backup or Log backup , size of backup , path where database is taken, name of backup set.

see below snap for detail

I hope this tip might help you somewhere.

Thanks !!

Rj!!

Did I take right backup ! how to ensure backup can be restore ? TIP # 65

 

Problem:- We have seen last time how to take backup in tip # 64.   but sometimes it happened we took backup  and we are not able to restore it. It might be corrupted.

Now , Next step thought come in our mind how to ensure we took right backup which can be restore.

Solution:-  To ensure backup is correct. We can check following option as shown in below figure.

in Reliability section  check following option

1) Verify backup when Finished

2) Perform checksum before writing  to media

 

Once this option is check SQL server automatically cross check verify the backup when it is finished and by checking “Verify backup when Finished”

With CheckSum SQL Server cross check before taking backup.

We can also write following query

BACKUP DATABASE IndiandotnetDB
TO DISK = 'D:\Indiandotnet.bak'
WITH CHECKSUM;

Now to assure more we can write following command and verify whether the database can be restored or not whether the backup set is valid or not.

RESTORE VERIFYONLY
FROM DISK = 'D:\Indiandotnet.bak'
WITH CHECKSUM

I hope this might helpful you somewhere.

Thanks!!

RJ!!

How to take compressed backup in SQL Server TIP #64

Problem:-  A good maintenance plan always said to take backup but sometimes it is very difficult to take backup of database just because of space constraint.
We take backup but we don’t have enough space available.
Solutions:  SQL server provided  the best way and gave us solution of compressed backup. Now how to take compressed backup let see step by step here.
Step 1:-  Select the database whose backup you want. Just right click and select backup option as shown in below figure

Step 2:-  Once you click the backup option an new screen will appear as shown below

Step 3:-  Now click on  Add button as shown in above screen. Once you click on the add button a new screen will appear as shown below where you have to provide the path and file name of compressed backup file.

Step 4:-   Once you provided the filename click OK to the button now click on the options menu on right hand you will get new tab option here in the last you will get compress option as shown in below figure with arrow. Select compress backup option in dropown
once this done click on OK button now you are good to go. Your compressed backup is done. and you will get backup complete message as shown below

Or else you can use following command
BACKUP DATABASE INDIANDOTNETDB
TO DISK = 'E:\MyCompressedBackup.bak'
WITH COMPRESSION ;
You will get compressed backup.

I hope this will help you somewhere.
Enjoy!!!
RJ!!