How to Add logfile to suspect database when full log size?

Reading Time: < 1 minute

Adds a log file to a file group when recovery cannot complete on a database due to insufficient log space (error 9002). After the file is added, sp_add_log_file_recover_suspect_db turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE database_name ADD LOG FILE.

 

sp_add_log_file_recover_suspect_db [ @dbName= ] 'database' ,   
    [ @name = ] 'logical_file_name' ,   
    [ @filename= ] 'os_file_name' ,   
    [ @size = ] 'size' ,   
    [ @maxsize = ] 'max_size' ,   
    [ @filegrowth = ] 'growth_increment'

 

Examples

In the following example, the database db1 was marked suspect during recovery due to insufficient log space (error 9002).

USE master;  
GO  
EXEC sp_add_log_file_recover_suspect_db db1, logfile2,  
'C:\Program Files\Microsoft SQL  
    Server\MSSQL13.MSSQLSERVER\MSSQL\Data\db1_logfile2.ldf',   
    '1MB';