3

So I have a VS2010 Database Project that I am deploying, with a few schema changes. I have one table in particular that the VSDBCMD insists on "rebuilding" i.e. rename->create->copy->drop

The only changes for this table are dropping some columns, which could be handled by the simply I dunno, dropping the columns. Normally I wouldn't mind, except this particular table is called "Attachments" and weighs in at 15 gigs or so. Which takes a long time, locks up the database and fails locally, as I don't have 15+ gigs free, and times out remotely in our testing environment.

Can anyone direct me to the rules VSDBCMD follows for changing the schema when it deploys?

Or perhaps you have experienced similar issues and have a suggestion?

Thanks!

Master Morality
  • 5,837
  • 6
  • 31
  • 43
  • It would be useful to get more details on your table structure. I have a simple test project and (with the _Block incremental deployment if data loss might occur_ option disabled) removing columns anywhere in the table simply generates an `ALTER TABLE _ DROP COLUMN _` statement (no table rebuild). – Tom Hunter Feb 27 '13 at 17:46

1 Answers1

1

VSDBCMD just 'likes' rebuilding tables too often, and I don't have the 'magic vsdbcmd manual' for when it chooses to rebuild a table unfortunately, but I don't trust the output of VSDBCMD on a production database anyway without manual checking first anyway.

There's a setting in the 'dbname.sqldeployment' file that allows the setting 'IgnoreColumnOrder' that might help prevent rebuilding the table (maybe it's triggering the rebuild because the column index has changed).

In your case I would just run a manually created script on your DB.

Heck, writing 'alter table Attachments drop column uselessData' would've probably cost you 10% of the time you put into asking this question in the first place :)

Wiebe Tijsma
  • 10,173
  • 5
  • 52
  • 68
  • 1
    I've tested the behaviour of the **IgnoreColumnOrder** option. It looks like this option is only effective when the project and the target tables have exactly the same column set (in this case if the order is different and the option is on, nothing will happen at deployment). If there are any new columns that are not being added to the end of the table, the table will be re-built (regardless of the option). – Tom Hunter Feb 27 '13 at 17:49
  • Yes it's a bit of a monster. Didn't get much better in the 2012 version, too much effort to get it running on the build server. Switched to FluentMigrator since and haven't looked back. http://github.com/schambers/fluentmigrator – Wiebe Tijsma Mar 04 '13 at 16:58