Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Had to reindex again
Message
From
15/04/2010 22:46:07
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
Other
Title:
Had to reindex again
Environment versions
SQL Server:
SQL Server 2008
Application:
Web
Miscellaneous
Thread ID:
01460466
Message ID:
01460466
Views:
103
This is the second time it happens in less than a week. Tonight, an index started to not be used anymore. So, basically, the process moved up to 5 seconds instead of just a fraction of a second to be executed. To resolve the issue, I had to drop the index and reindex again. This is what I use for creating it:
DECLARE @indexName nvarchar(128)
DECLARE @dropIndexSql nvarchar(4000)
DECLARE @Constraint_Name nvarchar(128)
DECLARE @SQL varchar(4000)
DECLARE @Message varchar(400)

Raiserror('Creating index combo IndexCombo1...',0,1) With Nowait

BEGIN TRY
   IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE Name='Reply')
      ALTER DATABASE UniversalThread ADD FILEGROUP Reply
   IF NOT EXISTS (SELECT * FROM sysfiles WHERE Name='Reply')
      ALTER DATABASE UniversalThread ADD FILE (Name='Reply',FileName='d:\Data\Universal Thread\Reply.ndf') To FileGroup Reply
   CREATE INDEX [IndexCombo1] ON [Reply] ([NoMember]) INCLUDE ([NoThread]) ON [Reply]
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index combo IndexCombo1...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index combo IndexCombo2...',0,1) With Nowait

BEGIN TRY
   IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE Name='Reply')
      ALTER DATABASE UniversalThread ADD FILEGROUP Reply
   IF NOT EXISTS (SELECT * FROM sysfiles WHERE Name='Reply')
      ALTER DATABASE UniversalThread ADD FILE (Name='Reply',FileName='d:\Data\Universal Thread\Reply.ndf') To FileGroup Reply
   CREATE INDEX [IndexCombo2] ON [Reply] ([NoMember],[Read]) INCLUDE ([NoThread]) ON [Reply]
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index combo IndexCombo2...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH
There has to be something that caused this. I cannot imagine that SQL Server would have a bug in there.
Michel Fournier
Level Extreme Inc.
Designer, architect, owner of the Level Extreme Platform
Subscribe to the site at https://www.levelextreme.com/Home/DataEntry?Activator=55&NoStore=303
Subscription benefits https://www.levelextreme.com/Home/ViewPage?Activator=7&ID=52
Next
Reply
Map
View

Click here to load this message in the networking platform