SQL Server Performance

dbcc shrink database TRUNCATE VS TRUNCATE ONLY

Discussion in 'Performance Tuning for DBAs' started by techbabu303, Apr 27, 2007.

  1. techbabu303 New Member

    Hi Folks,

    Wanted to know your thoughts on this ,

    NOTRUNCATE VS TRUNCATEONLY

    1. Does not allow free space to be 1. Allows the free space reclaimed to OS
    reclaimed to OS and file size remains
    the same.

    2. Reorder the page to start which improves 2. File is reduced and no pages are moved.
    IO reads , since the pages are moved from
    the end of file to start.

    3. Can be used in scenarios where you have disk space 3. When run out of disk space
    and want better performance.Since autogrowth if set
    on databases's does not happen frequently.


    Cheers
    Sat
    techbabu303, Apr 27, 2007
    #2

  2. satya Moderator

    If this was an interview question or a classroom based, what was your answer.<br />I would like to know your thoughts and then can addup that are required. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>
    satya, Apr 27, 2007
    #2

  3. techbabu303 New Member

    This was never intended to be interview Q, my understanding of the BOL is listed in my query.

    Not sure I missed this , i dont know if this is correct too ......there are articles which states make sure you leave 20% free space on data files, so was investigating the shrinkdatabase option for data file which have free space lesser than this threshold if it is correct.


    For logs you check if there more VLF's before u shrink them but for data u need to check the free space requirement for optimium performance also this needs to for databases with auto grow options.

    Anyway Iam sure this fits a piece of mainatinence Iam puzzeled with ???

    Cheers
    Sat


    techbabu303, Apr 27, 2007
    #2

  4. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] The question sounds like that, so hope you don't mind for my reply above.<br />Good that you are able to investigate on your learning curve, on the case of log files yes it purely works out on VLF and I'm covering few bits from what you've read:<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.<br /><br />Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.<br /><br />When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary. For example, if a user specifies a target_size of 325 MB for our sample 600 MB file with 100 MB virtual log files, the last two virtual log files are removed and the new file size <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />So if you have already enabled the AUTOSHRINK on the database then log may not shrinked as and when it occurs, that works for data file mostly. In the case of log file truncation or shrink operation you must consider the amount of transactions waiting on log and their status to be written to the disk or a checkpoint statement. So on your first post number 2 will be applicable.<br /><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>
    satya, Apr 27, 2007
    #2

  5. techbabu303 New Member

    Hi Satya,

    Thx for the reply , will have to understand the consquence or impact of enabling the AUTOSHRINK in user databases as you explained.

    When you say 2 is applicable do you mean NOTRUNCATE with AUTOSHRINK enable on database ?

    Cheers
    Sat
    techbabu303, May 2, 2007
    #2

  6. satya Moderator

    No on your first question:
    2. Reorder the page to start which improves 2. File is reduced and no pages are moved.
    IO reads , since the pages are moved from
    the end of file to start

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
    satya, May 2, 2007
    #2

  7. techbabu303 New Member

    Now that I have fairly understood the DBCC SHRINK FILE with both options, say I have user databases with free space less that 20% on data files is it good idea for me to go for option DBCC SHRINK FILE NOTRUNCATE.

    Can I safetly say <20% free space on data files my cause peformance degradadtion ?



    I have all my user db's with AUTO SHRINK disabled based on premises of the article, which states it would be additional overhead.

    http://www.sql-server-performance.com/database_settings.asp


    Cheers
    Sat
    techbabu303, May 3, 2007
    #2

  8. satya Moderator

    IF you have only any issues with free disk space then I would suggest to go with SHRINK operations, otherwise you are wasting system resources for cycling the waste.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
    satya, May 3, 2007
    #2

Share This Page