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?
EDIT
Recovery Model= simple. The truncate gets blocked mainly from users
that have Power BI data refreshes setup or users that are running sql
queries against it (via Power BI, excel, etc) Also, I have a small
window of time to try and get the refresh completed so any locks were
causing too much of a delay;
If you want to keep the delete, you should look into deleting in batches.
This way, the log will not grow as much when using the simple recovery model.
If you don't want to do that, you will have to live with the increased size of the database log file. If the log grows out every week it serves no purpose to shrink it.
The Log file increases some but not like the .mdf file which grows to
140GB+ and shrinks down to 2GB. Is the log file the same as the .ldf
file?
Correct, the log file corresponds to the .ldf file. If the .mdf file is the one growing, the only answer is looking at the data & datatypes.
If the data is deleted and inserted every load, the size should stay the same. If this is not the case, adding the datatypes, table definition and amount of rows to the question could be helpful.
