February 15, 2010 at 1:54 pm
I have 3 tables which has around 40K records and these 3 tables occupy 23MB of space.
I deleted all the records from these 3 tables but i dont see the size of the mdf file reduced.
Am i missing something? How do I reclaim that space?
February 15, 2010 at 2:08 pm
Database files don't automatically shrink when you delete data - that is normal. If you really need to recover the 23MB of space, then you use SHRINKFILE to shrink the database file.
However, you should not do this if you are going to be adding data to the database - as the file will just grow again causing the file to become fragmented. Besides, are you really concerned about 23MB of space?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 15, 2010 at 2:20 pm
Thanks Jeff for the reply.
The mdf was 468 MB before the deletes and still it is 468 MB. And we will be adding data in future.
We are using Express edition and my concern is that the data will be growing in future and might cross the 4 GB threshold. 😀
So, I am looking at all possibilities of changing the design how the data is growing and removing unnecessary data.
February 15, 2010 at 2:23 pm
If you expect new data to be added, I would suggest you to monitor the size of the file closely rather than thinking about the shrinking data files.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 15, 2010 at 4:40 pm
Make sure you defrag\Rebuild index after you perform many DML changes, because the index will be fragmented after DML changes, that is why mdf file is same size even after deleting data. Although 23 MB is not the issue here.
EnjoY!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply