1

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.

1

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.