I have created a job that loads several GBs of data from source DB to my destination DB on daily basis. in the beginning this job was running fine without any issues.
For the past 2 weeks the job is failing because of the transaction log is full.
I have added a step in my SQl job to clear the log before the loading starts, still the same error is happening.
I have monitored that the last table that is loading before the error is waiting for a couple of minutes before it fails. I suspect that it is waiting for the logs to get cleaned up.
If I run the DBCC SHRINKFILE command on this DB when the job is currently running, will it wipe out/kill the active transaction as well?
If I run the DBCC SHRINKFILE command on this DB when the job is currently running, will it wipe out/kill the active transaction as well
No, DBCC SHRINKFILE doesn't kill a transaction
I've added a step in my SQL job to clear the log before the loading starts.
If you are shrinking the file, and there is no free space, this isn't going to clear or shrink the log file. You need to backup the log file, ideally somewhat frequently based on your RPO and RTO, or swap your recovery model during this bulk insert to something like BULK LOGGED. More on how to do TLOG backups.