Sql database suspect что делать

How to Repair the SQL Database in Suspect Mode [Solved]

Most of the users of the Microsoft SQL Server get an error when connecting to an SQL Server. This may happen due to some of the several reasons such as missing or corrupt transactional log files of the SQL Server database, faulty hardware, virus attack, abrupt shutdown of the SQL server, etc. View the SQL Server error log to know the specific cause behind SQL Server database suspect mode.

When SQL Database goes in suspect mode, you cannot access the DB and cannot perform any transactions until it is back online. To resolve this problem, follow these steps:

Step 1: Turn on the Emergency Mode of the database. Now, do these:

1. Load the Microsoft SQL Server Management Studio and connect your database
2. Select the option “New Query”.

Here, you have to turn off the suspect flag on the database and switch it to EMERGENCY. For that, you have to write as:

EXEC sp_resetstatus ‘db_name’;

ALTER DATABASE db_name SET EMERGENCY

Step 2: Execute the function Consistency Check on the Master Database as:

Step 3: Change the database to Single User Mode, and then roll back the previous transactions as:

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 4: Backup the database as the next operation might cause data loss.

Step 5: Run the Database Repair allowing some data loss as:

DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

A lert: You will lose all information after the above step, so do ensure to take a database backup before this step.

Step 6: Open the database into Multi-User Mode.

ALTER DATABASE database_name SET MULTI_USER

Step 7: Freshen up the SQL database server and check the connectivity of the database.

Conclusion

Here in these steps, you can resolve Database in Suspect Mode error with the implementation of 7 manual methods with great ease. Somehow if these steps don’t work for you, then we recommend the professional approach. Try the Cigati SQL Recovery tool to repair SQL Server Database Suspect. With it, you get an instant and 100 percent SQL database back to normal.

How to Recover MS SQL Database from Suspect Mode?

Summary: Read this post to find solutions to recover MS SQL database marked as suspect. It describes step-wise instructions to fix the ‘SQL server suspect database’ issue by running Transact-SQL (T-SQL) commands in SQL Server Management Studio (SSMS). Also, it provides an alternative solution to restore the database using a SQL Recovery tool.

When SQL database goes into suspect mode, it becomes inaccessible. In such a situation, you will neither be able to connect to the database nor recover it during server startup.

Figure 1: Database in Suspect Mode

Check out the Infographic below for quick solutions to recover database from suspect mode in SQL Server 2008, and higher versions.

When does SQL database goes to suspect mode?

When SQL server suspects the primary filegroup of the database to be damaged or if the database file is missing, the database status is set to ‘Suspect’.

Also, there are a wide range of errors that could result in SQL database in suspect mode. Some of them are listed as below:

• System fails to open the device where the data or log file of SQL server resides.
• SQL server crashes or restarts in the middle of a transaction, resulting in a corrupt or inaccessible transactions log file.
• SQL Server tries to open a database, and the file belonging to that database is already open by anti-virus software installed on your system.
• The database is terminated abnormally.
• Lack of disk space.
• SQL cannot complete a rollback or roll forward operation.
• Database files are being held by the operating system, third-party backup software, etc.

How to get SQL database out of suspect mode?

NOTE: You can try restoring the database in suspect mode from a good known backup. If the backup is not available, proceed with the following steps.

Follow the steps in sequence given below to recover MS SQL database from suspect mode:

Step 1: Open SSMS and connect to the database.

Figure 3: Select New Query

Step 3: In the Query editor window, enter the following code to turn off the suspect flag on the database and set it to EMERGENCY:

DBCC CHECKDB (‘database_name’)

This statement will report any consistency errors (if found) in the database and will recommend running the minimum level of repair option to fix corruption.

Before initiating the repair process, you must first set the database into ‘Single User Mode.’ Doing so will prevent other users from making any changes to the database during the repair process.

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE database_name SET MULTI_USER

ALTER DATABASE database_name SET MULTI_USER

Step 9: Refresh the database server.

After completing these steps, you should be able to connect to the database. In case of any data loss, you’ll have the db backup to restore from (Step 6).

What if this solution doesn’t work?

If your server database file has turned severely corrupt, the above-mentioned steps may fail to revive the database. At this point, try restoring the database by using Stellar Repair for MS SQL.

The software can fix common SQL database corruption errors that occur due to reasons such as the database in suspect mode and several others. The software uses advanced algorithms to repair and restore SQL db from suspect mode to normal state (online).

How to Recover SQL Database from Suspect Mode with the Stellar SQL Recovery Tool?

NOTE: Make sure to close the server instance before running Stellar Repair for MS SQL software.

Step 1: Download, install, and run Stellar Repair for MS SQL software.

Step 2: From the Select Database window, choose Browse or Search to select the SQL database file (.mdf) of the suspect database.

Figure 9: Select Database File

Step 3: Once the file is selected, hit Repair.

Figure 10- Repair Selected File

NOTE: Make sure to uncheck the ‘Include Deleted Records’ checkbox if you don’t want the deleted records to be recovered.

Step 4: Preview the repaired MDF file for recoverable SQL server database objects.

Figure 13: Save Database window

Step 7: Click Save.

Open SSMS and attach the db (containing the repaired MDF file). You will be able to access the database.

Additional features of the software

• Repairs corrupt MDF and NDF files.
• Recovers tables, triggers, keys, indexes, stored procedures, defaults, rules, schema, etc.
• Supports MS SQL 2019, 2017, 2016, 2014, 2012, 2008 R2, and lower versions.
• Provides multiple saving options to save the repaired database including MS SQL (MDF), CSV, HTML, and XLS.

The software is trusted by Microsoft MVPs

Conclusion

This post discussed methods on ‘How to recover MS SQL database from suspect mode’. The best approach is to restore the database from a healthy backup. If you don’t have backup, use the EMERGENCY mode to access the database and repair it. However, you may fail to rollback the transactions that were active when database went into suspect mode. Also, using the REPAIR_ALLOW_DATA_LOSS option as the minimum repair level can lead to data loss. A better alternative is to use a specialized SQL database repair software that helps repair and restore the database from suspect to a normal state.

About The Author

Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.

Best Selling Products

Stellar Repair for MS SQL

Stellar Repair for MS SQL is an enterpri

Stellar Toolkit for MS SQL

3-in-1 software package, recommended by

Stellar Converter for Database

Stellar Converter for Database is an eff

Stellar Repair for Access

Powerful tool, widely trusted by users &

33 comments

hi . i am facing this issue at my DR site . PR is showing the DB is in Synchronized but DR is showing initializing/suspect . please guide me how to do it?

i have this issue at my DR site . i restored the DB at DR and when i add that DB in high availability group it should that command executed successfully but then at DR status of that DB changed to initializing/suspect. can any one help me in this? i am quite new at SQL server

i have this issue at my DR site . i restored the DB at DR and when i add that DB in high availability group it should that command executed successfully but then at DR status of that DB changed to initializing/suspend. can any one help me in this? i am quite new at SQL server

Hi. My SQL Express database was in the Suspect mode. I followed the 9 ‘Steps to Fix the SQL Server Database Suspect Mode Error’ and my problem is fixed.

Tips To Recover SQL Database From Suspect Mode

Yatendra Singh | Updated On — November 30, 2022 |

Read time 9 minutes

Sometimes, Database Administrators – DBAs, encounter a problem which usually is not common, though the chances of its sudden appearance are positive – the problem we’re addressing here is Suspect Mode.

In rare situations, database administrators find that the SQL Database has become inaccessible and marked with the word Suspect.

The Suspect Mode is a tricky one because it shows that the recovery process has been initiated but not completed successfully. In such a situation, the administrator is not sure whether the database will be restored and is confused about what actions should be taken.

In this article, we’ll discuss the database suspect mode situation and things like what the consequences will be if you encounter the same and will walk you through some tips to recover SQL Database from suspect mode.

The article shows all the steps to remove the Suspect Mode for the SQL database through DBDC CHECKDB and ALTER DATABASE commands. However, if the database is facing any corruption, then the above command may delete the data. So, you should use the software and recover the whole database from any kind of corruption. After recovering the database, you can save the database back to SQL Server and start using it again.

What Happens When SQL Server Marks the Database as Suspect?

Before we start, let us see what happens when the SQL Server marks the database as Suspect?

Whenever a database receives the Suspect stamp from the SQL Server, it turns inaccessible and reflects the risk of data loss, straight.

In other fine words, because of the damaged primary file group, the database gets marked as Suspect, as it becomes inaccessible during the startup of SQL Server.

Access the fastest tool Kernel for SQL Database Recovery to recover SQL Database in Suspect Mode. This software can quickly resolve all corruption errors related to SQL Database.

There lay many reasons that can lead to the same scenario, as each time why it happened, the reason behind it may vary.

Below are a few arguments from many that state why the SQL Server marks the database as Suspect:

• Hardware Failure.
• Database server faces improper shutdown.
• Unavailability of log files.
• Lack of disk space.
• Unavailability of database files.
• Log files are corrupt.
• Unavailability of the database resource.
• Incorrect assertion of free data page space upon insertion of a new row.
• Third-party software and applications inflictions to SQL database
• SQL cannot finish a roll forward and roll back operation.

The issue of Suspect Mode can be addressed via different ways – specifically two, the first method requires the use of SQL statement parameters and the second is the direct recommendation to use SQL Database backups.

We will discuss both the methods one by one, starting with SQL statement parameters:

We will start by resetting the database status using which will turn off the suspect flag on the database. First of all go to the SQL Server Management Studio and connect to your SQL database. Then click on New Query option under the menu bar. Then start running the below provided SQL scripts.

1. EXEC sp_resetstatus ‘yourDBname’;
   Note:
   yourDBname is the example database name, change it with a real SQL database name.
2. ALTER DATABASE yourDBname SET EMERGENCY
   Note:
   This SQL statement parameter will change the database state and set it to Emergency to provide flexibility to perform tasks on a corrupt/suspect SQL database.
3. DBCC checkdb (‘yourDBname’)
   Note: This statement will initiate a consistency check on the select database to find out changes or loss of data such as rows or columns.

Again, change yourDBname with your database name.

Executing DBCC checkdb can produce distinct results, which can decide whether to proceed with SQL statement parameters any further or not.

Below are those outcomes – proceed accordingly to the result you receive on-screen:

1. In some cases, executing DBCC checkdb can fix the issue by resetting the database status. To confirm this check your database status. If the status has changed, stop here and don’t proceed with the next step.
2. If the database status didn’t change after the above statement execution, try running the command EXEC sp_resetstatus ‘yourDBname’; and database status again, If the change reflects, the issue is gone.
3. In case if an inconsistency is detected, proceed to the next step.
4. Run CHECK DBCC for similar errors like below:

Executing the above SQL statement parameter can fix the Suspect issue, execute as per the instructions mentioned above under notes.

If no inconsistency is found in step-3, there is no need for step 4-6 but go through the points given in step-3 and proceed as per the result you receive.

Now, before discussing the second method – primary & the recommended one, let’s shed some light on step-5:

It is strictly recommended not to run this statement if no inconsistency is found in step-3.

If Executed, What the Consequences Will Be?

• The data will be lost for sure if this SQL statement is executed, and afterward, the user – DBA or admin, is left with another job of retrieving the lost data, which is not possible.
• Running this SQL statement affects business and the organization will suffer, as the lost data can’t be recovered back.
• The SQL Server Database cannot revert to the earlier state.

When the issue of database file corruption or damage or suspect mode occurs, in some cases due to the error ferocity, it becomes almost impossible to fix the issue and recover the data.

The chances of the corrupt or damaged database file(s) recovery also turn negative in the absence of a full SQL Server Database backup. In similar scenarios like this, it is best to make use of a professional and advanced tool that is designed to fix these problems, Kernel for SQL Database Recovery fills the space perfectly when none of the native approaches comes handy.

It is a one-stop-shop for all SQL Database file(s) issues, name it, file(s) corruption, or damage or suspect status. It enables you to recover all the database objects and is compatible with both MDF & NDF database file(s). Live recovery to SQL Server, creation of backup of the database after restoration in the script, and wide compatibility with versions of SQL Server are a few highlighted features of the tool from many.

We have made the database file(s) recovery simple with no requirement of technical expertise throughout the process, and to help you better, below is a demonstration of corrupt SQL Server Database file recovery via this efficient tool, go through the steps one-by-one:

Below are the steps of the recovery process:

1. Open the installed the tool.
2. Click Browse to select and add the corrupt or damaged SQL Server Database file and click Recover.
   
   Note:
   In case, if the database version is not selected, uncheck Auto detect database version option, and select manually.
3. Once the database file scan competes, data will get retrieved on-screen as below. Select the desired SQL database objects and Click Save,
   
4. Input SQL Server Name and Server Authentication details; click Ok,
   
   Note:
   Select Batch File option, to save in batch file at the system drive location.
5. Wait for the saving process to compete.
   
   Note:
   In case, if something went wrong or you want to adjust something, click Stop.
6. Corrupt or damaged, SQL Database file(s) are recovered successfully, click Ok.
   
   Thus, you’ve successfully repaired the corrupt or damaged or suspect marked SQL Server Database file.

Full SQL Server Database backup is what that can help in worst-case scenarios like SQL suspect mode, but when there stands no option left, It is the best solution to all SQL database file(s) errors and issues. It offers minimal options to keep things simple which makes it much user-friendly.

alexvyrvich

Рассмотрим несколько вариантов восстановления БД, в зависимости от того, насколько повреждены файлы БД зависит успешность того или иного метода. Все описанные способы были лично мной проверены на практике и все случаи восстановления, за исключением одного, были успешны. Используйте данное руководство на свой страх и риск, за совершенные вами действия ответственность несете, вы сами.

Итак, во-первых останавливаем службу SQL Server и копируем файлы базы данных (*.mdf и *.ldf) в другую папку, чтобы можно было восстановить их в случае неудачи.

Если у вас есть свежий актуальный бэкап, то дальше можете не читать, а просто восстановите БД из него, тем самым сэкономите драгоценное время, далее я приведу алгоритмы восстановления для разных версий SQL Server. Надеюсь вам это поможет, как в свое время помогло и мне.

Для всех версий SQL Server подойдет следующий вариант: делаем Detach database (отсоединить базу данных), удаляем журнал транзакций (файл с расширением ldf) и делаем Attach database(присоединить базу данных). В мастере выбираем наш mdf файл и жмем ОК.

Если mdf файл не поврежден, то он успешно присоединится и мы увидим нашу базу в диспетчере объектов целую и невредимую.

Радуемся успешному восстановлению. (Этот вариант сработает только если mdf файл не поврежден, поэтому срабатывает не всегда). Если не получилось, то создаем новую базу данных с таким же именем, останавливаем сервер. Подменяем файл mdf файлом от нашей базы, стартуем службу SQL Server и открываем Query analyzer(SQL 2000) или Management studio(SQL 2005/2008) в зависимости от нашей версии сервера.

USE master
GO
sp_configure 'allow updates', 1
reconfigure WITH override
GO

Если у вас SQL 2000, то далее пишем:

UPDATE sysdatabases SET STATUS= 32768 WHERE name = 'db_name'
GO

Если SQL 2005 или 2008, то пишем:

ALTER DATABASE db_name SET EMERGENCY, SINGLE_USER
GO

где вместо db_name пишем имя своей БД

Жмем F5. После этого наша БД должна быть видна в статусе EMERGENCY.

В особо тяжелых случаях возникают проблемы с переходом в EMERGENCY, возможны даже проблемы с detach, в таких случаях поврежденная база удаляется, а далее происходит хитрая подмена файлов данных. Для начала создадим новую базу, имена файлов mdf и ldf должны совпадать с именами файлов поврежденной базы. Новую базу переводим в режим EMERGENCY, останавливаем службу MSSQL и подменяем файлы поврежденными. Таким образом мы получим рабочий инстанс в статусе EMERGENCY с поврежденными файлами.

Отлично, приступаем к восстановлению.

Выполним следующие SQL команды.

DBCC REBUILD_LOG('db_name', 'Полный путь к новому файлу ldf')
GO

Жмем F5, если все нормально, сервер скажет: Warning: The log for database ‘db_name’ has been rebuilt.

Стираем и пишем:

USE master
GO
sp_dboption 'db_name', 'single user', 'true'
GO
USE db_name
GO
DBCC CHECKDB('db_name', REPAIR_REBUILD)
GO

Если база не хочет в single mode можно попробовать такую команду

USE db_name
ALTER database db_name set SINGLE_USER with rollback immediate
GO

если DBCC не хочет выполняться, то вместо REPAIR_REBUILD нужно подставить REPAIR_ALLOW_DATA_LOSS

Жмем F5, ждем некоторое время. Сервер вернет кучу сообщений. Если там будут содержаться ошибки, то лучше еще раз выполнить DBCC CHECKDB с параметром REPAIR_REBUILD, пока все ошибки не будут устранены.

Для SQL 2005/2008 действия несколько иные:

DBCC CHECKDB('db_name', REPAIR_ALLOW_DATA_LOSS)
GO

Тут без вариантов. В SQL 2005 и выше нет инструкции REBUILD_LOG, вместо этого выполняется CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS.

После того как сервер закончит выполнять запрос и вернет результат, меняем REPAIR_ALLOW_DATA_LOSS на REPAIR_REBUILD и выполняем запрос еще раз, это должно убрать оставшиеся ошибки в бд.

После всего этого наша база становится в нормальное состояние и уже доступна для работы с ней, но только в однопользовательском режиме, поэтому завершаем наш процесс возвращением бд в многопользовательский режим.

USE master
GO
sp_dboption 'db_name', 'single user', 'false'
GO

Для SQL 2005/2008:

ALTER DATABASE db_name SET ONLINE, MULTI_USER
GO

Все. База онлайн и готова к работе. Радуемся и не забываем делать бэкапы.