Hey Everyone,
What if you suddenly get
login failiures to a database and when u check it says the database is in SUSPECT
mode??
As per msdn, suspect
mode is
If you have a huge
database which demands high up time, DBCC Checkdb to identify the root cause may
not be the best solution. DBCC CheckDB can be time consuming.
--to check the status of
checkdb
select dateadd(second,estimated_completion_time/1000,
getdate()) as est_completion_time
,percent_complete,blocking_session_id,* from
sys.dm_exec_requests where session_id=sessionID_goes_here
or blocking_session_id=sessionID_goes_here
,percent_complete,blocking_session_id,* from
sys.dm_exec_requests where session_id=sessionID_goes_here
or blocking_session_id=sessionID_goes_here
--to identify any memory
dumps created
SELECT top 2 *
FROM sys.dm_server_memory_dumps
ORDER BY creation_time DESC
SELECT top 2 *
FROM sys.dm_server_memory_dumps
ORDER BY creation_time DESC
If you have a smaller
database you could follow below steps to recovery your database.
- EXEC sp_resetstatus 'DBName'
- ALTER DATABASE DBName SET EMERGENCY
- DBCC checkdb('DBName')
- If you get errors from checkdb=> ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- DBCC CheckDB ('DBName
', REPAIR_ALLOW_DATA_LOSS)
- ALTER DATABASE [DBName] SET MULTI_USER
However, If your
database is terabytes in size and unable to run dbcc checkdb, you could use
dbcc checktable option on the suspicious tables to isolate any corruption.But
the drawback is in EMERGANCY mode you are unable to execute DBCC Checktable
('tableName', REPAIR_ALLOW_DATA_LOSS).
We came across a
simmilar situation recently.
Point in time recovery was useless since the corruption comes with the log backups, you need to identify the point before the corruption and restore until then to solve the issue which is not practical and can cause huge data loss.
Our approach which was suggested by microsoft was to rebuild the log file at the cost of losing data in the transaction log.
Point in time recovery was useless since the corruption comes with the log backups, you need to identify the point before the corruption and restore until then to solve the issue which is not practical and can cause huge data loss.
Our approach which was suggested by microsoft was to rebuild the log file at the cost of losing data in the transaction log.
Simply you have to
detach the database and attach it without the logfile. A new log file will be
created and mapped to the database.
This way you can get the
database online.
Remember to run a DBCC
CHECKDB followed by the log rebuild and repair any inconsistancies.
Cheers
Nice blog! Keep it up!
ReplyDelete