Post reply

Truncateonly and Unused space

  • April 12, 2006 at 11:07 am

    #169039

    Need some help to clarify my issue. Initial DB info (from sp_spaceused): db_size=240G, data=128G, index_size=10G, unused=87G. After Dbcc Shrinkdatabase (myDB, TruncateOnly) was completed I've got: db_size=230G, data=128G, index_size=10G, unused=87G. Why "unused" space was not changed (or almost- difference is pretty small) regardless truncate option? Thanks 

  • April 12, 2006 at 11:11 am

    #632157

    Did you run DBCC UPDATEUSAGE before you reran sp_spaceused?

    Look in the BOL for the syntax.

    -SQLBill

  • April 12, 2006 at 11:23 am

    #632164

    Shame on me- I forgot, thanks. After I updated usage I've got: unused=61G (and extra 20G were added to unallocated space). But my question still remains- why not all unused space has gone? And probably one more question arised-why these 20G of space were added to unallocated but not released to system instead? Thanks

     

  • April 12, 2006 at 11:26 am

    #632166

    what was your initial size of the database ?

     

    Kindest Regards,

    Amit Lohia

  • April 12, 2006 at 11:31 am

    #632171

    Sorry, but all initial database info is in my 1-st message. Thanks

  • April 13, 2006 at 3:25 am

    #632290

    The truncate_only option does not attempt to relocate data, so only releases space that happens to be at the end of the file. Also, shrinkdatabase will not reduce the size below that at which it was initially created. You must use shrinkfile to do that.

  • April 13, 2006 at 8:08 am

    #632394

    I am afraid these are not answers to my questions as 1) space was not released to system and 2 ) initial db size was pretty small. Thanks

  • April 13, 2006 at 10:35 am

    #632459

    Yes, they are answers. You ran SHRINKDATABASE using the WITH TRUNCATE_ONLY option. With Truncate_Only removes inactive portions of the log file. But it does NOT 'return' the empty space to the OS. It keeps the space as part of the log file and reuses it. If you want to return the empty space to the OS you must use the SHRINKDATABASE or SHRINKFILE command without the TRUNCATE_ONLY option.

    -SQLBill

  • April 13, 2006 at 10:39 am

    #632461

    Just try the SHRINKDATABASE without TRUNCATEONLY option. This will answer all your questions.

    Kindest Regards,

    Amit Lohia

  • April 13, 2006 at 1:16 pm

    #632504

    Thanks guys. Probably BOL explanation is slightly confusing. For example, for TruncateOnly option there is "Causes any unused space in the data files to be released to the operating system", i.e. first, data files (not only trans log) and second, released to OS. Anyhow, I tryed already without truncateonly option and it works. Thanks 

Viewing 10 posts - 1 through 9 (of 9 total)

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