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.
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?
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.
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.