Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Space did not gets released after removing data Expand / Collapse
Author
Message
Posted Thursday, November 21, 2013 6:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:56 PM
Points: 115, Visits: 233
Hi,
We have deleted 120GB of data but space did not released even after 2 days. Is there any reason for this? Could any one tell me how exactly it releases the space after truncating a 120GB table?
Thanks.


Thanks,
I’m nobody but still I’m somebody to someone………….
Post #1516622
Posted Thursday, November 21, 2013 8:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 36,984, Visits: 31,509
SQLAli (11/21/2013)
Hi,
We have deleted 120GB of data but space did not released even after 2 days. Is there any reason for this? Could any one tell me how exactly it releases the space after truncating a 120GB table?
Thanks.


Space is not deleted from the database after deletes or truncates. It simply becomes unallocated space in the database. Do you have enough room on disk without recovering this space as truly "free space"?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516629
Posted Thursday, November 21, 2013 8:25 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 745, Visits: 4,766
If you absolutely have to get the disk space free, then you can use SHRINKDATABASE or SHRINKFILE, but it does awful things to your databases. You have to reindex them all etc... not pretty. So only do it if you have to. Definitely read up on it in BOL before doing anything drastic like that.
Post #1516632
Posted Thursday, November 21, 2013 10:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:56 PM
Points: 115, Visits: 233
Pietlinden,
Yes, we shrunk the .mdf and alert moves to Warning from centreon. But i think it is not right resolution.

Jeff,
I understand that space will simply unallocated rather than releasing. But disk got only 9GB free space. I need at least 50 GB of free space.


Thanks,
I’m nobody but still I’m somebody to someone………….
Post #1516643
Posted Thursday, November 21, 2013 10:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 42,820, Visits: 35,949
SQLAli (11/21/2013)
Pietlinden,
Yes, we shrunk the .mdf and alert moves to Warning from centreon. But i think it is not right resolution.


If you really do want to release space back to the OS, it's the *only* resolution.

I understand that space will simply unallocated rather than releasing. But disk got only 9GB free space. I need at least 50 GB of free space.


Why? What good does 50GB do sitting unallocated and unused on a disk do? You might as well make the disk 50GB smaller.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1516645
Posted Friday, November 22, 2013 9:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:13 PM
Points: 33,198, Visits: 15,341
I'm with Gail on this. If you need the space, then shrink the files, but make sure that you leave space to reindex or perform maintenance.

If you just want xxx space to prevent alarms, you should really evaluate whether you need more space on the drive. If it's for backups, please, please don't store these on the disk with the mdf.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1516837
Posted Monday, December 16, 2013 2:01 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 27, 2014 9:05 AM
Points: 504, Visits: 364

Did you try rebuilding table including all the indexes? That would release space to database, then shinking file would relase space at OS.
Post #1523424
Posted Monday, December 16, 2013 2:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:56 PM
Points: 115, Visits: 233
Suneel,
No i did not try to rebuild the indexes. As i explained before i just shrunk the .mdf and space gets released.


Thanks,
I’m nobody but still I’m somebody to someone………….
Post #1523443
Posted Monday, December 16, 2013 4:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 36,984, Visits: 31,509
SQLAli (12/16/2013)
Suneel,
No i did not try to rebuild the indexes. As i explained before i just shrunk the .mdf and space gets released.


You're pretty well screwed then because, except for simple truncation during a shrink, shrink fragments nearly everything to 99% fragmented. You at least need to follow a shrink with a REORGANIZE (although that will only fix the Leaf Level of the indexes and data). Since REORGANIZE doesn't update stats, you should update the stats, as well.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse