DB locking up when adding indexes programmatically

I've got this big giant script that drops all my default constraints, drops
all indexes, then drops all clustered index constraints, then adds alot of
new indexes, constraints, and clustered indexes. I've got it running
without throwing any errors now. However, sometimes the database locks up.
It seems to be set to single user mode but it's not really. Often times one
user can still use the database but everybody else gets their Enterprise
Manager locked up if they try to do anything with that database. Even Cold
Fusion queries time out. During this time we can't even see the current
activity. If we close out the window in Query Analyzer that I used to run
it, it will warn me that there are uncommitted transactions. Restarting the
SQL Server service will fix the problem. When it's back up, I can see that
everything got dropped, but no indexes or anything was added. I have only
done this to test databases so far but I have 140 customer databases I have
to run this on and I cannot do that at this point due to the lockups. I
thought it was due to us having a failover cluster but yesterday I got it to
happen on a development server that is not clustered. Any ideas?
Thanks in advance,
CoryHi
A clustered index build requires a table lock in SQL Server 2000.
During this time, nobody can acces that table. It also needs disk space, so
it might need to grow the DB.
Run sp_who2 whilst the script is running to see what is happening.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Cory Harrison" wrote:

> I've got this big giant script that drops all my default constraints, drop
s
> all indexes, then drops all clustered index constraints, then adds alot of
> new indexes, constraints, and clustered indexes. I've got it running
> without throwing any errors now. However, sometimes the database locks up
.
> It seems to be set to single user mode but it's not really. Often times o
ne
> user can still use the database but everybody else gets their Enterprise
> Manager locked up if they try to do anything with that database. Even Col
d
> Fusion queries time out. During this time we can't even see the current
> activity. If we close out the window in Query Analyzer that I used to run
> it, it will warn me that there are uncommitted transactions. Restarting t
he
> SQL Server service will fix the problem. When it's back up, I can see tha
t
> everything got dropped, but no indexes or anything was added. I have only
> done this to test databases so far but I have 140 customer databases I hav
e
> to run this on and I cannot do that at this point due to the lockups. I
> thought it was due to us having a failover cluster but yesterday I got it
to
> happen on a development server that is not clustered. Any ideas?
>
> Thanks in advance,
> Cory
>
>|||If the table(s) is(are) big then Dropping and Re-creating is going to be
pretty resource intensive during which time it is going
to freeze the server. You might want to look at improving the performance of
the Index Creation process.
Did you try the SORT IN TEMPDB option ? This might help a bit though not to
a large extent.
Also, I hope you are Dropping the Non-Clustered Index before Dropping
Clustered Index and Create Clustered Index
before creating the Non-Clustered Index.
Gopi
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%238yru4LTFHA.336@.TK2MSFTNGP09.phx.gbl...
> I've got this big giant script that drops all my default constraints,
> drops all indexes, then drops all clustered index constraints, then adds
> alot of new indexes, constraints, and clustered indexes. I've got it
> running without throwing any errors now. However, sometimes the database
> locks up. It seems to be set to single user mode but it's not really.
> Often times one user can still use the database but everybody else gets
> their Enterprise Manager locked up if they try to do anything with that
> database. Even Cold Fusion queries time out. During this time we can't
> even see the current activity. If we close out the window in Query
> Analyzer that I used to run it, it will warn me that there are uncommitted
> transactions. Restarting the SQL Server service will fix the problem.
> When it's back up, I can see that everything got dropped, but no indexes
> or anything was added. I have only done this to test databases so far but
> I have 140 customer databases I have to run this on and I cannot do that
> at this point due to the lockups. I thought it was due to us having a
> failover cluster but yesterday I got it to happen on a development server
> that is not clustered. Any ideas?
>
> Thanks in advance,
> Cory
>
>