Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Rebuild Info Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 7:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Are u sure i need to do
Alter Table tablename Rebuild???

Error -Invalid syntax near Rebuild...








************************************
Every Dog has a Tail !!!!!
Post #1410581
Posted Wednesday, January 23, 2013 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
What edition of SQL Server?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410582
Posted Wednesday, January 23, 2013 7:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 6,370, Visits: 13,706
Execute the following against your database and substitute the table name in the WHERE predicate and post the results back please

SELECT		OBJECT_NAME(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.rows AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.type IN (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
END = au.container_id
WHERE OBJECT_NAME(i.object_id) = 'yourtablename'



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1410584
Posted Wednesday, January 23, 2013 7:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2
(Build 3790: Service Pack 2)








************************************
Every Dog has a Tail !!!!!
Post #1410585
Posted Wednesday, January 23, 2013 7:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Perry Whittle (1/23/2013)
Execute the following against your database and substitute the table name in the WHERE predicate and post the results back please

SELECT		OBJECT_NAME(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.rows AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.type IN (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
END = au.container_id
WHERE OBJECT_NAME(i.object_id) = 'yourtablename'




TableName IndexName IndexType PartitionNo NumRows InType NumPages TotKBs UsedKBs DataKBs
Test HEAP HEAP 1 1024112 IN_ROW_DATA 4465 35720 35392 35384








************************************
Every Dog has a Tail !!!!!
Post #1410587
Posted Wednesday, January 23, 2013 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
runal_jagtap (1/23/2013)
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2
(Build 3790: Service Pack 2)


There is your problem, you have posted in a 2008 forum, so you got a 2008 answer, when you should of posted in the 2005 forum, or detailed from the start it was 2005.

The only way to rebuild a heap in 2005 is either pump data into a new table or create a clustered index then drop it, but would seriously recommend keeping the clustered index.

http://sqlblog.com/blogs/louis_davidson/archive/2008/02/26/2008-rebuilding-a-heap.aspx




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410588
Posted Wednesday, January 23, 2013 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 6,370, Visits: 13,706
runal_jagtap (1/23/2013)
TableName IndexName IndexType PartitionNo NumRows InType NumPages TotKBs UsedKBs DataKBs
Test HEAP HEAP 1 1024112 IN_ROW_DATA 4465 35720 35392 35384



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1410593
Posted Wednesday, January 23, 2013 7:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
I am totally now
Will look in to this tomorrow.. thanks all for helping & assisting me








************************************
Every Dog has a Tail !!!!!
Post #1410596
Posted Wednesday, January 23, 2013 9:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 39,883, Visits: 36,230
Do not create then drop a clustered index. If you want a heap, live with the extent fragmentation. It's far less damaging than logical fragmentation. If you want to put a clustered index on, then put one on and leave it there.


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 #1410651
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse