Post reply

Truncating doesn't release OS space ?

  • 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

  • August 2, 2008 at 2:43 am

    #852284

    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

  • August 2, 2008 at 3:38 am

    #852290

    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

  • August 2, 2008 at 3:44 am

    #852293

    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

  • August 4, 2008 at 8:42 am

    #852722

    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.

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

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