Today I came across this question. Everyone knows truncating table won't reduce the actual size of DB, in my scenario I have 5 tables on a DB which almost occupied 50 GB, so if I truncate all the tables in it and then start inserting new rows across tables will it increase the size or it still the same 50 GB, what I am trying to do is reuse the same tables- I don't need the old data.
SQL Server – Does Inserting Rows into Truncated Table Increase Size?
insertsql serversql-server-2008truncate
Related Question
- Sql-server – Table with 4 million records – different size and performance in development and production
- SQL Server 2008 R2 – Understanding PREEMPTIVE_REENLIST Wait Type
- Sql-server – Efficient way to copy/sync a table – ideas on different approaches
- SQL Server Partitioning – Benefits of Partition Switch with Identical Staging and Destination Tables
- Db2 – Will TRUNCATE stop the increase of the DB2 database size
- SQL Server – Insert Values into Table and Handle Duplicates
- Sql-server – Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE
- SQL Server – How to List Stored Procedures with INSERTs Without Column Names
Best Answer
It depends on how much free space was in the database before you started truncating, and how quickly you start inserting new rows.
As an optimization, a truncate on a large table (more than 128 extents) uses deferred drop. Within the truncate transaction, the allocation units associated with the table are simply marked for deallocation. After commit, the actual deallocation work is performed separately in batches by the system. As a result the space released by the truncate is gradually made available for reuse.
See Dropping and Rebuilding Large Objects
If you truncate all the tables in one transaction, then immediately start rapidly inserting rows to a database that had very little space remaining, the chances are quite good that the database would grow, at least for a short time until the deferred drops started to catch up.