reclaim space after moving indexes to file group

I have an extremely large database and most of the space is the index size. I moved several indexes to a different file group (just to experiment) but no matter what I do I cannot reduce the size of the MDF.

I tried shrink database, shrink files, rebuilding clustered index. What can I do to reclaim that space in the MDF? I've moved 25GB worth of indexes to a different file group. Is it even possible to reduce my mdf by that same 25gb (or close to it)?

SQL Server 2008 Enterprise

Group   Total Space     Avail Space
PRIMARY 388485.000000   27126.3125000
Index   24778.375000    26.6250000

Options I tried for shrink:

TSQL: 
1. DBCC SHRINKFILE (1, 10);
2. DBCC SHRINKFILE (1, TRUNCATE_ONLY);

UI: Shrink database: 
1. Without 'reorganize files...'
2. With 'reorganize files...'
2a. set max free space = 10%
2b. set max free space = 90%
2c. set max free space = 75%

UI: Shrink files: (type =data, group = primary, filename = db name)
1. Release unused space
2. reorganize pages... setting shrink file = 2GB (max it allows开发者_运维知识库)
3. reorganize pages... setting shrink file = 361,360 (min allowed)

did not try 'Empty file' option because that doesnt seem like what i want.

---

I've noticed in the past that shrinking the data file in smaller chunks can be more effective than trying to shrink it all in one go. If you were to attempt to use a similar strategy then you'd want to do something like below:

DECLARE @targetSize AS INT = 388000;
DECLARE @desiredFinalSize AS INT = 362000;
DECLARE @increment AS INT = 300;
DECLARE @sql AS VARCHAR(200);

WHILE @targetSize > @desiredFinalSize
BEGIN
    SET @sql = 'DBCC SHRINKFILE(''MyDataFileName'', ' + CAST(@targetSize AS VARCHAR(10)) + ');'
    SELECT @sql;
    EXEC(@sql);

    SET @targetSize = @targetSize - @increment; 
END  

---

OK, try through the UI a "Shrink Database" with "0%" max free space.

The 10% setting you used before "worked" but your 27 gigs free space is under 10% (its like 7%) of your total DB.

Bear in mind this may cause you performance issues down the road since your DB will need to grow again, and this will also cause performance issues now since shrinking leads to fragmentation which is bad.

However if space is your primary concern the above method should work.

---

This step looks wrong

UI: Shrink files: (type =data, group = primary, filename = db name)
1. Release unused space
2. reorganize pages... setting shrink file = 2GB (max it allows)
3. reorganize pages... setting shrink file = 361,360 (min allowed)

Among your question steps, this is the right one.

1. Shrink File
2. File type (Data), group (PRIMARY) Name ()
3. Shrink Action = reorganize pages (=move to front), Shrink File *

Shrink File is entered in MB, so a value of 361,360 would be a third of a Terabyte. The easiest way to set it to minimum is to enter 0, tab out -> it will replace with the min allowed.

Right now we need to get the size down to something manageable for backups

This is completely the wrong way to go about it. BACKUPs do not include free space, so whether your primary mdf file has 100GB of free space or 1TB, as long as the data resides on 200GB, that is the size of the backup files. For SQL Server 2008, you can use "with compression", which gives an average size of about 1/6th.

---

sometimes i've noticed that if i shrink down a file, it won't shrink below the initial size set on the file. could you go see what the initial file size of the file is?