Running Index causing machine to halt

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Running Index causing machine to halt

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2004-06-24 : 08:45:36

Does anyone have a recommendation on what could be happening when trying to run an index on a talble my machine freezes up and needs rebooted without ever finishing the index. The table consists of 250K rows. Machine has 2gb of mem.

Anyone run into this before?

Thanks.

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-24 : 08:50:52

Did you run

DBCC CHECKDB

first?

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-24 : 08:51:24

Try

DBCC REINDEX?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-24 : 12:27:50

It's DBCC DBREINDEX. But what do you mean run an index? Do you mean create an index? Creating indexes can take a long time depending upon how much data there is or whether you've select non-clusterd or clustered. Since you've got 250k, it might take a few minutes to do. Which edition of SQL Server is this and what does your hardware look like? Is this on a personal machine? How long do you wait before the reboot?

Tara

bogey
Posting Yak Master

166 Posts

Posted - 2004-06-24 : 13:31:22

SQL Server Standard Edition, Dual processors, 2 gb of memory running NT 5.0 with sp3. I would wait all night and it still would not finish. After a few minutes the keyboard would lock up. Here is the code:

USE [PittMedical]
go

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
go

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE CLUSTERED INDEX [AllSixYears1] ON [dbo].[AllSixYears] ([LAST_NAME] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

CREATE NONCLUSTERED INDEX [AllSixYears2] ON [dbo].[AllSixYears] ([AWARD_ID] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

What I finally had to do dump the data, drop then recreate the table with the appropriate indexes, then load the data back into the table. I'm still puzzled why the machine would lock up? Hmmmmm

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-24 : 20:03:27

Well, you'll probably never know now. You could have tried doing a DBCC CHECKTABLE and dropping and creating the existing indexes. Hopefully, it won't happen again.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

bogey
Posting Yak Master

166 Posts

Posted - 2004-06-29 : 10:11:42

I'd like to thank everyone for their input on this and would like to add one final note to which feedback would be great. I just found out that the machine that I've been having probs with has Veritis Backup Exec which is using MSDE. Could this be a problem having MSDE and Sql Server 2000 sp3 running on the same machine?

Again thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources