1

We have an issue with growing transaction logs hitting disk size limits

When ever we upgrade our software on a client's site. our upgrade installer runs a number of scripts to add columns to tables and move data around. once complete it will upgrade the web site that uses the db

The typical db can be of a moderate size 1GB - a few of the tables will contain 10's of millions of rows. nothing that should stress the server too much

We have very little control over our customers back up policy although we recommend full transactional logs backed up every 1hr. we cant guarentee this followed. And we have no control over when the customer runs the upgrade proccess.

Recently one of our scripts added a column to a table that typically has a large numbers of rows, 10 - 20 mil, the column had a default value so had to update all the rows.

For one customer this caused an already large transaction log to grow to the size of the disk. The script failed and at that point our installer stops

I have read a few articles regarding truncating or shrinking the log, running checkpoints , switching to simple mode.

I would like to avoid them needing to do any db maintenance before upgrading as most of them know little about it.

Does any one have a best practice to follow when running these kind of upgrade schema scripts.

The DB is MSSQL

Any help would be much appreciated

=========================== UPDATE 1==================== I will recommend the following steps for our upgrader

• Take DB backup before upgrading begins

• Start of the update scripts set the recovery mode to simple

• Before running an update that may cause the issue run a ‘checkpoint’ and then shrink the transaction log

• Run large updates in batches (10000 rows at a time) o For new columns with a default value create the column allowing nulls o Set the default value in batches o Add the default value and not null constraint after o Run a checkpoint between each batch so that log does not need to be kept (this will avoid the log growing too large) checkpoints run once a minute by default so may not be needed

• After all updates have been run set the recovery mode to the original state (full)

• Backup the database to restart the full log mode

  • 1. can your script run full backup and transaction log backup before you start running? – Liya Tansky May 23 '18 at 12:31
  • 2. I would consider adding a column with Null, setting the default value in batches and only then adding the constraint and setting the NOT NULL – Liya Tansky May 23 '18 at 12:32
  • Adding a new NOT NULL column with a default constraint and WITH VALUES is a meta-data only operation in Enterprise Edition, avoiding the excessive logging. – Dan Guzman May 23 '18 at 12:57
  • unfortunately we don't have control over the backups and the customers use anything from express to enterprise 2008 upwards. – Craig Walker May 23 '18 at 14:43
0

Starting with SQL Server 2012 adding of not NULL column with default values to an existing table is metadata only operation.

From what you wrote it's clear that it's not your case, i.e. or your server is beyond 2012, or one of the following probably is your case:

  • the newly added column increases the maximum possible row size over the 8060 bytes limit the column cannot be added online
  • your server Edition is not Enterprise

(you can find the complete list of restictions here: Online non-NULL with values column add in SQL Server 2012

So how you can resolve the issue? If you cannot upgrade to at least 2012 or your Edition is not Enterprise, the only thing I can suggest you is to split this one big transaction to many transactions. This will permit you to clear the log (on checkpoint in simple recovery model or on a log backup in full / bulk logged).

So instead of adding not null column first add null column and then update it with your values in batches. So the log will have the possibility to clear between batches.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.