Delete Change Tracking table data sys.syscommittab

,

1. Find the table size of the hidden change tracking table

USE [ChangeTrackingTableDatabase]
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
GO 
select sct1.name as CT_schema, 
sot1.name as CT_table, 
ps1.row_count as CT_rows, 
ps1.reserved_page_count*8./1024. as CT_reserved_MB, 
sct2.name as tracked_schema, 
sot2.name as tracked_name, 
ps2.row_count as tracked_rows, 
ps2.reserved_page_count*8./1024. as tracked_base_table_MB, 
change_tracking_min_valid_version(sot2.object_id) as min_valid_version 
FROM sys.internal_tables it 
JOIN sys.objects sot1 on it.object_id=sot1.object_id 
JOIN sys.schemas AS sct1 on 
sot1.schema_id=sct1.schema_id 
JOIN sys.dm_db_partition_stats ps1 on 
it.object_id = ps1. object_id 
and ps1.index_id in (0,1) 
LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id 
LEFT JOIN sys.schemas AS sct2 on 
sot2.schema_id=sct2.schema_id 
LEFT JOIN sys.dm_db_partition_stats ps2 on 
sot2.object_id = ps2. object_id 
and ps2.index_id in (0,1) 
WHERE it.internal_type IN (209, 210); 
GO

2. Disable Change tracking on Tables.

use ChangeTrackingTableDatabase

go

Alter table dbo.TableName Disable Change Tracking;
go
2. Create a sql file with the below script. Replace the database name and servername in the script.
use <DatabaseName>
go
if (@@servername <> 'Servername' or db_name() <> 'DB_Name')
begin
raiserror ('The SERVER or DATABASE IS NOT CORRECT' , 20, -1) with log;
end 
select getdate() as 'Start Time' 
go
delete sys.syscommittab
go
select getdate() as 'End Time'
go
sp_spaceused 'sys.syscommittab'
go
3. Run the .sql file from cmd prompt with the below script
sqlcmd -Ssvrname1 -A -E -i c:\temp\Delete_committab.sql
1. Disable change tracking on table

Alter table dbo.TableName Disable Change Tracking;
go


2. Create a sql file with the below script. Replace the database name in the script.
use <DatabaseName>
go

if (@@servername <> 'Servername' or db_name() <> 'DB_Name')
begin
	raiserror ('The SERVER or DATABASE IS NOT CORRECT' , 20, -1) with log;

end 

select getdate() as 'Start Time' 
go

delete sys.syscommittab
go

select getdate() as 'End Time'
go

sp_spaceused 'sys.syscommittab'
go

4.Delete syscommit table. run in command prompt.
sqlcmd -Ssvrname -A -E -i c:\temp\Delete_committab.sql

Rate

Share

Share

Rate