SQL Compare does not detect disabled indexes

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

SQL Compare does not detect disabled indexes

Postby shawnC » Thu Nov 08, 2012 10:18 am

Hi


tl;dr: SQL Compare does not detect disabled indexes

More...

I was playing with a bulk load script for test data.
In this, I disable constraints (CHECK and FK) and triggers.

However I wanted to disable some indexes temporarily. It failed and I needed to reset the database schema.

I thought I re-enabled all indexes (and constraints and triggers) and this appeared to be all OK when I used SQL Compare.
However, my overnight maintenance jobs failed because an index on an indexed view was still disabled. I had used sp_MSForEachTable which skipped the view

Repro script:

Code: Select all
/*
Run on 2 databasee
*/
DROP VIEW dbo.FooView; DROP TABLE dbo.Foo;
GO
CREATE TABLE dbo.Foo (FooID int NOT NULL PRIMARY KEY, Other char(20) NOT NULL CHECK (Other <> 'Seven'));
GO
INSERT dbo.Foo VALUES (1, 'one'),(2, 'two'),(3, 'three'),(4, 'four'),(5, 'five'),(6, 'six');
GO
CREATE NONCLUSTERED INDEX IX_Other ON dbo.Foo (Other);
GO
CREATE TRIGGER TRG_Foo ON dbo.Foo FOR DELETE AS SET NOCOUNT ON;
GO
CREATE VIEW dbo.FooView
WITH SCHEMABINDING
AS
SELECT F.FooID, F.Other FROM dbo.Foo F WHERE FooID >= 4;
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_PK ON dbo.FooView (FooID);
GO
SELECT * FROM dbo.Foo;SELECT * FROM dbo.FooView;
GO


/*
A SQL Compare now shows PK and UQ have different system generated name

Now disable stuff in one database only
*/


GO
SELECT * FROM sys.indexes WHERE sys.indexes.is_disabled = 1;SELECT * FROM sys.check_constraints CC WHERE CC.is_not_trusted = 1;SELECT * FROM sys.triggers T WHERE T.is_disabled = 1;
GO
ALTER INDEX ALL ON dbo.Foo DISABLE;
ALTER TABLE dbo.Foo NOCHECK CONSTRAINT ALL;
ALTER TABLE dbo.Foo DISABLE TRIGGER ALL;
GO
ALTER INDEX IXCU_PK ON dbo.FooView DISABLE;
GO
SELECT * FROM sys.indexes WHERE sys.indexes.is_disabled = 1;SELECT * FROM sys.check_constraints CC WHERE CC.is_not_trusted = 1;SELECT * FROM sys.triggers T WHERE T.is_disabled = 1;
GO

/*
A SQL Compare now does not show differences in the indexes (expected: 2 on dbo.Foo, 1 on dbo.FooView).. but it does show the CHECK constraint differences
*/

DROP VIEW dbo.FooView; DROP TABLE dbo.Foo;
GO
shawnC
 
Posts: 13
Joined: Tue Nov 22, 2011 1:53 pm

Postby Brian Donahue » Mon Nov 12, 2012 12:28 pm

I could not reproduce your issue because I ran across another bug. But SQL Compare does recognize that the constraint needs to be enabled/disabled - it's just that the command fails because SQL Compare is not respecting the "ignore system-generated names" option when enabling/disabling constraints.

However, this would explain the problem if you did not see the error message when the sync script ran and just went by the end-result, which was that the constraint was not disabled or re-enabled (depending on the direction you ran the sync).

I filed a bug SC-6104 about this, but not with your problem description (change not detected) but rather that the system-generated name is not ignored; the difference is you can visually confirm SQL Compare finds the difference, it is just not synchronized correctly.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby shawnC » Wed Nov 14, 2012 8:53 am

Thanks Brian

My reported bug is about disabled indexes (sys.indexes.is_disabled), not constraints as such

Obviously a unique constraint and a primary key are indexes, but explicit indexes (CREATE INDEX) that are disabled are not detected: neither are indexes (from constraints) from CREATE TABLE

So, neither the PK nor IX_Other or IXCU_PK are detected. Only the table PK has a system generated name, the others are explicitly named
shawnC
 
Posts: 13
Joined: Tue Nov 22, 2011 1:53 pm

Postby shawnC » Mon Nov 19, 2012 11:46 am

any update please?
shawnC
 
Posts: 13
Joined: Tue Nov 22, 2011 1:53 pm

Postby shawnC » Thu Nov 14, 2013 12:29 pm

One year later... any update please?
shawnC
 
Posts: 13
Joined: Tue Nov 22, 2011 1:53 pm

Postby mjswart » Thu May 01, 2014 6:56 pm

I can reproduce this too.
http://www.red-gate.com/MessageBoard/vi ... hp?t=20479

[edit] Actually, my reproduction was about disabled constraints, not disabled indexes. But my symptoms seem similar to these symptoms.
mjswart
 
Posts: 3
Joined: Wed Apr 30, 2014 3:30 pm

Postby shawnC » Tue May 06, 2014 7:54 am

They don't bother fixing much these days.

I'm going right off SQL Compare because of continual low level unfixed bugs.
shawnC
 
Posts: 13
Joined: Tue Nov 22, 2011 1:53 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests