February 2, 2012 at 3:21 am
We have a production database which contains a nullable column which we would like to make non-nullable.
The table has 95 million rows and occupies 30GB on disk.
All NULLs have been eradicated in the column to be changed.
I decided to test the change in QA.
Here's the way I have scripted the change (I had to drop an index which referenced the column, apply the not-nullable change, then rebuild the index):
use <DBName>
go
if exists ( select *
from sys.indexes
where object_id = object_id('dbo.<Tablename>')
and name = 'IX_<index_name>' )
drop index [IX_<index_name>] on [dbo].<Tablename> with ( ONLINE = OFF )
GO
alter table dbo.<Tablename>
alter column <ColumnName> varchar(5) not null
go
create nonclustered index [IX_<index_name>] on [dbo].[<tablename>]
(
[<ColumnName1>] asc,
[<ColumnName>] asc, --this is the column whose definition is being changed
[<ColumnName2>] asc
)
include ( [<ColumnNam3>]) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) on [PRIMARY]
GO
This test highlighted the following issues:
1) While the column's definition is being changed, most processes which access the database are locked out. That's more than 30 minutes down-time.
2) The transaction log grew by more than 50 GB.
I'd be interested to hear any ideas about how to get this change applied with less impact (less downtime & less effect on the TLog). I've started thinking about building an exact copy of the existing table definition, but with the non-nullable column and then copying the data into that.
The hard bit happens when switching around table names / FK constraints and possibly other stuff when the data has been populated. Before I go down that avenue, I thought I'd get input from people here - thanks very much for any comments.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 2, 2012 at 10:21 am
Silence! Any ideas anyone?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 2, 2012 at 10:41 am
I'd recommend the second approach.
Use sp_rename for changing the names once done?
February 2, 2012 at 10:44 am
Hi Phil, if you're looking to minimise the down time I'd suggest partition switching
February 2, 2012 at 11:08 am
Thank you very much for the responses - I'll look into the partition switching idea.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 6, 2012 at 10:50 am
Partition Switching will fail as the Main and Switch Table should be identical(not just no. of columns but also their properties).
"sp_rename" is a safe option.
February 6, 2012 at 10:58 am
Thanks for the response. I got there in the end by a slightly devious method - I did an alter table with nocheck and added a Check constraint on the column (after fixing up any existing nulls).
Job done - in a flash.
Of course, any future developers who inherit this table will shrug their shoulders and wonder who the idiot was who did it that way :I)
I'll have to live with the guilt.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply