Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2007-09-27 : 13:47:42
|
| Hi:I have issued the following ALTER TABLE CHECK ADD CONSTRAINT on a table which has around 100K rows and it is taking long time (it's been more than 30 mins the alter table is running) to add the constraint. Is this normal or should I kill the process.ALTER TABLE [dbo].[tblAbsHeqAnalyticOutputSimulationPathValues] WITH CHECK ADD CONSTRAINT [CK_tblAbsHeqAnalyticOutputSimulationPathValues_1] CHECK ([dbo].[svfConstraintVerifyTableUniqueActiveEntryFacade]('tblAbsHeqAnalyticOutputSimulationPathValues')<=(1) AND [dbo].[svfConstraintVerifyTableUniqueActiveEntryFacade]('tblAbsHeqAnalyticOutputSimulationPathValues')>=(0))Thanks ! |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 14:01:39
|
"should I kill the process"If you do it will rollback, which could take as long again to "undo"  Actually, typing that I'm not sure there is anything to "undo", presumably SQL Server is just ploughing through the data checking that the constraint is not violated by any existing data.Could you use WITH NOCHECK instead? (checking by some other query that there are no violations, or maybe you know that already [allowing for any new records which could be being added and violate the constraint])Kristen |
 |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-27 : 14:04:14
|
| Could there be something going on that's blocking it? |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-09-27 : 14:05:37
|
| There is absolutely no blocking. It is still going on. It's been almost 1 hour now... |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-27 : 14:10:04
|
| Did you run this from Enterprise Manager or Query Analyzer? |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-09-27 : 14:13:43
|
| Using Query analyzer... |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-27 : 14:14:29
|
| Not sure why it would take that long then on only 100k rows. |
|
|
|
evjo
Starting Member
20 Posts |
Posted - 2007-09-29 : 22:53:36
|
| How is the CPU / IO in the activity monitor? It could also be a wait because of resource shortages on the server.Whenever I see an old lady slip and fall on a wet sidewalk, my first instinct is to laugh. But then I think, what if I was an ant, and she fell on me. Then it wouldn't seem quite so funny. - Jack Handey |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-30 : 05:23:14
|
How long did it take then?  |
|
|
|
|
|
|
ADD CONSTRAINT TAKING LONG TI