November 14, 2012 at 2:30 pm
Hi all,
This is my issue:
--128593056 records in BusinessProcesses, table size 85GB
--Task:
--Changing OrderingAccount from varchar(30) not null to varchar(35) not null
alter table BusinessProcesses disable trigger all
alter table BusinessProcesses nocheck constraint all
--this is very fast
alter table BusinessProcesses alter column
OrderingAccount varchar(35)
go
--this does not execute
alter table BusinessProcesses alter column
OrderingAccount varchar(35) not null
go
--Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN OrderingAccount
--failed because one or more objects access this column.
--Only the index IBusinessProcessesOrderingAcc access this column
--I drop index IBusinessProcessesOrderingAcc and the alter table starts to execute, but the
--execution lasts too long
--The database is not used by anyone while altering, its log is shrunk before start, there is enough --space ...
/*
--recreate the index
alter table BusinessProcesses enable trigger all
alter table BusinessProcesses check constraint all
I checked the following two links:
http://blogs.msdn.com/b/sqlcat/archive/2006/03/01/541550.aspx
http://blogs.msdn.com/b/sqlcat/archive/2006/03/31/566046.aspx
where recommendation is to create a new table, fill it, ... and rename it,
but it is impossible for my case, because the table has over 10 indexes, over 10 constraints,
over 10 FKs ...
*/
Can anyone advise something or share an experience...
Thanks a lot in advance
IgorMi
Igor Micev,My blog: www.igormicev.com
November 14, 2012 at 3:01 pm
Is OrderingAccount a foreign key in another table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2012 at 3:02 pm
No it is not. It is a varchar column only.
Igor Micev,My blog: www.igormicev.com
November 14, 2012 at 3:28 pm
The difference is the "NOT NULL" constraint between the two statements.
The following statement converts the column to a nullable column (I ran it in a test scenario to confirm that the column indeed changes to allow nulls). SQL Server doesn't have to enforce the not null constraint so it is very fast.
alter table BusinessProcesses alter column
OrderingAccount varchar(35)
go
The following statement retains the NOT NULL so SQL Server has to enforce the NOT NULL constraint. WIth that many rows in the table, it can take quite some time to complete.
alter table BusinessProcesses alter column
OrderingAccount varchar(35) NOT NULL
go
Have you tried running it in a like environment with a copy of the database? That's the first thing I would look at.
November 14, 2012 at 3:37 pm
George M Parker (11/14/2012)
The difference is the "NOT NULL" constraint between the two statements.The following statement converts the column to a nullable column (I ran it in a test scenario to confirm that the column indeed changes to allow nulls). SQL Server doesn't have to enforce the not null constraint so it is very fast.
alter table BusinessProcesses alter column
OrderingAccount varchar(35)
go
The following statement retains the NOT NULL so SQL Server has to enforce the NOT NULL constraint. WIth that many rows in the table, it can take quite some time to complete.
alter table BusinessProcesses alter column
OrderingAccount varchar(35) NOT NULL
go
Have you tried running it in a like environment with a copy of the database? That's the first thing I would look at.
alter table BusinessProcesses alter column
OrderingAccount varchar(35)
go
This is not even necessary, i used it to just extend the length. The fail first occurs with NOT NULL. after dropping the index on the column it starts executing but so long. The problem is that the table is huge. I should try the approach by using temp table and then rename it ...but it also seems to be a long time consuming operation ...
Thanks
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply