Troubleshooting Error No. 9002 (Insufficient Log Space)
You get this error when database was marked suspect during recovery due to insufficient log space.
To resolve this issue you can run sp_add_log_file_recover_suspect_db System Stored Procedure.
This adds a log file when recovery cannot complete on a database due to insufficient log space.
After the file is added, this SP turns off the suspect setting and completes the recovery of the database.
Permissions Required to run this SP
You should be a member of the sysadmin fixed server role.
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).
EXEC sp_add_log_file_recover_suspect_db db1, logfile2,
‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db1_logfile2.ldf’, ‘1MB’;