Sometimes, we might encounter such error from SSIS:
The transaction log for database '@dbname' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Cause:
To find out available free space of transaction log file:
Object Explorer àSQL Server instanceàDatabaseàright click on task->shrink->filesàchoose File Type as LogàCheck below “Available Free Space”
The % of Available Free Space should be 0%.
View current settings of database by running this T-SQL command:
sp_helpdb [ [ @dbname= ] 'name' ]
View
size and
growth column for log file.
Next, find Location of the log file and log on to SQL server
machine. We should verify if there is no enough space for this log file
to be allocated (free space < growth)
Resolution:
Object Explorer àSQL Server instanceàDatabaseàright click on Property àChoose Files à
Option 1. Add one more log file
Option 2. Truncate original log file to a lower size(with this option, you will lose log message)
Run TSQL command with Alter database:
ALTER DATABASE @dbname MODIFY FILE ( NAME = N'GDS_Log_1', SIZE = 1925120KB )