Post reply

release unused diskspace function

  • August 23, 2011 at 4:43 am

    #242509

    What is the release unused diskspace function ? Is it effectively a shrink that will invalid the Transaction log backups

  • August 23, 2011 at 4:56 am

    #1371871

    could you clarify what you're asking?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • August 23, 2011 at 5:02 am

    #1371873

    are you referring to the option to release ununsed space in the database shrink dialog?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 23, 2011 at 5:05 am

    #1371875

    Shrink does not break the log chain.

    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 23, 2011 at 5:09 am

    #1371881

    Perry Whittle (8/23/2011)

    are you referring to the option to release ununsed space in the database shrink dialog?

    Yes this the option, im looking at

  • August 23, 2011 at 5:12 am

    #1371884

    MarvinTheAndriod (8/23/2011)

    Perry Whittle (8/23/2011)

    are you referring to the option to release ununsed space in the database shrink dialog?

    Yes this the option, im looking at

    Are you shrinking the database or an individual file?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 23, 2011 at 5:13 am

    #1371885

    I am shrinking the log file. When scripted out it comes up with the command line:

    DBCC SHRINKFILE (N'HCMPRD88_Data' , 0, TRUNCATEONLY)

  • August 23, 2011 at 5:20 am

    #1371886

    MarvinTheAndriod (8/23/2011)

    I am shrinking the log file. When scripted out it comes up with the command line:

    DBCC SHRINKFILE (N'HCMPRD88_Data' , 0, TRUNCATEONLY)

    it looks like youre trying to shrink the log file to 0MB, is that correct?

    Quite apart from the fact that sizes are ignored when specifying TRUNCATEONLY!

    see the following links for further details

    Shrink database

    Shrink file

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 23, 2011 at 5:53 am

    #1371898

    Why are you shrinking?

    Why are you shrinking as small as possible? Do that and the next thing that will happen is for the log to grow. That'll slow everything down.

    p.s. TruncateOnly on shrink is ignored for log files. It's only a valid option for data files (see BoL)

    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 23, 2011 at 6:04 am

    #1371910

    GilaMonster (8/23/2011)

    p.s. TruncateOnly on shrink is ignored for log files. It's only a valid option for data files (see BoL)

    good catch Gail i totally missed that bit 🙂

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 23, 2011 at 6:10 am

    #1371915

    i see what is confusing the OP now

    go into the shrink file dialog and select the log file and release ununsed space, then script action to new query window. It produces the following

    USE [mydb]

    GO

    DBCC SHRINKFILE (N'MYDB_log' , 0, TRUNCATEONLY)

    GO

    selecting the data file produces

    USE [mydb]

    GO

    DBCC SHRINKFILE (N'MYDB' , 0, TRUNCATEONLY)

    GO

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 23, 2011 at 6:37 am

    #1371938

    Yeah, SSMS writing bad code again. What's new?

    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 23, 2011 at 6:39 am

    #1371939

    It's consistent, i'll give it that. It's the same in SQL Server 2008 R2 management studio 😀

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 23, 2011 at 12:38 pm

    #1372218

    Marvin,

    Gail ask you a very good question, why do you want to shink the log file? Shrinking the log file is not always a good idea. First of all you can't use the instant file initialization on the log file, for more information on instant file initialization check this post by Kimberly Tripp. Shrinking the file causes fragmentation. You can find a very interesting post by Pinal Dave about this subject here[/url].



    My blog

Viewing 14 posts - 1 through 13 (of 13 total)

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