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
7 thoughts on “Database Shrink: NOTRUNCATE vs. TRUNCATEONLY”
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
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
Hi – I just wanted to share something with you both that might be helpful.
While routinely shrinking isn’t a good idea for the reasons discussed there is at least one scenario where there is some value to performing a shrink – with TRUNCATEONLY.
In my organisation we perform large volume data builds in a staging environment and then deploy the databases to a read-only reporting production environment. By performing a shrink prior to physically copying the DB files from STG to PRD we can significantly reduce the network bandwidth required for the deploy which speeds up the copy and also minimises performance disruption for other network users.
There are often rare exceptions to the best of rules. I hope this helps!
After large a purge that remove a significant volume of data a shrink makes sense if you need room for other databases to grow and you are pushing the capacity of the drive that the data files reside on.
Dammit, finally a clear explication. I will test tomorrow to use TRUNCATEONLY.