The transaction log for database '@dbname' is full

 

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:

DBCC SHRINKFILE('@logname', 1)
BACKUP LOG @dbname WITH TRUNCATE_ONLY

转载于:https://www.cnblogs.com/harryaya/archive/2008/01/18/1044444.html