Database Shrink: NOTRUNCATE vs. TRUNCATEONLY

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Two weeks ago I published my latest SQL Server Quickie, which covered the basics of Database Shrink operations in SQL Server, and why you should never ever use them. After the release of the Quickie, I got really good feedback on it, and one interesting question was about the additional NOTRUNCATE and TRUNCATEONLY options that are available. Therefore I want to take my time in this blog posting to discuss both options in more detail. Before you move on, please be sure to watch the SQL Server Quickie to have a basic understanding about what happens to your data file when you run a database shrink operation.

NOTRUNCATE

When you provide the option NOTRUNCATE to the database shrink command, SQL Server performs the shrink operation as I have described on the flipchart in the Quickie. This means that SQL Server moves pages from the end of your data file towards the beginning, where some free unallocated space is found. The main difference is that the data file itself is not shrunk.

This means that the unused space at the end of the data file is not reclaimed back in the file system. The physical size of your data file is the same as before the operation. After performing a database shrink with the NOTRUNCATE option you end up with some free space available at the end of your data file – and of course the index fragmentation that is introduced.

TRUNCATEONLY

When you run the database shrink command with the TRUNCATEONLY option, SQL Server truncates the data file from the end towards the beginning as long as you have some free unallocated space at the end of the data file. With this option SQL Server doesn’t move any pages within the data file. You yourself must make sure that there is some free space available at the end of your data file, so that TRUNCATEONLY can reclaim some space for you.

If you have read very carefully up to this point, you can now already imagine what happens under the hood when you run a regular database shrink operation without providing additional options:

  • In the first step SQL Server runs the database shrink operation with the NOTRUNCATE option to gain some free space at the end of your data file. Nothing happens here to the physical size of your file in the Windows file system. This step also introduces the index fragmentation.
  • In the second step SQL Server runs the database shrink operation with the TRUNCATEONLY option, and finally removes the unallocated extents at the end of the data file. It also shrinks down the physical size of the file in the Windows file system.

Summary

It doesn’t matter which options the database shrink command provides to you: a database shrink is never ever recommended! If you know that you have unallocated space at the end of your data file that you want to get rid of, it’s much safer to run a database shrink with the option TRUNCATEONLY, because you don’t introduce index fragmentation. But as soon as you use the option NOTRUNCATE, SQL Server has to move pages inside your data file, and this introduces serious index fragmentation.

Thanks for reading!

-Klaus

4 Comments

  • Christoph Müller-Spengler

    Thank you for sharing the bit that was missing in your SQL Server Quickie about SHRINK Operations.
    What do you think about regularly shrinking your databases with TRUNCATEONLY and then having it growing by autogrowth again. And that will loop all the time.
    Would that lead to external fragementation that will reduce performance significantly?

    • Hello Christoph,

      Thanks for your comment. Why do you want to shrink your database, when you want to auto growth it afterwards again?

      Thanks

      -Klaus

  • Christoph Müller-Spengler

    Hello Klaus,
    personally i do not want to perform that kind of “Maintenance”. I just hoped to get a very clear statement (like you always do) to never ever regularly perform that kind of shrink, even with TRUNCATEONLY. It’s because i know some people that have to be convinced and i am seeking for all the power that i can get 😉
    Tanks,
    Christoph

    • Hello Christoph,

      I would also not recommend it, because why would you shrink something, when you afterwards plan to auto growth it again – doesn’t really make sense. Try to initially size your database in the right way.

      I would only perform shrinks in some very rare *specific* circumstances, but not in a regular maintenance plan.

      Thanks & nice greetings

      -Klaus

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top