dbcc reindex hangs

[kodaksmile]

Hi all,

We have a script that we are running on MSSQL 7 to reindex tables with a certain fragmentation. It has been running fine for a year or more. We had one weekend where we hit a conflict because someone forgot to reschedule the sp_updatestats and both tried to run at the same time. Ever since that we have one table that just seems to hang on the reindex. Any suggestions?

Script is below:

USE database1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempTable]
GO

CREATE TABLE [dbo].[tempTable] (
[tempTable] [varchar] (255) NULL
)

DECLARE @tName varchar(45), @nIndex int
DECLARE @dateStamp varchar(20)
DECLARE @dateHour int, @dateDayCurrent int, @dateStartDay int
DECLARE c_table CURSOR -- Create cursor to loop through table

FOR SELECT * FROM tempTable
INSERT INTO tempTable (tempTable)

select TableName from DataHolder ORDER BY ScanDensity ASC --where ScanDensity < 61 or LogicalScanFragmentation > 40

OPEN c_table
Fetch c_table into @tName

SELECT @dateStartDay = DATEPART(day, GETDATE())
-- get the current day assuming starting this reindex after batch
-- and add 1 day that so we know when we have to stop the reindex.
set @dateStartDay = @dateStartDay + 1


while (@@FETCH_STATUS=0) begin
-- insert if statement to check for the time and if less than the specified time continue with reindex
-- time check
SELECT @dateDayCurrent = DATEPART(day, GETDATE())
SELECT @dateHour = DATEPART(hour, GETDATE())
if (@dateStartDay = @dateDayCurrent) and (@dateHour > 4)
Goto BREAK_OUT
SET @dateStamp = Convert(Char(20),GETDATE())
fetch c_table into @tName
print @tName
SELECT @nIndex = (select TableIdIndex from DataHolder where TableName = @tName)
print @nIndex
print @dateStamp
dbcc dbreindex(@tName,' ',100)
SET @dateStamp = Convert(Char(20),GETDATE())
print @dateStamp
end

BREAK_OUT:
print 'This is the end'
print @dateStartDay
print @dateDayCurrent
print @dateHour
select * from tempTable
deallocate c_table
drop table tempTable
--drop table DataHolder





Happy Holidays!

 

[ptheriault]

Try running DBCC CHECKTABLE() to see if there are any problems with that table.

- Paul
- Database performance looks fine, it must be the Network!

 

[kodaksmile]

I will try that during off time. We were able to run the DBCC Reindex against a backup copy that we restored onto another server (to eliminate any type of data related issue - also because we can run that more frequently our production maintenance window is small)

 

[ptheriault]

Because you can run it on a backup copy It sounds like you have currupted an index. CHECKTABLE will show you that. If it is currupted drop the bad index an try recreating it.

- Paul
- Database performance looks fine, it must be the Network!

 

[kodaksmile]

Hope everyone had a happy holiday!

Well the bad news is I ran the DBCC CHECKTABLE ('MyTable') and the results came back normal. Any other suggestions?

 

[ptheriault]

Did you also run CHECKDB? If not run CHECKDB to look for page errors in the DB.

- Paul
- Database performance looks fine, it must be the Network!