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';

Author Profile

Mazidi Amin

Data is fascinating.

I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam, or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family .

My areas of specialty are:

• SQL Server 2019-2008 performance tuning.

• Designing high availability and disaster recovery solutions.

• Securing SQL Server

• SSIS , SSAS

• Designing Data warehouse