Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kristeru
Starting Member
2 Posts |
 Posted - 2005-03-15 : 07:38:26
|
| I have read all about DBCC SHRINKDB and other commands and simply cannot figure out how to remove the free space from the following table. Nor can I figure out how the table got so much free space to begin with. here is the result of sp_spaceused for the table in question.rows 38324reserved 321808 KBdata 60528 KBindex_size 5576 KBunused 255704 KBNote that the unused space is 80%!This is happening for almost all of the large tables in my database.I have checked the following and none seems to do the trick: DBREINDEX, CHECKALLOC with REPAIR_REBUILD option, CHECKTABLE, CLEANTABLE, SHRINKDATABASE with the TRUNCATEONLY option Here is the output of SHOWCONTIG for this table:TABLE level scan performed.- Pages Scanned................................: 3582- Extents Scanned..............................: 2471- Extent Switches..............................: 2470- Avg. Pages per Extent........................: 1.4- Scan Density [Best Count:Actual Count].......: 18.13% [448:2471]- Extent Scan Fragmentation ...................: 93.48%- Avg. Bytes Free per Page.....................: 4533.9- Avg. Page Density (full).....................: 43.98% |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-15 : 07:51:31
|
| First thing, make sure you run DBCC UPDATEUSAGE on that table, it's possible the numbers are outdated.Next, look at your table structure and row size. If you have char/varchar(1000) or larger columns, and/or significant variations in the size of the data stored in them, you're going to have fragmentation issues no matter what. That seems to be borne out by your SHOWCONTIG numbers.If you do not have a clustered index on this table, add one. It can decrease the size of your index usage and may better consolidate the data pages.Last, do not use DBCC SHRINKDB, use DBCC SHRINKDATABASE or DBCC SHRINKFILE instead. SHRINKDB is obsolete and will be dropped in the next release of SQL Server. And SHRINKDATABASE will not consolidate a fragmented table, DBREINDEX does. |
 |
|
|
kish
Starting Member
45 Posts |
Posted - 2005-03-15 : 07:56:29
|
| USE <database>sp_spaceused @updateusage = 'TRUE'This will update all the unused pages in the database. |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-03-16 : 09:54:03
|
| Also check the fill factor of the tables. If this set to a low number (e.g. 20) that would indicate that there is, by design, space left on each page for future data modifications.What's with the yak thing? |
|
|
|
kristeru
Starting Member
2 Posts |
Posted - 2005-03-16 : 13:35:32
|
| How do you see the fillfactor for a table in SQL Server? I have not seen anything in the documentation on fillfactor for tables...only indexes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-17 : 04:34:06
|
| - Scan Density [Best Count:Actual Count].......: 18.13% [448:2471]- Extent Scan Fragmentation ...................: 93.48%Unless my memory is playing up Scan Density should be approaching 100% and Extent Fragmentation should be approaching 0%, if that's right these are cleary way out of wack and something is allowing enormous fragmentation. Quite possibly the fillfactor on a clustered index, if you have one on that table.If you didn't already do so you probably should use the ALLINDEXES option:DBCC SHOWCONTIG MyTable WITH ALL_INDEXESKristen |
|
|
|
|
|
|
|
|
Removing free space