Sql Server issue

Question

I hope this is not off-topic, but I have a real problem that I oculd use some advice on.

I have an application that upgrades its own Sql Server database (from previous versions) on startup. Normally this works well, but a new version has to alter several nvarchar column widths.

On live databases with large amount of data in the table this is taking a very long time. There appear to be two problems - one is that Sql Server seems to be processing the data (possibly rewriting it), even though that isn't actually being changed, and the other is that the transaction log gobbles up a huge amount of space.

Is there any way to circumvent this issue? It's only a plain Alter Table... Alter Column command, changing nvarchar(x) to nvarchar(x+n), nothing fancy, but it is causing an 'issue' and much dissatisfaction in the field. If there was a way of changing the column width without processing the existing data, and somehow suppressing the transaction log stuff, that would be handy.

It doesn't seem to be a problem with Oracle databases.

An example command:

IF EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE table_name='ResourceBookings' AND column_name = ('ResourceBookerKey1') AND character_maximum_length <= 50)
    ALTER TABLE [ResourceBookings] ALTER COLUMN [ResourceBookerKey1] NVARCHAR(80) NULL

As you can see, the table is only changed if the column width needs to be increased

TIA

Answer 1

Before upgrading, make sure the SQL Server database's Recovery Model is set to "Simple". Go to SSMS, right-click the database, select properties, and then click on the Options pages. Record the "Recovery Mode" value. Set the Recovery Model to "Simple", if it's not already (I assume it's set to FULL).

Then run the upgrade. After the upgrade, you can restore the value back to what it was.

Alternately you can script it with something like this:

Before upgrade:

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE;

After upgrade:

ALTER DATABASE MyDatabase SET RECOVERY FULL;