Red Gate forums :: View topic - Index is being dropped and recreated for no reason
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

Index is being dropped and recreated for no reason

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
JackAce



Joined: 08 Jul 2011
Posts: 45

PostPosted: Thu Nov 17, 2011 1:20 am    Post subject: Index is being dropped and recreated for no reason Reply with quote

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:

<?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>
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6640

PostPosted: Mon Nov 21, 2011 12:47 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group