Suspect database issue fixed in SQL Server
In this article we will learn, how resolve suspect database. When we open our SQL Sever Management Studio and see that database(suspect). If we want to perform any operation or access suspect database, then we can't do anything.
One day, I was working and our computer system shutdown due to electricity. After that I was connecting to an SQL Server, I find that the SQL database (db) is marked as ‘SUSPECT’.
Then I was thinking that I didn't do anything, why this happen then I studied about it and found it's solution. Many people thing like me, that I didn't do anything but why this happened but many people are genius and they have solution.
Why this happen because of several reason like virus attack, corrupt transaction log file, fault of h/w, abrupt shutdown of SQL Server. When our database goes in suspect mode then we can't perform any operation and we can't access of our suspect database.
Resolve this issue use the following step.
Step 1: Open Microsoft SQL Server Management Studio and connect to your database.
Step 2: Open New Window
Step 3: Use the below SQL script into your New Query window which you opened and replace [YourDatabaseName] with the name of your suspect database.
execute above SQL query one by one. Now your problem will remove.
Note: [YourDatabaseName] = Your suspect database name.
Conclusion: Above SQL query solve your suspect database problem.
One day, I was working and our computer system shutdown due to electricity. After that I was connecting to an SQL Server, I find that the SQL database (db) is marked as ‘SUSPECT’.
Then I was thinking that I didn't do anything, why this happen then I studied about it and found it's solution. Many people thing like me, that I didn't do anything but why this happened but many people are genius and they have solution.
Why this happen because of several reason like virus attack, corrupt transaction log file, fault of h/w, abrupt shutdown of SQL Server. When our database goes in suspect mode then we can't perform any operation and we can't access of our suspect database.
Resolve this issue use the following step.
Step 1: Open Microsoft SQL Server Management Studio and connect to your database.
Step 2: Open New Window
Step 3: Use the below SQL script into your New Query window which you opened and replace [YourDatabaseName] with the name of your suspect database.
EXEC sp_resetstatus [YourDatabaseName]; ALTER DATABASE [YourDatabaseName] SET EMERGENCY DBCC checkdb([YourDatabaseName]) ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ([YourDatabaseName], REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE [YourDatabaseName] SET MULTI_USER
Note: [YourDatabaseName] = Your suspect database name.
Conclusion: Above SQL query solve your suspect database problem.
Leave a Comment