SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


changing the table reserved size


changing the table reserved size

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)

Group: General Forum Members
Points: 416276 Visits: 47141

Hmmm. Wierd....

Can you run the following and post the output please

DBCC SHOWCONTIG('<table name>')



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Glitch
Glitch
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 3
DBCC SHOWCONTIG scanning 'actions' table...
Table: 'actions' (199671759); index ID: 1, database ID: 76
TABLE level scan performed.
- Pages Scanned................................: 2396
- Extents Scanned..............................: 1208
- Extent Switches..............................: 1489
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 20.13% [300:1490]
- Logical Scan Fragmentation ..................: 7.01%
- Extent Scan Fragmentation ...................: 50.99%
- Avg. Bytes Free per Page.....................: 127.3
- Avg. Page Density (full).....................: 98.43%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)

Group: General Forum Members
Points: 416276 Visits: 47141

Hmm. Very wierd

Try rebuilding the clustered index - DBCC DBREINDEX ('Actions') - and then do an update usage. See if that has any effect.

If not, then I'm really stumped.

From the show contig, it looks like there's lots of empty space within the extents (average pages per extent = 2. It should be 8 if the index has just been rebuilt)

Since extents are allocated to objects, not individual pages, that may well be your missing space.



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Tom Goltl
Tom Goltl
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2048 Visits: 205

How big is the server/database that this data is in. That table is only 120mg worth of data, really not that much. It seems you would be better off finding other items to clean up then this table, unless this is a 500mg harddrive. I agree with Gail, that index has not been rebuilt recently or inserts are that intense. I would run dbcc dbreindex ('actions','',80) to set the default fill factor back to 80.

Tom





Ian Yates
Ian Yates
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8032 Visits: 445
A brute force way could be to create a new table, move the data you wish to keep into it, drop the old table and then rename the new table and recreate links, indices, etc. The suggestions from other people are probably worth trying before this though as your system might be off the air for a few seconds whilst the data shuffle occurs. To minimise this you could
1. Create the new table
2. Drop FKs on the old one
3. Rename the old table and the new table (so the new table takes on its permanent name) - new inserts can go in to the new/empty table
4. Move the data
5. Drop the old table



Glitch
Glitch
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 3

Thanks everyone,

I ran DBCC DBREINDEX ('Actions') also dbcc dbreindex ('actions','',80) which give me

namerowsreserveddata index sizeunused
actions10391831232232407848144

the reserved size when down from 118800 to 31232. woohoo!

now I can play wow .. hehehe





Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117112 Visits: 9672
Sounds about right... What command did you use on the first reindex?
Glitch
Glitch
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 3

I used DBCC INDEXDEFRAG and I think I didn't run DBCC DBREINDEX properly.

Thanks all





Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117112 Visits: 9672
Ok, if you can figure out what didn't work, could you please post it in the forum so it may help someone else in the future?
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22017 Visits: 715
note that index defrag and index rebuild are very very different, the table was badly fragmented, I can't understand why you need to rebuild the table with 80% fill factor btw, you can't recommend a fill factor unless analysis of the table has been done to establish the optimal setting - first thing I always do is remove fill factors unless there is a justified proof for it, all you do is bloat database size, increase scans and readahead and increase io.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search