Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

blocking remove Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 1:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 4, 2013 8:13 AM
Points: 190, Visits: 367
hi

i am adding new column with default value
this table has millions of rows, so while i am executing my query ,its causing blocking to other user.

how to solve this

example

alter table emp
add column senti_v int default 0

thanks
Post #1470870
Posted Monday, July 8, 2013 2:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 1,375, Visits: 2,663
Firstly, change the "add column" part to just "add".

Secondly, this kind of process will definitely take exclusive locks on the table which will block other processes.

I suggest that you run this process during a very quiet time or during a downtime period.

Regards
Post #1471081
Posted Thursday, July 11, 2013 12:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:41 AM
Points: 197, Visits: 729
You could add the column without a default value, that usually works quite quick.
Afterwards you could update the column in chunks of for example 1 Mio rows.
Post #1472733
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse