Database log is full

在轉檔過程中，遇到異常，大部份報錯:

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

yi

解決方案:

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