Index is being dropped and recreated for no reason

Forum for users of SQL Compare schema synchronization utility

Moderators: JonathanWatts, Chris Auckland, David Atkinson

Index is being dropped and recreated for no reason

Postby JackAce » Thu Nov 17, 2011 1:20 am

We are currently using the SQLCompare.exe command line application to generate deployment scripts for our main database. We are also using SQL Source Control to version it.

After generating the synchronization script for our production deployment, I noticed that there is an index that is being dropped at the beginning of the script and then recreated at the end. The table is not being touched as part of the deployment. Why SQL Compare is dropping and recreating the index is a mystery to me.

We'd really prefer that the index not be dropped, since the table is quite large (around 3 million rows) and recreating the index adds about 10 minute to our deployment time.

Two questions:
* Can you tell me why the index might be dropped when the table is not changing at all?
* Is there a command line option that we can use to prevent the index from being dropped? We are using the /argfile parameter in SQLCompare to generate the script.

Below is a cleansed version of the argument file that we are passing to SQLCompare:
Code: Select all
<?xml version="1.0"?>
<commandline>
   <scripts1>RedGateScripts</scripts1>
   <server2>Server</server2>
   <database2>DB</database2>
   <include>Assembly</include>
   <include>Function</include>
   <include>StoredProcedure</include>
   <include>Table</include>
   <include>View</include>
   <include>UserDefinedType</include>
   <include>Contract</include>
   <include>MessageType</include>
   <include>Queue</include>
   <include>Service</include>
   <include>Additional</include>
   <include>Different</include>
   <include>Missing</include>
   <include>Identical</include>
   <options>IgnoreStatisticsNorecompute</options>
   <options>IgnoreConstraintNames</options>
   <options>IgnoreNotForReplication</options>
   <options>IgnoreQuotedIdentifiersAndAnsiNullSettings</options>
   <options>IgnoreUsersPermissionsAndRoleMemberships</options>
   <scriptfile>UpdateSchema_DB_Server.sql</scriptfile>
   <force/>
</commandline>
JackAce
 
Posts: 45
Joined: Fri Jul 08, 2011 11:00 pm

Postby Brian Donahue » Mon Nov 21, 2011 12:47 pm

If you need to know the reason why the index gets recreated, can you try running the same comparison in SQL Compare (the UI version), go through the process of synchronizing the databases, and on the last page of the sync wizard, look at the warnings tab. There may be some information there.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests

cron