SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Delete Change Tracking table data sys.syscommittab

By Harsha Bhagat,

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

Total article views: 138 | Views in the last 30 days: 138
 
Related Articles
ARTICLE

Change Tracking - 2008

Change tracking in SQL Server 2008 enables applications to obtain only changes that have been made t...

ARTICLE

Change Tracking and Database Refactoring

Using change tracking in SQL Server 2008 to aid in database refactoring within an OLTP system.

ARTICLE

Version Control - Part 2 - Tracking Changes

Part 2 of Steve Jones' series on version control. This article examines how you can track the change...

FORUM

changes objects ?

Howto find all changed objects since a date

FORUM

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object

 
Contribute