Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance, Line 36 Could not create a capture instance because the capture instance name ‘dbo_ChangeDataCapture’ already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

After reading my previous article on CDC(Change Data Capture), one of the reader had tried to do it in hisdevelopment server but in his server, CDC was already enabled and anotherco-incident was that, the table he has choose for CDC, was already having itscapture instance, may be any of the other team member might have done it and hedoesn’t knew that , so he was greeted with the error like given below.
Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance,Line 36
Could not create a capture instance because the capture instance name’dbo_ChangeDataCapture’ already exists in the current database. Specify anexplicit unique name for the parameter @capture_instance.
Error message is pretty much clear that the table he was usingwas already having capture instance so obviously he should use another tablefor this testing purpose or remove CDCfrom that table and re-create it or while activating CDCfor that table, he has to provide another name for capture instance table by providing@Capture_Instance name explicitly (not recommended). I will provide TSQL neededfrom disable CDClater in this article.
Well, these are some of the solution when you face abovegiven error message but as said by someone that “Prevention is better than cure”, that reader asked me how do I knoweven before activating CDCthat the table is CDC enabled or not. 
It is not a big issue; you can use very small TSQL querieslike below when you want to know it.
–list out thename of databases which are CDC enabled
SELECT [name], database_id
FROM master.sys.databases WHERE  is_cdc_enabled =1    
GO
–list out alltables which are CDC enabled in your database
SELECT [name] AS Table_name
FROM sys.tables  WHERE is_tracked_by_cdc =1
GO
–know indetails like which table is CDC enabled
–which is thecapture instance of that table
EXEC sys.sp_cdc_help_change_data_capture
GO
–disable CDCfrom your table.
EXECUTE sys.sp_cdc_disable_table
    @source_name = N’ChangeDataCapture’,
    @source_schema =N’dbo’,
    @capture_instance =N’dbo_ChangeDataCapture’;
   
–disable CDCfrom your database
EXEC sys.sp_cdc_disable_db
GO
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance, Line 36 Could not create a capture instance because the capture instance name ‘dbo_ChangeDataCapture’ already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

After reading my previous article on CDC(Change Data Capture), one of the reader had tried to do it in hisdevelopment server but in his server, CDC was already enabled and anotherco-incident was that, the table he has choose for CDC, was already having itscapture instance, may be any of the other team member might have done it and hedoesn’t knew that , so he was greeted with the error like given below.
Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance,Line 36
Could not create a capture instance because the capture instance name’dbo_ChangeDataCapture’ already exists in the current database. Specify anexplicit unique name for the parameter @capture_instance.
Error message is pretty much clear that the table he was usingwas already having capture instance so obviously he should use another tablefor this testing purpose or remove CDCfrom that table and re-create it or while activating CDCfor that table, he has to provide another name for capture instance table by providing@Capture_Instance name explicitly (not recommended). I will provide TSQL neededfrom disable CDClater in this article.
Well, these are some of the solution when you face abovegiven error message but as said by someone that “Prevention is better than cure”, that reader asked me how do I knoweven before activating CDCthat the table is CDC enabled or not. 
It is not a big issue; you can use very small TSQL querieslike below when you want to know it.
–list out thename of databases which are CDC enabled
SELECT [name], database_id
FROM master.sys.databases WHERE  is_cdc_enabled =1    
GO
–list out alltables which are CDC enabled in your database
SELECT [name] AS Table_name
FROM sys.tables  WHERE is_tracked_by_cdc =1
GO
–know indetails like which table is CDC enabled
–which is thecapture instance of that table
EXEC sys.sp_cdc_help_change_data_capture
GO
–disable CDCfrom your table.
EXECUTE sys.sp_cdc_disable_table
    @source_name = N’ChangeDataCapture’,
    @source_schema =N’dbo’,
    @capture_instance =N’dbo_ChangeDataCapture’;
   
–disable CDCfrom your database
EXEC sys.sp_cdc_disable_db
GO
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Change Data Capture (CDC) in SQL Server 2008

Recently I was working on one project and client required toaudit the data, means, insert / update / delete should be tracked on someimportant table. There are many ways you can capture these information (AuditTrail), I have written quite a few articles on this subject too but all thoseneeds additional code to be written. Fortunately my client is using SQL Server2008 R2 version so I don’t even need to write down any specific code to capturechanged data as there is facility of CDC (Change Data capture) in SQL Server2008.
Even before we look at CDC in details, make sure you haveyour SQL Server Agent is running as CDC in SQL Server 2008 will use SQL ServerAgent to make audit trail for you. If your SQL Server Agent is not running atthe moment, start it from control Panel->Administrative Tools-> Services.You can find “SQL Server Agent (YourInstanceName)”, just start this service andthen you will be able to work on CDC.
create database SQLHub
GO
use SQLHub
go
Create Table ChangeDataCapture
(
      ID INT Identity(1,1)
      ,Name varchar(20)
)
GO
–enable CDC inSQLHub database
–this willcreate “cdc” schema in SQLhub database too
–along with”cdc” schema, it will create some system table
–in”cdc” schema
USE SQLHub
GO
EXEC sys.sp_cdc_enable_db
GO
–now enable CDCfor our table created above.
–when you willenable cdc for ChangeDataCapture table
–it will createtwo job under SQL Server Agent
–which willread data from transaction whenever you will make any change in data
–and stores itin CDC table
USE SQLHub
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’ChangeDataCapture’,
@role_name     = NULL
GO
–now you haveorginal table, named “ChangeDataCapture”
–another tableto keep all changes “ChangeDataCapture”,
–which has beencreated in step above
select * fromChangeDataCapture
–followingtable will be there under category of “System Tables” folder.
select * from cdc.dbo_ChangeDataCapture_CT
–now let usmake some DML operation in ChangeDataCapture table and
–observe howdoes it stores data in cdc.dbo_ChangeDataCapture_CT
INSERT INTO ChangeDataCapture
SELECT ‘Ritesh Shah’ UNION ALL
SELECT ‘Rajan Shah’ UNION ALL
SELECT ‘Teerth Shah’
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.
–2 representINSERT.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofUPDATE now.
UPDATEChangeDataCapture
SET Name=‘Rajan Jain’ WHERE Name=‘Rajan Shah’
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.
–3 representvalue before UPdate and 4 represent new value after update.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofDELETE now
Delete From ChangeDataCapture WHEREID=2
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.
–1 representDELETE operation
select * from cdc.dbo_ChangeDataCapture_CT
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Change Data Capture (CDC) in SQL Server 2008

Recently I was working on one project and client required toaudit the data, means, insert / update / delete should be tracked on someimportant table. There are many ways you can capture these information (AuditTrail), I have written quite a few articles on this subject too but all thoseneeds additional code to be written. Fortunately my client is using SQL Server2008 R2 version so I don’t even need to write down any specific code to capturechanged data as there is facility of CDC (Change Data capture) in SQL Server2008.
Even before we look at CDC in details, make sure you haveyour SQL Server Agent is running as CDC in SQL Server 2008 will use SQL ServerAgent to make audit trail for you. If your SQL Server Agent is not running atthe moment, start it from control Panel->Administrative Tools-> Services.You can find “SQL Server Agent (YourInstanceName)”, just start this service andthen you will be able to work on CDC.
create database SQLHub
GO
use SQLHub
go
Create Table ChangeDataCapture
(
      ID INT Identity(1,1)
      ,Name varchar(20)
)
GO
–enable CDC inSQLHub database
–this willcreate “cdc” schema in SQLhub database too
–along with”cdc” schema, it will create some system table
–in”cdc” schema
USE SQLHub
GO
EXEC sys.sp_cdc_enable_db
GO
–now enable CDCfor our table created above.
–when you willenable cdc for ChangeDataCapture table
–it will createtwo job under SQL Server Agent
–which willread data from transaction whenever you will make any change in data
–and stores itin CDC table
USE SQLHub
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’ChangeDataCapture’,
@role_name     = NULL
GO
–now you haveorginal table, named “ChangeDataCapture”
–another tableto keep all changes “ChangeDataCapture”,
–which has beencreated in step above
select * fromChangeDataCapture
–followingtable will be there under category of “System Tables” folder.
select * from cdc.dbo_ChangeDataCapture_CT
–now let usmake some DML operation in ChangeDataCapture table and
–observe howdoes it stores data in cdc.dbo_ChangeDataCapture_CT
INSERT INTO ChangeDataCapture
SELECT ‘Ritesh Shah’ UNION ALL
SELECT ‘Rajan Shah’ UNION ALL
SELECT ‘Teerth Shah’
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.
–2 representINSERT.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofUPDATE now.
UPDATEChangeDataCapture
SET Name=‘Rajan Jain’ WHERE Name=‘Rajan Shah’
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.
–3 representvalue before UPdate and 4 represent new value after update.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofDELETE now
Delete From ChangeDataCapture WHEREID=2
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.
–1 representDELETE operation
select * from cdc.dbo_ChangeDataCapture_CT
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Find your backup history in SQL Server

