I am running into 'transaction log full' issue with SQL Server 2008 R2.
My database includes 80,000 tables which contains up to 5 columns of BIGINT and there is an index for each columns.
The recovery model for the database is set to simple.
The pattern of work with the database is by conducting a large number of bulk insert, update and delete command for about 50 hours while for given time there are only 8 commands are executing in the same period of time.
The transaction log file was set to auto growth by 10 percent and was limited to 2,000 GB (this was defined by mistake since the disk size is only 500 GB).
The 'transaction log full' exception occurred while the database was executing the commands described above and its file size was 41 GB and the log file was 270 GB which use all disk space.
In order to prevent the issue from reoccurring we set the max log file size to 20 GB and removed all transactions in the commands described above (since the use of transaction was not needed).
But unfortunately we experienced this exception again while the log file reached the 20GB limit.
Is it possible that the SQL server doesn’t truncate the log file due to the fact that the server is always executing commands and therefore there is not a single moment were the transaction log is not in use?
What might be the reason for that behavior?