Follow the steps to resolve the issue when your DB is in simple recovery mode and the log file is full. Also this is applicable in a special case when there are numerous (>2000) virtual logs inside your transaction log.
1. Find log files of the DB whose log file is huge
SELECT * FROM sys.database_filesWHERE type_desc = 'LOG'
2. Find the log file usage as follows
DBCC SQLPERF (LOGSPACE)
3. Find the number of Files in the log file. This depends on how the growth of the log file. If growth is very less then the count would increase. The status = 2 => the file can't be shrunk.
DBCC LOGINFO (Conductor)
4.. Offline DB
5. Move the log files
6. Take DB online
7. Set Emergency
8. Put the DB in Single User Mode
Custom Search
9. Rebuild Log
DBCC CHECKDB (Conductor, REPAIR_ALLOW_DATA_LOSS)
10. Put the DB in Multi User Mode
ALTER DATABASE Conductor SET MULTI_USER WITH ROLLBACK IMMEDIATE
The only time that's going to work on a suspect database is it it is suspect because of a full log.
ReplyDeleteIt's not going to do anything (or will make matters worse) if the suspect status is a result of data corruption encountered during rollback/rollforward, log corruption encountered during rollback/rollforward or any other cause.
btw, there's a built in stored proc for fixing databases suspect due to log full. sp_add_log_file_recover_suspect_db
The case described in the blog talks about the scenario when the log file is full and the DB is in simple recovery mode.
ReplyDelete