April 27, 2010 at 8:28 am
We've had massive deletes in our database and I need to reclaim unused space. I've run an index defrag and updated the statistics but I still have a HUGE amount of unused space in a variety of tables. How do I get this back?
sp_spaceused
Rows Reserved Data index_size unused
169764 637792 KB134352 KB168 KB503272 KB
dbcc chowcontig
TABLE level scan performed.
- Pages Scanned................................: 1392
- Extents Scanned..............................: 175
- Extent Switches..............................: 174
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.43% [174:175]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 28.57%
- Avg. Bytes Free per Page.....................: 46.8
- Avg. Page Density (full).....................: 99.42%
April 27, 2010 at 8:40 am
Ideally, you would leave the free space there so it's available as your database grows. Then it does not need to re-allocate the space. If you absolutely need the space available to the OS, you can shrink the individual database files. I would suggest only shrinking them part way and leave some free space in the database. Shrinking causes fragmentation, so you might want to rebuild indexes afterwards.
April 27, 2010 at 8:45 am
if the disk has multiple database files on it then it's best not to use shrinkdb. reason is that if it grows and you shrink it then the file will be physically spread all over the disk in small batches impacting I/O performance. not in a good way
April 27, 2010 at 10:00 am
The unused space I'm showing there is for a single table, not the entire DB. I've got multiple tables with large unused space and I want to release the space at just the table level. The datafiles are reporting that they are almost full but the tables show lots of free space.
April 27, 2010 at 11:03 am
i think you have to run alter index rebuild in order to reclaim used space. it will compact the indexes. haven't run the lite version of alter index for a long time so i'm not 100% sure
April 27, 2010 at 12:30 pm
Given the size and availability requirements I can't rebuild the index which is why I did the defrag.
April 27, 2010 at 12:41 pm
in the database properties, do you have any free space in there or is it almost full? what about when you right click on a database, go to reports and run one of the reports that tells you which table and file is full/free etc?
April 27, 2010 at 1:51 pm
The database reports 11GB free but the problem is that the mdf's are still expanding. Checking the tables with sp_spaceused shows that several of the tables have multiple GB's free . Which is why I'm trying to get that space released before we run into maxing out the disks.
April 27, 2010 at 1:58 pm
as you have seen indexdefrag may not give you the results you aimed for.
(Books online has more details about the differences between dbreindex and indexdefrag)
Do you have a clustered index for that table ?
If not, that may be your best option, because the leaf level of a clustered index actually contains the actual data pages.
(non-clustered indexes refer to the data using the clustered index key reference)
Check books online for details on index types.
If you don't want to create a clustering index, but need to free up pages, you may create one temporarly and drop it right after you created it.
This will cause sqlserver to actually rewrite the datapages twice. Once in the clustered index and once again to the new heap space.
Another option is to create a new table, and move the data to that one, rename the old table, copy over the last data , rename the new table. I don't prefer this way of doing it, because it you will also have to double check all security related stuff, indexes, DRI, ....
If you can, use DBCC DBReindex ! And use it for ALL your indexes individually (starting with the clustering one [because of the reason I mentioned above about NCI refering to the CLIx] !).
If you don't get the approval, just point to the fact your server is having issues, and you should take care of them thoroughly.
If you need to test it, you might just create a temporary persistent table (including the indexes), copy the data using the nolock hint, and rebuild the just created table to see how long it may take. Just to get a rough idea. Double that time and see if it is feasible.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 27, 2010 at 2:16 pm
The tables do have clustered indexes which leaves me with the rebuild option as the only thing I know to try but haven't, the problem is the time the tables would be unavailable given the size (worst one is 87 million rows).
Can't really move the data into a new table either so I'm feeling a bit trapped and praying for a better answer. Worst case I guess I put pressure on going for the rebuild and hope that pulls back some space unless there is another idea out there.
April 27, 2010 at 2:58 pm
If you have a lot of unused space reported in tables, run the following command in that database to make sure your space usage is up to date:
exec sp_spaceused @updateusage = 'true'
After that, you can run this script to look at the details of how space is used by each table.
Script to analyze table space usage
April 28, 2010 at 2:35 am
If yours is enterprise edition then you can make use of online reindexing.
April 28, 2010 at 8:58 am
how long would it take to rebuild one index?
this may be a business case to request new hardware or look at your I/O system. i've tested stuff like index rebuilds on local disk and EMC SAN and have gotten all kinds of results. i've seen a server attached to an EMC SAN have pretty bad I/O because of the way the disks were configured
SAN's are still expensive, but server hardware is dirt cheap these days if you configure it right
November 23, 2010 at 9:01 am
This is probably a bit late but for the record I had the same issue. 18GB database 17.5GB taken up by unused KB in one table. This unused space is not affected by reindexing, indexdefrag, or shrinking the DB. sp_spaceused was ran:
rows,reserved KB,data KB,index_size KB,unused KB
1884091,17066808,2312024,38832,14715952
The data was exported via DTS to a temporary database, deleted from the original table and re-imported via DTS and sp_spaceused now looks like this:
rows,reserved KB,data KB,index_size KB,unused KB
1884091,283488,250936,32496,56
This had no effect on the space in database file for growth this was all within the "used" portion of the database file. The 18GB database is now 600MB.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply