June 11, 2012 at 2:54 pm
I need the quickest way to increment the Change Tracking counter for a given database.
There is a known bug in SQL 2008 (non R2) where this value can get set to 0 in an automated mirror failover scenario. We’ve hit this bug in production and it’s trying to kill us. 🙁 I'll try and find/post the KB article to make this more findable for any other sufferers.
The target #: I need to get it to 55.6 million.
A brief summary of the things I’ve done:
4000 = Most updates per minute I can generate with High Safety Mirroring enabled
170000 = Most updates per minute I can generate with High Safety Mirroring DISabled (nice improvement!)
However, I'm still ~8hrs away from completion - I'd like to be fewer hours away... but my brain is tapped.
I've gone through several iterations, and here is the current solution I’m using, but I’m wondering if anyone can quickly recommend any improvements?
Create the table – create as small a table & row as possible just to bump up the number:
CREATE TABLE [dbo].[ChangeTrack_Fix_A](
[ID] [bit] UNIQUE NOT NULL,
[BitField] [bit] NOT NULL,
CONSTRAINT [PK_ChangeTrack_Fix_A] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[ChangeTrack_Fix_A] ENABLE CHANGE_TRACKING
INSERT INTO [dbo].[ChangeTrack_Fix_A] ([ID],[BitField])VALUES (1,0);
My current code to increment the change tracking # as quickly as possible
Note: The repeated update doesn’t seem to provide any additional throughput.
The code will run for at least a minute
The looping for the actual running is running for 5 min and auto restarts - I'm just trying to save you time in helping me so it just runs for 1 min and doesn't restart. 🙂
SET NOCOUNT ON
IF EXISTS (SELECT COUNT(*) FROM sys.change_tracking_tables)
BEGIN
DECLARE @CurrentVersion BIGINT = CHANGE_TRACKING_CURRENT_VERSION()
, @DateStart DATETIME = GETDATE()
, @FakeTargetBump BIGINT = 10000
, @EndDate DATETIME
DECLARE @TargetVersion BIGINT = (SELECT MAX(commit_ts)+@FakeTargetBump from sys.dm_tran_commit_table)
SELECT @CurrentVersion CurrentVersion, @TargetVersion TargetVersion
DECLARE @CurrentVersionHolder INT = @CurrentVersion
,@CTPerMin Decimal(10,2)
,@StartTime DATETIME
,@RunTime_InSec INT
SELECT @StartTime = GETDATE()
IF @CurrentVersion < @TargetVersion
BEGIN
WHILE @CurrentVersion < @TargetVersion
BEGIN
SET @EndDate = DATEADD(MINUTE,1,GETDATE())
WHILE GETDATE() < @EndDate
BEGIN
UPDATE dbo.[ChangeTrack_Fix_A]
SET BitField = 0;
UPDATE dbo.[ChangeTrack_Fix_A] /*Just added to reduce the # of times the While eval is hit – no real improvement noticed*/
SET BitField = 0;
END
SET @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION()
END
END
SET @RunTime_InSec = DATEDIFF(second,@StartTime,GETDATE())
SELECT @TargetVersion TARGET_CT_VERSION
,@CurrentVersion AS [CHANGE_TRACKING_CURRENT_VERSION]
,((@CurrentVersion-@CurrentVersionHolder)/(@RunTime_InSec*1.0))*60 AS CT_Increments_Per_Minute
END
June 11, 2012 at 2:56 pm
Just to be clear:
I'm looking for the lightest weight method possible to increment the Change Tracking #.
June 13, 2012 at 1:44 pm
For posterity... the things we tried that worked, and some after-the-pain ideas.
The bug:
http://support.microsoft.com/kb/2344694
If you have landed here, and have a lot of change tracking numbers to increment through (like we did), I offer advice.
I have some good advice, some brute force advice, and some experimental advice.
Good advice:
How recent was your last full backup BEFORE you hit the bug? I don't believe this bug is carried into the TLOG backups. A quicker way for us to have recovered would've been to restore the last full backup then attempt to restore log files skipping any "infected" full backups (if your media set is the same and the system lets you do this).
You will have to break mirroring and restore it again, but it might get you back up sooner.
For us, this might have gotten us up in 3.5 hrs compared to the 10 hrs it took.
There is still some risk to this, I didn't have the means of testing if the TLOG backups also got "infected" with the bad change tracking number.
Brute force advice:
This was the path we chose. We ran it as a sql jobs so we could run multiple threads and for us found that 5 threads worked best (adjust the table names to A/B/C/D/E/etc for each thread). A bigger machine could possibly run more, a smaller machine might need to run less. We were on a mid sized machine with a small/midsized SAN.
Code to increment:
SET NOCOUNT ON
CREATE TABLE [dbo].[ChangeTrack_Fix_A](
[ID] [bit] UNIQUE NOT NULL,
[BitField] [bit] NOT NULL,
CONSTRAINT [PK_ChangeTrack_Fix_A] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[ChangeTrack_Fix_A] ENABLE CHANGE_TRACKING
INSERT INTO [dbo].[ChangeTrack_Fix_A] ([ID],[BitField])VALUES (1,0);
SET NOCOUNT ON
IF EXISTS (SELECT COUNT(*) FROM sys.change_tracking_tables)
BEGIN
DECLARE @MinVersion BIGINT = 0
, @CurrentVersion BIGINT = 0
, @DateStart DATETIME = GETDATE()
IF CHANGE_TRACKING_CURRENT_VERSION() < (SELECT MAX(commit_ts) from sys.dm_tran_commit_table)
BEGIN
DECLARE @TargetHolder BIGINT = (SELECT MAX(commit_ts) from sys.dm_tran_commit_table)
DECLARE @EndDate DATETIME
WHILE CHANGE_TRACKING_CURRENT_VERSION() < @TargetHolder
BEGIN
SET @EndDate = DATEADD(minute,5,GETDATE())
WHILE GETDATE() < @EndDate
BEGIN
UPDATE dbo.[ChangeTrack_Fix_A]
SET BitField = 0;
UPDATE dbo.[ChangeTrack_Fix_A]
SET BitField = 0;
END
SET @TargetHolder = (SELECT MAX(commit_ts) from sys.dm_tran_commit_table);
END
END
END
Code to measure your speed and estimated completion time:
DECLARE @CTCurrentVerHolder INT
,@CTPerMin Decimal(10,2)
,@RunTime_InMin INT = 2
SELECT @CTCurrentVerHolder= CHANGE_TRACKING_CURRENT_VERSION()
DECLARE @timer NVARCHAR(10)='00:0'+CAST(@RunTime_InMin AS NVARCHAR(1))+':00'
WAITFOR DELAY @timer
SELECT (SELECT MAX(commit_ts) from sys.dm_tran_commit_table) TARGET_CT_VERSION
,CHANGE_TRACKING_CURRENT_VERSION() AS [CHANGE_TRACKING_CURRENT_VERSION]
,(CHANGE_TRACKING_CURRENT_VERSION()-@CTCurrentVerHolder)/(@RunTime_InMin*1.0) AS CT_Increments_Per_Minute
,(((SELECT MAX(commit_ts) from sys.dm_tran_commit_table)-CHANGE_TRACKING_CURRENT_VERSION())/((CHANGE_TRACKING_CURRENT_VERSION()-@CTCurrentVerHolder)/(@RunTime_InMin*1.0)))/60.0 HrsLeft
,DATEADD(minute,(((SELECT MAX(commit_ts) from sys.dm_tran_commit_table)-CHANGE_TRACKING_CURRENT_VERSION())/((CHANGE_TRACKING_CURRENT_VERSION()-@CTCurrentVerHolder)/(@RunTime_InMin*1.0))),GETDATE()) EstCompletion
Experimental advice:
I had an idea halfway through the change tracking incrementing (about 5hrs of the 10hrs it took) that would definately be worth trying if you haven't started on any other path. We had already chosen our horse and we were riding it as hard as we could (the above bulk force method) - the manager wouldn't allow it to be tried or even tested on another machine.
- Disabling change tracking on all the change tracked tables
- Disabling change tracking on the database (Sets CHANGE_TRACKING_CURRENT_VERSION() to NULL)
- Renable change tracking on the database
- Renable change tracking on the tables you want tracked
(code to script out the drop and add of the change tracking on the tables follows:)
I think the change tracking feature might have picked up the correct number from the internal sys table when CT was re-enabled.
No guarantee here. PLEASE test it somewhere non-production. But if you haven't started anything, it might be a very quick fix compared to full restores or forcing millions of updates into a mirrored database cave-man-style.
BE CAREFUL HERE! You could drop the change tracking, then close the below results and not know which tables needed change tracking... ack.
Code to script out all the change tracked tables - gives enable and disable commands. Leave the results "up" so you can copy/paste and renable everything after you're done.
SELECT
QUOTENAME(SCHEMA_NAME(O.schema_id))+'.'+QUOTENAME(OBJECT_NAME(CTT.object_id)) ObjectName
,'ALTER TABLE '
+ QUOTENAME(SCHEMA_NAME(O.schema_id))+'.'+QUOTENAME(OBJECT_NAME(CTT.object_id))
+ 'DISABLE CHANGE_TRACKING;'AS DROPCT
,'ALTER TABLE '
+ QUOTENAME(SCHEMA_NAME(O.schema_id))+'.'+QUOTENAME(OBJECT_NAME(CTT.object_id))
+ 'ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = '
+ CASE is_track_columns_updated_on
WHEN 0 THEN 'OFF'
ELSE 'ON'
END +
');'
AS ADDCT
,*
FROM sys.change_tracking_tables CTT
INNER JOIN sys.objects O
ON CTT.object_id = O.object_id
ORDER BY QUOTENAME(SCHEMA_NAME(O.schema_id))+'.'+QUOTENAME(OBJECT_NAME(CTT.object_id))
Here's to nobody else running into this particular nightmare - at least not in a 24/7 system!
June 13, 2012 at 1:53 pm
I realize you posted quite a bit of information above. I was wondering if you might consider writing this as an article. You can expand on some of the things you had to do as well as on the various alternatives.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply