space release after deleting rows from rable
-
How to release space to OS after deleting rows from table?
We deleted about 25gb worth of data from 2 tables but not seeing free space on drive.
I shrank the data file, I thought that should do it, but no help.
Anyone have any ideas?
Regards,
SQLisAwe5oMe. -
DBCC ShrinkFile (or ShrinkDatabase) is the only way to release space back to the OS. Depending on what you deleted, you may need to rebuild the clustered index first if the delete just left a lot of pages mostly free. Of course, the shrink will fragment all indexes, so you'll need to rebuild them again after the shrink.
Why do you want to release space to the OS? Will that 25GB never be reused by this database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
As Gail already said the only way will be Shrinking the database/file(s). Deleting tables by itself will not give back the space to the OS. You will probably see the space available on the data file. If you think the database will grow again, I would not Shrink the database/file(s).
-
You could use one of the Standard Reports provided in SSMS (Right click the database -> Reports -> Standard Reports -> Disk Usage ( or Disk Usage by Table or Disk Usage by TOP Tables)) to get a report including the information about unused (= free) space.
This will at least tell you that SQL Server indeed did free the memory (and what amount) but hasn't released it to the OS yet.
Most probably you'll see more than 25GB marked as "unused"...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply