Truncating doesn't release OS space ?

Question

Truncating doesn't release OS space ?

Ankit Mathur-481681

SSCrazy

Points: 2074
More actions
August 2, 2008 at 12:51 am

#71771

Hi Friends,
While testing a few waysto reclaim OS space I deleted all of my tables except the 3 biggest of them all.
I truncated these 3 tables thinking that after deleting all other tables and truncating these 3 I should be almost bringing my database MDF size to bare minimum. But to my surprise it isn't the case. My MDF file still remains the same size as it was before I started this process.
I have done reindexing for all the 3 empty tables that still remains. Even that didn't helped.
Now the question is, why even after deleting all the tables and truncating the rest 3 I'm unable to bring down the size of MDF file even by a few notches. Doesn't truncating a table releases space back to OS ?
Please guide how can I bring down the OS space required.
Thanks
Ankit Mathur

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Ankit Mathur (8/2/2008)

Now the question is, why even after deleting all the tables and truncating the rest 3 I'm unable to bring down the size of MDF file even by a few notches. Doesn't truncating a table releases space back to OS ?

No. It just frees up space within the mdf file for later reuse by SQL

Please guide how can I bring down the OS space required.

Are you expecting to repopulate this DB? If so, may be better to leave it large, as growing the file is expensive and may cause file-system fragmentation.

If you're not expecting to repopulate the data to original levels, you can shrink the file down to a smaller size. DBCC SHRINKFILE(1, < target size > )

If you have any tables that still contain data, you should rebuild their indexes after the shrink, as shrink shuffles index pages around.

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

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

Ankit Mathur-481681 SSCrazy Points: 2074 More actions · Answer 2

Hi Gail,

Thanks for the reply.

I actually wanted to avoid using SHRINKFILE option. Is there any other way to reclaim OS space. I read somewhere it should not be a preferred mode.

Also, while I'll be repopulating these tables that'll be after sometime and till then I cannot block the space.

As for the third part, right now there's no other table other than the 3 tables I was talking of and as discussed already they all have been truncated. So effectively, there's absolutely no data in USER Tables and it can be safely assumed that only the Table schema for the 3 user tables remains except system tables.

Ankit Mathur

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ankit Mathur (8/2/2008)
I actually wanted to avoid using SHRINKFILE option. Is there any other way to reclaim OS space. I read somewhere it should not be a preferred mode.

Nope. The way to return space to the OS is to use shrinkfile or shrinkdatabase. They're not recommended, but as a once-off operation it's not too big an issue, especially in dev/test environment. Just don't schedule it regularly in production.

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

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

Ray Mond SSCertifiable Points: 7159 More actions · Answer 4

I read somewhere it should not be a preferred mode.

Perhaps the articles were referring to the autoshrink database option, since you refer to it as a 'mode'.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.