August 7, 2009 at 3:19 am
Hi Everybody,
I am having a datafile(mdf) size of 300GB. I deleted lot of tables and data present in DB , but the mdf file is remains as 300G only. I think the free space is not releasing to OS. Is there any way to make the free space release to OS.
Thank you all,
Venu Gopal.K
Software Engineer
INDIA
August 7, 2009 at 4:10 am
You need to shrink the file using DBCC SHRINKFILE command or the UI.
August 7, 2009 at 4:24 am
You can shrink the file by using the command DBCC shrinkdb or DBCC shrinkfile (You can read about it in BOL). You can also do it with the GUI by right click on the database’s name and select shrink from the pop up menu. The question is why do you want to shrink the database? If you have a disk space problem, then I can understand it, but if you don’t have disk space problem, then you can leave the file at his current size. Shrinking the file might increase the tables’\indexes’ fragmentation. Also if you’ll shrink it there is a good chance that it will expend again as you insert new data into the database.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 7, 2009 at 4:47 am
venu_ksheerasagaram (8/7/2009)
Is there any way to make the free space release to OS.
Why do you want to do that? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply