?
Solved

SQL Rebuild Index Task

Posted on 2009-02-10
17
Medium Priority
?
1,235 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
0
Comment
Question by:Shepwedd
  • 6
  • 6
  • 3
  • +1
17 Comments
 
LVL 11

Expert Comment

by:Sven
ID: 23599104
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
0
 
LVL 11

Expert Comment

by:Sven
ID: 23599112
DBCC DBREINDEX is the correct one ;)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23599138
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.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:Shepwedd
ID: 23599242
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.

0
 
LVL 11

Expert Comment

by:Sven
ID: 23599477
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.
0
 

Author Comment

by:Shepwedd
ID: 23600510
I don't receive any error messages, the query attempts to execute and then doesn't complete or fail.
0
 
LVL 11

Expert Comment

by:Sven
ID: 23600657
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.
0
 

Author Comment

by:Shepwedd
ID: 23600770
It's a strange one but the job will only stop running when I manually cancel it. Hence,no error messages.
0
 
LVL 11

Expert Comment

by:Sven
ID: 23601174
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.
0
 

Author Comment

by:Shepwedd
ID: 23602346
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 ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23603658
And the big question:  Why are you periodically rebuilding the indexes?  There simply is no need for that.
0
 
LVL 11

Expert Comment

by:Sven
ID: 23609035
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.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23609201
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

0
 

Author Comment

by:Shepwedd
ID: 23610646
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.


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23618193
>>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).
0
 

Accepted Solution

by:
Shepwedd earned 0 total points
ID: 23660367
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23666089
>>This is how I resolved it<<
And I think you are approaching it from the wrong angle, but more power to you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question