asked
problems with shrink
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)
Just be aware, you don't want to get in the habit of shrinking your databases repeatedly. One time shrinks after a major data change like this is fine, but if you grow & shrink repeatedly you will fragment your data on the disk within Windows.
Are the tables you deleted from heaps or do they have clustered indexes? Also remove truncate only from the command, also shrink in small chunks say a few GB at a time. Also remember to rebuild all indexes and statictics after the shrink
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.
14 People are following this question.
