Lessons that needs to be known when executing scripts on Production Server

Hi Friends,

This post is something as a DBA we need to be cautious of.  For Every production release we usually get scripts from development that needs to be executed. Frankly speaking we will not check in depth of what is being written inside them and it will be even harder if we don't have Test Environment to estimate the results.

Coming back to the issue as part of release the developer had to change a huge table from NULL to NOT NULL and he used the general syntax of 

alter table dbo.TEST alter column TEST_ID bigInt not null;

This might look pretty simple but internally SQL Server will need to check all the existing values to ensure no nulls are present. It will then physically rewrite every row of the table. As well as being slow in itself, these actions generate a great deal of transaction log, which can have knock-on effects.

My Environment is involved in ALWAYS ON and it has 6 replicas the log file size which was initially at 2 GB started growing and it has grown up to 330 GB and still the operation has not been completed .

We have 3 replicas in one Data center and the other 3 in remote data center. To transfer this size of more than 300 GB just imagine how much time would it take and during this time we can't shrink the log file and also if for some reason if any outage happens then we will be in Disastrous situation.

So I feel it is the duty of Developers and DBA's to engage and discuss before doing them on Production servers.

Now what is the solution to carry this operation . Well there are many ways to achieve this but I opted for the below method

1)  Remove the Database from Always ON (I know it's painful)
2)  Change the Recovery model from Full to Simple
3)  Create a new table by specifying the col as NOT NULL
4)  Populate the rows from Older to New table
5)  Rename the table.

Important points to keep in Mind: Even though we change the recovery model to simple this being Atomic operation it will consume more log space.

To make this operation to perform as Minimal logged we need to make use of TABLOCK Hint

The same operation which took more than 300 GB in Full recovery model has took exactly 1 GB of Log file Size. Just imagine the variation in size. 

And the insert into <NEWtablename>  with(tablock) select * from OLDTABLENAME just took 18 minutes whereas the command (Alter table) was running for more than 4 hours.

Also if we have any constraints we need to create them back as like the original table. I believe there are 2 options here one is drop and recreate them and the other disabling and enabling of them (which I have not tested ) . Former option took 8 minutes as it has to check few conditions.

As DBA we should be aware of change process as well. Is it not?

Recovery Model

Initial Size

Final Size



200 MB

235 GB



200 MB

235 GB



200 MB

1200 MB


We need to understand that even though you run in simple recovery model it will not help us in achieving minimal logging unless we use tablock hint as I am running on SQL Server 2016. 

For Earlier versions of SQL We need to use some other trace flags along with Tablock hint.