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