SQL 2008 Offline Database Rebuild

tech-
tech- used Ask the Experts™
on
I have a a database running in SQL 2008 I need help to run an Offline or a detached Database Rebuild...  I look forward to the help.  500 Points,  time
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
In order to perform Database Index Rebuild, database must be Attached and Online..
You can't perform Rebuild on a database that is either Detaches or offline.

Author

Commented:
How do I rebuild a database online?  It is a 160GB database that needs to be rebuilt ASAP.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
After you attach your database and it becomes online, execute the script below to rebuild it

USE ur_db_name
GO
exec sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)';
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
rrjegan17:

I ran that command suiccessful... The index physical report is still showing a few tables it recommends a rebuild on...  

Also, I was told by the software company that this rebuild could take days to complete.... and it only appeared to take a few minutes.  The server has dual core, x64 with 10GB ram...

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> The index physical report is still showing a few tables it recommends a rebuild on...  

Does those tables have Primary keys or Clustered indexes defined on it..
Else the report would say that it needs Rebuilds..

>> Also, I was told by the software company that this rebuild could take days to complete

It depends upon the fragmentation of indexes.

Kindly run the script above one more time and check whether those tables are not shown again in the report..

Author

Commented:
I ran it  sgsin...   Few tables are still reporting...

nonclustered index rebuild
Cluster index reorganize.

This report looks much better, it was every table prior.

Is there another in depth command?  Thank you
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Use the script in "D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes" of the below link to selectively Reindex or Reorganize table based upon the fragmentation

http://msdn.microsoft.com/en-us/library/ms188917.aspx
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> nonclustered index rebuild
Cluster index reorganize.

For Nonclustered index, it would require a index rebuild which can be ignored as the records would be stored in a random order.
And for Clustered index, reorganizing using the script in the earlier comment should help..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial