<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server Newbies
»
Unable to Rebuild or Reorganize an Index
Unable to Rebuild or Reorganize an Index
Rate Topic
Display Mode
Topic Options
Author
Message
rummings
rummings
Posted Monday, November 12, 2012 12:25 PM
SSC Journeyman
Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 92,
Visits: 291
I was hoping to rebuild all indexes on one of our SQL Server 2008 databases over the weekend. The database is used on our campus to record data from numerous monitoring units around our campus. This is a warehouse so it is very large. I tried to rebuild the indexes, but it ran for over 12 hours... so I tried to Reorganize the indexes.... still would not complete. I then tried to individually rebuild the most fragmented indexes. It was working.... then I hit the one that would not finish. How should I handle indexes that will never rebuild? Thank you in advance.
Charlie
Post #1383818
GilaMonster
GilaMonster
Posted Monday, November 12, 2012 12:52 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669,
Visits: 29,923
Were the rebuilds blocked by something?
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1383831
rummings
rummings
Posted Monday, November 12, 2012 1:01 PM
SSC Journeyman
Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 92,
Visits: 291
To be honest... I did not check... I will test off hours and let you know. Thanks for the advice!
Post #1383839
IgorMi
IgorMi
Posted Tuesday, November 13, 2012 1:51 PM
SSCommitted
Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 1,875,
Visits: 1,439
Hi,
You may have space issues for your tempdb, or generally space issues, and then it is slow.
Are you rebuilding online or offline?
Regards
IgorMi
Post #1384286
rummings
rummings
Posted Tuesday, November 13, 2012 1:58 PM
SSC Journeyman
Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 92,
Visits: 291
Hi Gail, Sorry in the delay getting back to you. I ran the test last night from home. The Index rebuild was running and another process was being blocked by the Index Rebuild (checking in Activity Monitor).
Charlie
Post #1384289
rummings
rummings
Posted Tuesday, November 13, 2012 2:00 PM
SSC Journeyman
Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 92,
Visits: 291
Hi IgoMi,
I have a lot of Space on the tempdb. The tempdb is located on a SAN. I was Rebuilding offline... then I tried to Reorganize the Index. Still ran forever.
Charlie
Post #1384290
GilaMonster
GilaMonster
Posted Tuesday, November 13, 2012 2:05 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669,
Visits: 29,923
Is the rebuild/reorg blocked by something else? If so, it will be sitting doing nothing until whatever is blocking it has finished.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1384293
GilaMonster
GilaMonster
Posted Tuesday, November 13, 2012 2:06 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669,
Visits: 29,923
IgorMi (11/13/2012)
Hi,
You may have space issues for your tempdb, or generally space issues, and then it is slow.
Lack of space will result in either the files growing or the rebuild failing, not it running slow.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1384294
sanket kokane
sanket kokane
Posted Tuesday, November 13, 2012 11:18 PM
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 11:21 PM
Points: 323,
Visits: 961
rummings (11/12/2012)
I was hoping to rebuild all indexes on one of our SQL Server 2008 databases over the weekend. The database is used on our campus to record data from numerous monitoring units around our campus. This is a warehouse so it is very large. I tried to rebuild the indexes, but it ran for over 12 hours... so I tried to Reorganize the indexes.... still would not complete. I then tried to individually rebuild the most fragmented indexes. It was working.... then I hit the one that would not finish. How should I handle indexes that will never rebuild? Thank you in advance.
Charlie
Try Ola's Index Optimization script
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
It help me to reduce my time from 12 hours to 2-3 hours for index rebuild/Re-organize .
-----------------------------------------------------------------------------
संकेत कोकणे
Post #1384393
George M Parker
George M Parker
Posted Wednesday, November 14, 2012 12:33 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:13 PM
Points: 516,
Visits: 1,004
Our index maintenance process includes a "SET LOCK_TIMEOUT" statement so that if any one index rebuild is blocked longer than the duration, it times out and moves on to the next index.
Post #1384811
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
