# Sql-server – Performing large amounts of schema updates and transaction log issues

sql server

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

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:
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.