1. check table from database
USE teguhth
GO
select @@servername as ServerName, DB_NAME() as DBName,* from pembelian;
select @@servername as ServerName, DB_NAME() as DBName,* from teguhth.dbo.pembelian;
//** command for data cdc become
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.change_tables
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.dbo_pembelian_CT
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.dbo_pembelian_CT ORDER BY __$start_lsn DESC
**//
2. enable Change Data Capture (CDC) from database level & Table
1. At DB level:
USE teguhth
GO
EXEC sys.sp_cdc_enable_db;
2. At table level:
USE teguhth
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'pembelian',
@role_name = null,
@supports_net_changes = 0;
-- , @capture_instance = N'Address'
-- , @capture_instance = NULL
-- , @captured_column_list = N'KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN'
-- , @filegroup_name = N'PRIMARY';
Job 'cdc.teguhth_capture' started successfully.
Job 'cdc.teguhth_cleanup' started successfully.
3. Check if CDC has been enabled at database level
USE teguhth
GO
select @@servername as ServerName, DB_NAME() as DBName,name, is_cdc_enabled
from sys.databases
GO
select @@servername as ServerName, DB_NAME() as DBName,name, is_cdc_enabled
from sys.databases where name = 'teguhth'
GO
4. Check if CDC is enabled at the table level
USE teguhth
GO
select @@servername as ServerName, DB_NAME() as DBName,name,type,type_desc,is_tracked_by_cdc
from sys.tables
USE teguhth
GO
select @@servername as ServerName, DB_NAME() as DBName,name,type,type_desc,is_tracked_by_cdc
from sys.tables where name='pembelian'
5. check cdc before update insert delete
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.change_tables
select @@servername as ServerName, DB_NAME() as DBName,
CASE __$operation
WHEN 1 THEN 'delete'
WHEN 2 THEN 'insert'
WHEN 3 THEN 'update before'
WHEN 4 THEN 'update after'
ELSE 'Unknown'
END 'operation type', *
from cdc.dbo_pembelian_CT
select @@servername as ServerName, DB_NAME() as DBName,
CASE __$operation
WHEN 1 THEN 'delete'
WHEN 2 THEN 'insert'
WHEN 3 THEN 'update before'
WHEN 4 THEN 'update after'
ELSE 'Unknown'
END 'operation type', *
from cdc.dbo_pembelian_CT
ORDER BY __$start_lsn DESC
6. test insert
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E006','ELK-02','J-0001','2002-06-24',1);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E007','ELK-02','J-0002','2002-06-24',1);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E008','ELK-02','B-0001','2002-06-25',2);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E009','ELK-02','B-0002','2002-06-25',2);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E010','ELK-03','J-0001','2002-06-20',5);
7. check cdc after insert
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.change_tables
select @@servername as ServerName, DB_NAME() as DBName,
CASE __$operation
WHEN 1 THEN 'delete'
WHEN 2 THEN 'insert'
WHEN 3 THEN 'update before'
WHEN 4 THEN 'update after'
ELSE 'Unknown'
END 'operation type', *
from cdc.dbo_pembelian_CT
select @@servername as ServerName, DB_NAME() as DBName,
CASE __$operation
WHEN 1 THEN 'delete'
WHEN 2 THEN 'insert'
WHEN 3 THEN 'update before'
WHEN 4 THEN 'update after'
ELSE 'Unknown'
END 'operation type', *
from cdc.dbo_pembelian_CT
ORDER BY __$start_lsn DESC
8. update
update teguhth.dbo.pembelian
set JUMLAH_PEMBELIAN = 106 where KODE_PEMBELIAN='BEL-E006'
update teguhth.dbo.pembelian
set JUMLAH_PEMBELIAN = 107 where KODE_PEMBELIAN='BEL-E007'
update teguhth.dbo.pembelian
set JUMLAH_PEMBELIAN = 108 where KODE_PEMBELIAN='BEL-E008'
update teguhth.dbo.pembelian
set JUMLAH_PEMBELIAN = 109 where KODE_PEMBELIAN='BEL-E009'
update teguhth.dbo.pembelian
set JUMLAH_PEMBELIAN = 110 where KODE_PEMBELIAN='BEL-E010'
9. check cdc after update
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.change_tables
select @@servername as ServerName, DB_NAME() as DBName,
CASE __$operation
WHEN 1 THEN 'delete'
WHEN 2 THEN 'insert'
WHEN 3 THEN 'update before'
WHEN 4 THEN 'update after'
ELSE 'Unknown'
END 'operation type', *
from cdc.dbo_pembelian_CT
select @@servername as ServerName, DB_NAME() as DBName,
CASE __$operation
WHEN 1 THEN 'delete'
WHEN 2 THEN 'insert'
WHEN 3 THEN 'update before'
WHEN 4 THEN 'update after'
ELSE 'Unknown'
END 'operation type', *
from cdc.dbo_pembelian_CT
ORDER BY __$start_lsn DESC
10. delete
delete from teguhth.dbo.pembelian
where KODE_PEMBELIAN='BEL-E006'
delete from teguhth.dbo.pembelian
where KODE_PEMBELIAN='BEL-E007'
delete from teguhth.dbo.pembelian
where KODE_PEMBELIAN='BEL-E008'
delete from teguhth.dbo.pembelian
where KODE_PEMBELIAN='BEL-E009'
delete from teguhth.dbo.pembelian
where KODE_PEMBELIAN='BEL-E010'
11. check cdc after update
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.change_tables
select @@servername as ServerName, DB_NAME() as DBName,
CASE __$operation
WHEN 1 THEN 'delete'
WHEN 2 THEN 'insert'
WHEN 3 THEN 'update before'
WHEN 4 THEN 'update after'
ELSE 'Unknown'
END 'operation type', *
from cdc.dbo_pembelian_CT
select @@servername as ServerName, DB_NAME() as DBName,
CASE __$operation
WHEN 1 THEN 'delete'
WHEN 2 THEN 'insert'
WHEN 3 THEN 'update before'
WHEN 4 THEN 'update after'
ELSE 'Unknown'
END 'operation type', *
from cdc.dbo_pembelian_CT
ORDER BY __$start_lsn DESC
12. Monitoring
EXEC sys.sp_cdc_help_change_data_capture
select @@servername as ServerName, DB_NAME() as DBName,object_name(object_id) as NewTableCDC,* from cdc.captured_columns;
select @@servername as ServerName, DB_NAME() as DBName,object_name(object_id) as NewTableCDC,* from cdc.change_tables;
select @@servername as ServerName, DB_NAME() as DBName,object_name(object_id) as NewTableCDC,object_name(source_object_id) as SourceObject, * from cdc.change_tables;
select @@servername as ServerName, DB_NAME() as DBName,object_name(object_id) as NewTableCDC,* from cdc.ddl_history;
select @@servername as ServerName, DB_NAME() as DBName,object_name(object_id) as NewTableCDC,* from cdc.index_columns;
select @@servername as ServerName, DB_NAME() as DBName,* from cdc.lsn_time_mapping;
13. Capacity Size Table
SELECT @@Servername as [ServerName],db_name() as [DBName],getdate() [Date],
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows as Rows,
-- SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
-- SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
-- (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t with (NOLOCK)
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
where s.Name='cdc'
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name
14. capacity report
SELECT @@SERVERNAME as [Server_name], db_name() [DB_Name], getdate() as [Date],
--row_number() over(order by a3.name, a2.name))%2 as l1,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
((a1.reserved + ISNULL(a4.reserved,0))* 8)/1024 AS reserved_in_MB,
(a1.data * 8)/1024 AS data_in_MB,
((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END))/1024 * 8 AS index_size_in_MB,
((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8)/1024 AS unused_in_MB
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps with (NOLOCK)
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables with (NOLOCK) WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps with (NOLOCK)
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT' and a3.name='cdc'
ORDER BY reserved_in_MB desc
No comments:
Post a Comment