While managing multiple databases, it is often needed tocheck database backup history like when did we taken backup? Where did we takelast few backups? What was the size of last few backup? And many other information.
I have setup maintenance plan for few database, fewdatabases backup taken by third party software and all works on schedule on particulartime even I keep one small T-SQL script handy to check all information I havementioned above.
SELECT     
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_NameAS BackupPath,
    MedSet.Software_NameAS SoftwareUsedForBackup,
    bkSet.User_Name ASBackupTakenBy,
    bkSet.Server_NameAS ServerName,
    bkSet.Database_NameAs DatabaseName,
    CASE bkSet.Type     
            WHEN ‘L’ THEN ‘TransactionLogBackup’
            WHEN ‘D’ THEN ‘FullBackup’
            WHEN ‘F’ THEN ‘FileBackup’
            WHEN ‘I’ THEN ‘DifferentialBackup’
        WHEN ‘G’ THEN ‘DifferentialFileBackup’
        WHEN ‘P’ THEN ‘PartialBackup’
        WHEN ‘Q’ THEN ‘DifferentialPartialBackup’
        ELSE NULL END AS BackupType,
    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB
FROM       
      msdb..BackupMediaFamily MedFam
INNER JOIN 
      msdb..BackupMediaSet MedSet
ON
      MedFam.Media_Set_ID = MedSet.Media_Set_ID
INNER JOIN 
      msdb..BackupSet bkSet
ON
      bkSet.Media_Set_ID = MedSet.Media_Set_ID
WHERE     
      –keep your database name incondition
      bkSet.Database_Name = ‘Adventureworks’
AND       
      –put the date between which you wantto find details of backup
      bkSet.Backup_Finish_Date BETWEEN ‘2011-07-01’ AND ‘2011-07-10’
ORDER BY   
      bkSet.Backup_Finish_Date DESC

Reference:Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
 http://www.SQLHub.com
Askme any SQL Server related question at my “ASK Profile

Find your backup history in SQL Server

While managing multiple databases, it is often needed tocheck database backup history like when did we taken backup? Where did we takelast few backups? What was the size of last few backup? And many other information.
I have setup maintenance plan for few database, fewdatabases backup taken by third party software and all works on schedule on particulartime even I keep one small T-SQL script handy to check all information I havementioned above.
SELECT     
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_NameAS BackupPath,
    MedSet.Software_NameAS SoftwareUsedForBackup,
    bkSet.User_Name ASBackupTakenBy,
    bkSet.Server_NameAS ServerName,
    bkSet.Database_NameAs DatabaseName,
    CASE bkSet.Type     
            WHEN ‘L’ THEN ‘TransactionLogBackup’
            WHEN ‘D’ THEN ‘FullBackup’
            WHEN ‘F’ THEN ‘FileBackup’
            WHEN ‘I’ THEN ‘DifferentialBackup’
        WHEN ‘G’ THEN ‘DifferentialFileBackup’
        WHEN ‘P’ THEN ‘PartialBackup’
        WHEN ‘Q’ THEN ‘DifferentialPartialBackup’
        ELSE NULL END AS BackupType,
    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB
FROM       
      msdb..BackupMediaFamily MedFam
INNER JOIN 
      msdb..BackupMediaSet MedSet
ON
      MedFam.Media_Set_ID = MedSet.Media_Set_ID
INNER JOIN 
      msdb..BackupSet bkSet
ON
      bkSet.Media_Set_ID = MedSet.Media_Set_ID
WHERE     
      –keep your database name incondition
      bkSet.Database_Name = ‘Adventureworks’
AND       
      –put the date between which you wantto find details of backup
      bkSet.Backup_Finish_Date BETWEEN ‘2011-07-01’ AND ‘2011-07-10’
ORDER BY   
      bkSet.Backup_Finish_Date DESC

Reference:Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
 http://www.SQLHub.com
Askme any SQL Server related question at my “ASK Profile

FileStream in SQL Server 2008+

One of my friends is working on one project which used tostore images and some document files. He is using BLOB fields to get this taskdone, when he was discussing this with me, I suggested him to use FileStreamfeature instead of the way he is using currently. There are some reasons forthat and I am going to explore it in this article.
In SQL Server 2005, we had two option to deal with binarydata (image, document etc.).
1.)    Storeimages/documents in OS’ file system and keep pointer (path) of thatimages/documents into database table
2.)    Use BLOB fields to store images/documents directly in database table.
So far we used to take any of the above approaches but bothhave their own limitations. In first approach, transactional consistency is theissue, when you take backup of database, it won’t get backup of the folderswhere we are keeping our files (images/documents). In second approach, you canstore binary data directly in SQL Server database table so whenever you getdatabase backup, it will backed up by its own but it affects performance whileconverting that binary data from database table itself, other than that, BLOB field has limitation of 2GB.
To overcome these limitations, Microsoft developer teamprovided very cool feature, named “Filestream” in SQL Server 2008. With help of“Filestream”, you can store images/documents/videos directly in windows NTFSfile system, it has no limitations of 2GB like BLOB and when you take backup ofdatabase, your Filestream data will be backed up by its own. Apart from that,you can get advantage of NTFS streaming APIs for efficient and performancedriven file operation.
To create Filestream enable column in your table, you haveto have a file group in your database which is enable for “FileStream” and youwill also need one column in your table which should have varbinary(max) data typeso that images/documents/videos could be stored there.
Even before doing any of the above things, you have toenable “Filestream” in your SQL Server which is by default disable.
USE MASTER
GO
–if file streamis not already enable in your server
–look atfollowing link to enable it.
EXEC sp_configure filestream_access_level, 2
–why I haveused 2? know it from below link.
—-http://technet.microsoft.com/en-us/library/cc645956.aspx
RECONFIGURE
GO
CREATE DATABASE SQLHubFileStream ONPRIMARY
(
      NAME = SQLHubFileStream_data,
      FILENAME = N’D:\TestDB\SQLHubFileStream_data.mdf’
),
FILEGROUPSQLHubFileStream_FS CONTAINS FILESTREAM
(
      NAME =SQLHubFileStream_FILESTREAM,
      FILENAME = N’D:\TestDB\SQLHubFileStream_FS’
)
 LOG ON
(
      NAME = SQLHubFileStream_LOG,
      FILENAME = N’D:\TestDB\SQLHubFileStream_log.ldf’
);
GO
USESQLHubFileStream
GO
CREATE TABLE Customers
(
      ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
      Name varchar(25),
      CustPhoto VARBINARY(MAX) FILESTREAM
)
GO
–I have alreadykept “Ritesh-Teerth.JPG” file in my D drive,
–you have toprovide your own path and file name here.
INSERT INTO Customers
VALUES
(NEWID(),‘Ritesh Shah’,(select * FROM OPENROWSET(BULK ‘D:\Ritesh-Teerth.JPG’, SINGLE_BLOB) AS img))
GO
select * from Customers
go
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile