We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL Rebuild Index Task

Medium Priority
1,294 Views
Last Modified: 2013-11-30
Hi,

I have an SQL Rebuild Index task which fails to complete. It ran fine for well over a year but for some reason it now runs but doesn't seem to finish.

I have run all the alter statements individually on a restored test database and all semed to run fine. I ran them individually on the live sytem and eventually I came accross statements which didn't finish.

There are no errors in the event viewer or in the SQL Logs mainly I think because the job isn't actually failing.

Any thoughts on what may be casuing this or ideas on any troubleshooting steps would be much appreciated.

Grant
Comment
Watch Question

SvenTech Lead Web-Development

Commented:
Post the statements you are running. Maybe you are trying to delete an index that is not existing or you are creating an index that is allready present.

You could use DBCC Reindex to reorganize your indexes:
http://msdn.microsoft.com/en-us/library/aa258828(SQL.80).aspx
SvenTech Lead Web-Development

Commented:
DBCC DBREINDEX is the correct one ;)
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Rebuild Index requires sufficient free space in both data files and in the disk.
Do you have necessary free space in your machine and data file.
What about your Fill Factor in your database?
even it might affect this.

Author

Commented:
Thanks replying guys. One of the queries that isn't completing is.

ALTER INDEX [IX_DOCCCHE_DOCVER] ON [MHGROUP].[DOCCACHE] REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

I don't think there not being sufficient free space is the issue. The drive has over 50gb free on it and the data and log files are both set for unrestricted growth but it is definitely something I will look at.

SvenTech Lead Web-Development

Commented:
What is the error message you are getting when running through query analyzer?

Instead of rebuilding each index for a table you could use DBCC DBREINDEX as I stated and linked above.

Author

Commented:
I don't receive any error messages, the query attempts to execute and then doesn't complete or fail.
SvenTech Lead Web-Development

Commented:
On failure it will throw out an exception or error message. On complete it will state that it was successfull. Please run query analyzer with results as text.

Author

Commented:
It's a strange one but the job will only stop running when I manually cancel it. Hence,no error messages.
SvenTech Lead Web-Development

Commented:
Why cancel it? Depending on size of the index and rows in table the task could take some time. And if there are still connections to the database it will take even longer.

If you cancel the task it will not rebuild the index or rollback the task withou giving you an error message.

Author

Commented:
The full rebuild index task used to complete in approx 1hr 15mins. It then started having issues and after it had been running for over 4hrs I had to cancel it to allow users access to our application. When I ran the query above it executed for over an 1 hour and 30mins before I had to cancel it.

The problem is there just aren't enough hours in the day ! I am guessing there is no way to see how far the job is getting through ?
CERTIFIED EXPERT
Top Expert 2012

Commented:
And the big question:  Why are you periodically rebuilding the indexes?  There simply is no need for that.
SvenTech Lead Web-Development

Commented:
This issue could only be solved with larger amount of RAM or quicker harddrives, if it can be solved. Rebuilding an index takes the time it takes. If you have plenty of rows and unique values it takes time.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
At the simplest level, indexes are rebuilt by constructing a new copy of the index and then deleting the old one. This means that essentially there are two copies of the index for a short period of time. Constructing the new index could require as much database file space as the original index, and if the rebuild requires a sort operation, then an additional 20 percent of the index size is required for the sort.
So the worst case is that rebuilding an index requires 1.2 times the space of the old index. If the database file does not have enough free space, then the file will have to grow as the operation proceeds. It's possible that if autogrow is not enabled or there is not enough space on the disk volume, then there may not be enough free space available and the rebuild operation will fail.

Whether the operation fails or not, the extra space allocated to the database file is not freed up after the rebuild operation completes.

Using DBCC INDEXDEFRAG (or ALTER INDEX ... REORGANIZE in SQL Server 2005) has the advantage that it uses almost no additional database file space, but it can take longer and generate a lot more transaction logging than an index rebuild.

The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:

Script referred from
http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx
--Script to automatically reindex all tables in a database
 
USE DatabaseName --Enter the name of the database you want to reindex
 
DECLARE @TableName varchar(255)
 
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
 
OPEN TableCursor
 
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
 
CLOSE TableCursor
 
DEALLOCATE TableCursor

