ETL process causes database to rapidly grow - Shrinkfile reduces size from 140GB to 2GB - Keep shrinking?

by Vicki   Last Updated September 11, 2019 17:06 PM - source

I have a database (sql 2012) that is used mainly for etl of data. Every hour ssis packages run that truncates/deletes and reloads full datasets. The database keeps growing even though the data is wiped out and replaced.

I ran a SHRINKFILE and the database went from 140 GB down to 2 GB. As the days pass, the database increases in size 2+GB a day.

I am trying to figure out how to prevent the database from growing so rapidly.

• Should I run truncate command or delete command? I try to use truncate but I was having issues with getting a lock on the tables so I switched to a delete • Is it better if I completely delete the table and recreate the object each time instead? Would this help with the database growth? • I have read where "shrinking" is not a good thing, but sometimes it is necessary. Would setting up a shrinkfile command to run weekly during off hours solve my problem?



Answers 1


Should I run truncate command or delete command? I try to use truncate but I was having issues with getting a lock on the tables so I switched to a delete • Is it better if I completely delete the table and recreate the object each time instead? Would this help with the database growth? • I have read where "shrinking" is not a good thing, but sometimes it is necessary. Would setting up a shrinkfile command to run weekly during off hours solve my problem?

First things first, are you using the full recovery model and not scheduling log backups? When are your log backups scheduled?

If yes, either change your recovery mode to simple (ALTER DATABASE ... SET RECOVERY SIMPLE or start taking log backups (more frequently). Consider the RPO of your database in this decision.

If not, then the delete is probably increasing your database's log file size as it is a logged operation.

The truncate table is a metadata only operation and should be instantaneous even for huge tables (as long as it is not being blocked).

MS Docs on TRUNCATE TABLE

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

&

When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

When running truncate table, the table will be locked. This includes a SCH-M lock. Nonetheless you should be using truncate if you are deleting all the data in the table.

Why and what is locking the table when running the truncate?

Randi Vertongen
Randi Vertongen
September 11, 2019 16:16 PM

Related Questions


Releasing free space in mdf file to OS

Updated April 10, 2017 14:06 PM

How to free the unused space for a table

Updated September 26, 2017 05:06 AM

Shrinking the log file does not reduce size

Updated February 05, 2018 15:06 PM

Truncate log file mirrored database

Updated June 07, 2015 22:02 PM