SQL Server Performance Forum – Threads Archive

How to reclaim the disk space used by REINDEX

As everybody here knows, a DBCC DBREINDEX on a database creates lot of empty space in the database. Now I’m trying to reclaim this disk space by shrinking the database using DBCC SHRINKDATBASE. But SHRINKDATBASE is increasing the logical fragmentation of all the indexes into 90s (Probably due to the page movement caused by shrink). And a SHRINKDATBASE with ‘truncateonly’ option is not reducing the size at all. So, how can I shrink a database (after DBCC DBREINDEX) without fragmenting the indexes ?

Without fragmenting the indexes, I think no way.
That is way I only rebuild critical tables, but may be you are doing the same.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks Luis, The only reason we do DBREINDEX is to de-fragment indexes and there is no point if a shrink after DBREINDEX fragments the indexes again. But, I can’t understand why the ‘truncateonly’ option never works. This is supposed to truncate the free space from the end of the file. As I can see there is lot of free space in the data file, but this option does not release even a single byte out of it.

If there is only SPECIFIC NEED to shrink the database then do it, otherwise DBREINDEX and SHRINK DATABASE works opposite way and will have no use rather using server resources. If you don’t have disk space problems on SQL server then leave’em. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya, I have multiple databases on the same disk that I want to reindex and each database is growing almost double in size after a reindex. And I don’t have enough disk space to accommodate this growth. So, I want to reindex few databases at a time and shrink the extra disk space used by these before reindexing another set. If the DBCC SHRINKDATABASE with truncateonly option do what it’s supposed to do, then I’m all set. But for some reason this does not shrink the database at all. Do you know how to make this command work ?

In that case can you define DBREINDEX specific indexes instead of complete database-indexes, as I’d followed similar approach for one of our service. And the truncateonly option should work and check if there any uncommitted transactions are pending in the log avoiding the completion of this statement. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

You need to use DBCC SHRINKFILE instead of shrinkdatabase and shrink the log file only. This will not affect your indexes if you only shrink the log files. You might consider: 1. Stop your transaction log backups and switch the recovery mode to simple.
2. Run the DBCC DBREINDEX.
3. Switch the recovery mode back to full and resume your transaction log backups. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Thanks for your suggestions. Since I’m keeping the database in Bulk-Logged mode before DBREINDEX, the transaction log file in not growing much (though the subsequent transaction log backup creates a huge backup file). My main concern is with the data file growth, not log files. Any more suggestions to reduce this growth without increasing index fragmentation.

And here is some information from the dbcc shrinkfile command:
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— —— ———– ———– ———– ————–
19 4 1409832 128000 651120 651112 What I understand from this is, File 4 is currently occupying 1409832 pages, though only 651120 pages are being used by data in this file. But, shrinkfile still can’t shrink this file. Any ideas ?

On which filegroup the indexes are stored? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

On a filegroup named ‘INDEX’ which is seperate from the filegroup (PRIMARY) for data.

Have you tried reindexing only required indexes and check the space rather than all in the database? To be sure that you’re benefiting from your defragmentation efforts, measure the performance of representative queries before and after defragmentation. You might discover that you can reduce the frequency of your defragmentation or even eliminate this data-maintenance task. You can also stop and restart DBCC INDEXDEFRAG without losing any defragmentation work that you’ve already completed. For more information about defragmentation, refer to the white paper SQL Server 2000 Index Defragmentation Best Practices athttp://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>