Open in new window

Author

Commented:
Thanks to everyone for replying and to get back to you all.

acperkins: To reduce fragmentation on the Indexes and hopefully avoid any sql performance issues.

DarthSonic:  I have 16GB of RAM with 4 dual-core processors in the server, surely this should be enough. The application itself seems to be running fine.

rrjegan17: I like this and I can see this as a potential solution. I'll do a bit of testing and see what I find.

Again thanks guys, much appreciated.


CERTIFIED EXPERT
Top Expert 2012

Commented:
>>To reduce fragmentation on the Indexes and hopefully avoid any sql performance issues.<<
I am afraid that is not a good answer (as you are discovering).
Commented:
This is how I resolved it. I ran the script below, this will create a stored procedure called tksp_defrag. I ran exec tksp_defrag from a SQL Query Analyser window.  After this I ran the rebuild index task which completed successfully.

use  [YourDatabaseNameHere]
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tksp_defrag]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[tksp_defrag]
go
create proc [dbo].[tksp_defrag]
      @fillfactor varchar(10) ='100',
      @clip int=10
as
BEGIN
DECLARE @dbid int,
            @ObjectName varchar(255),
            @objectid int,      
            @IndexName      varchar(255),
            @indexid int,
            @execstr      varchar(8000)
            
set nocount on
create table #outstr
            (
            fwid int identity not null,      
            outstr varchar(8000) null
            )
DECLARE Indexcursor
      CURSOR FAST_FORWARD
FOR
SELECT
      schema_name(o.schema_id)+'.'+object_name(o.object_id) as objectname,
      i.name as indexname,
      db_id() as dbid,
      o.object_id as objectid,
      i.index_id as indexid
from
      sys.indexes i
inner join
      sys.objects o
on
(
      i.object_id=o.object_id
)
where
      o.type='U'  and
      i.index_id>0 -- No heaps
order by
      o.object_id,
      i.index_id


OPEN Indexcursor

FETCH NEXT FROM Indexcursor INTO @ObjectName,@IndexName,@dbid,@objectid,@indexid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr='Initial analysis of fragmentation of '''+@ObjectName+''','''+@IndexName+''' shows:'
INSERT INTO
      #outstr (outstr)
      SELECT @execstr
INSERT INTO #outstr
SELECT convert(varchar(12),convert(decimal(16,2),avg_fragmentation_in_percent))+'%'  
      FROM sys.dm_db_index_physical_stats
    (@dbid, @objectid, @indexid, NULL , 'LIMITED')
if not exists (
SELECT convert(varchar(12),convert(decimal(16,2),avg_fragmentation_in_percent))+'%'  FROM sys.dm_db_index_physical_stats
    (@dbid, @objectid, @indexid, NULL , 'LIMITED') where avg_fragmentation_in_percent>@clip)
BEGIN
SELECT @execstr='Defragmentation of ('''+@ObjectName+''','''+@IndexName+''') not necessary as below '+convert(varchar(12),@clip)+'%'
INSERT INTO #outstr (outstr) SELECT @execstr
END
if exists (
SELECT convert(varchar(12),convert(decimal(16,2),avg_fragmentation_in_percent))+'%'  FROM sys.dm_db_index_physical_stats
    (@dbid, @objectid, @indexid, NULL , 'LIMITED') where avg_fragmentation_in_percent>@clip)
BEGIN
SELECT @execstr ='ALTER INDEX '+@IndexName+' on '+@ObjectName+'
REBUILD WITH (FILLFACTOR='+@FillFactor+',MAXDOP=1)'
exec (@execstr)
SELECT @execstr='Remaing fragmentation of  ('''+@ObjectName+''','''+@IndexName+''') is:'
INSERT INTO #outstr SELECT @execstr
INSERT INTO #outstr
SELECT convert(varchar(12),convert(decimal(16,2),avg_fragmentation_in_percent))+'%'  FROM sys.dm_db_index_physical_stats
    (@dbid, @objectid, @indexid, NULL , 'LIMITED')

END
FETCH NEXT FROM Indexcursor INTO @ObjectName,@IndexName,@dbid,@objectid,@indexid
END

So far so good.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>This is how I resolved it<<
And I think you are approaching it from the wrong angle, but more power to you.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.