Hi, i need to shrink my database, i deleted 700 million rows. When use sql server GUI to shrink i see that i have 200gb free space to shrink, but when i run the shrink if finishes after a minute and returns that almost all pages are in used and doesn't do anything. Because the pages have only partially free space could it be that's why he cant do a shrink? Could it be that i need first to reorganize/rebuild index, to move the allocated space from pages to other pages and like that free the space on pages and just afterwards to shrink? And then after the shrink to do again a rebuild index? i used that tsql for shrinking: DBCC SHRINKFILE (N'ShopAndCheckUsers' , 470000, TRUNCATEONLY)
TRUNCATE ONLY will only release the space from the last allocated area back to the OS, so I for example if there is a page at 469GB in the db it will only release space from 469GB to 470GB back to the OS, so you only get 1GB back as TRUNCATEONLY doesnt perform page reordering.
I personally prefer to do things like this with t-sql. Use the query below to determine how much free space is available. SELECT Name ,Filename, CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)], CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)], CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)] FROM dbo.sysfiles a (NOLOCK) --Now use DBCC SHRINKFILE to manually shrink your .mdf or.ldf file.