Today once the connectivity to the data and log volume for SQL servers is lost and I received the following error:
Log Writer: Operating system error 2
(failed to retrieve text for this error. Reason: 15100) encountered.
This resulted in the database on that volume showing as being suspect.
I tried to detach the database, after connecting back to the Server, but got below error:
Cannot detach a suspect or recovery pending database.
It must be repaired or dropped. (Microsoft SQL Server, Error: 3707)
To resolve above error i did the following:
ALTER DATABASE DemoData SET EMERGENCY;
GO
EMERGENCY mode sets the database as READ_ONLY, disabled logging, and access is limited to sysadmins. Marking the database in this mode is a first step for resolving log corruption.
ALTER DATABASE DemoData set single_user
GO
DBCC CHECKDB ( DemoData, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE DemoData set multi_user
GO
This should resolve any corruption and bring the database online. The database will come out of EMEREGENCY mode automatically.
Log Writer: Operating system error 2
(failed to retrieve text for this error. Reason: 15100) encountered.
This resulted in the database on that volume showing as being suspect.
I tried to detach the database, after connecting back to the Server, but got below error:
Cannot detach a suspect or recovery pending database.
It must be repaired or dropped. (Microsoft SQL Server, Error: 3707)
To resolve above error i did the following:
ALTER DATABASE DemoData SET EMERGENCY;
GO
EMERGENCY mode sets the database as READ_ONLY, disabled logging, and access is limited to sysadmins. Marking the database in this mode is a first step for resolving log corruption.
ALTER DATABASE DemoData set single_user
GO
DBCC CHECKDB ( DemoData, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE DemoData set multi_user
GO
This should resolve any corruption and bring the database online. The database will come out of EMEREGENCY mode automatically.
Thank you very much sir..
ReplyDeleteIt helped me a lot...
May I add my thanks to that, worked first time
ReplyDeleteHow long should I anticipate the Debugging Query to last on the first script? ALTER DATABASE DemoData SET EMERGENCY
ReplyDeleteI could not understand the question. Please elaborate.
DeleteThank very much.
ReplyDeleteWork for me.