>> If the machine that runs this job locks up for some reason, will SQL server know to terminate the transaction/connection on its own and free those records? What about transaction log file? Isn't it going to be filled up pretty quickly as well since it won't be able to truncate due to those locks?


If NT detects no activity of the connection, it will notify SQL Server. SQL
Server will then terminate the connection and rollback the current transaction.

The transaction log won't be effected by the exclusive locks. It's the unterminated transaction that will prevent a full truncation of the log. SQL Server cannot truncate the transaction log past an open transaction. This is a fact of life. If you find that you're running out of space in the transaction log, just increase it. Remember, the work has to get done and disk space is cheap.

>> BTW, I do have "Truncate Log on Checkpoint" turned on for this particular database.

I don't recommend that this database setting be enabled for production databases. If it is, SQL Server will not allow the transaction log to be backed-up as a seperate entity. You'll be limited to full and diff. backups.

-Mike