How to solve suspect status in MS-SQLSERVER Database

How to reset the database suppect status
I. Reasons for this situation
If the file or log growth mode of the database is set to the following two modes during daily operation:
1. files do not automatically grow
In this state, if the data or logs in the database grow to the specified file size hour, there is not enough space to continue adding data, ms SQL server will mark the database as suspect (suspect)
2. Automatic File growth but maximum file size limit

In this state, if the data or logs in the database grow to the specified maximum file size, there is not enough space when adding data, ms SQL server will mark the database as suspect (suspect)
3. Automatic File growth does not limit the file size, but the remaining disk space for storing files is insufficient.

4. Accidental power loss, resulting in disk file damage
5,

Ii. solution:
3,
Method 1:
Release the disk space on any disk drive that contains the relevant database log files. The released disk space enables the recovery system to automatically increase data or transaction log files.
Execute sp_resetstatus to reset the suspicious status.
Run DBCC dbrecover (database) to restore the database.

Method 2:
Release disk space on another disk drive.
Move the transaction log files with insufficient disk space to the disk drive referred to in step 1.
Execute sp_detach_db to detach the database.
Execute sp_attach_db to append the database and point to the moved file.

Method 3:
Add a log file to the suspicious database and execute sp_add_log_file_recover_suspect_db to run the recovery operation on the database.
Solve error message 1105 and then bring the database online
Release the disk space of any disk that contains files in the file group mentioned in error 1105. Releasing disk space can increase the number of files in the file group.
Execute sp_resetstatus to reset the suspicious status.
Execute DBCC dbrecover (database) for recovery.

Method 4:
Release disk space on another disk drive.
Move the data files in a file group with insufficient disk space to the disk drive referred to in step 1.
Execute sp_detach_db to detach the database.
Execute sp_attach_db to append the database and point to the moved file.

Method 5:
Add a data file to the questionable database and execute sp_add_data_file_recover_suspect_db to run the recovery operation on the database.

Execute sp_resetstatus.

Use alter database to add a data file or log file to the database.

Stop and restart SQL Server.
With the additional space provided by new data files or log files, SQL server should be able to restore the database.

Release the disk space and run the Restoration Operation again.

Sp_resetstatus indicates whether to shut down the database, but other options of the database are kept intact.
Note that sp_resetstatus can be used only under the guidance of your primary support provider or if you have any suggestions for troubleshooting.
Otherwise, the database may be damaged.
Because the system table is modified in this process, the system administrator must enable system table update before creating this process. To enable update, use the following procedure:
Use master
Go
Sp_configure 'Allow updates', 1
Go
Reconfigure with override
Go

Immediately disable system table update after the process is created:
Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go

Only the system administrator can execute sp_resetstatus. Disable SQL Server immediately after the process is executed.
Syntax:
Sp_resetstatus database_name

The following example will disable the question mark of the production database.
Sp_resetstatus Production

The following is the result set:
Database 'production 'status reset!
Warning: You must reboot SQL Server prior to accessing this database!
Note
Sp_resetstatus: disables the question mark in the database. This process updates the schema and status columns of the named database in sysdatabases.
Before running this process, you should refer to the SQL Server Error Log and solve all problems. Run sp_resetstatus to stop and restart SQL Server.

For some reason, the database may become suspicious. Possible causes include the denial of access to database resources by the operating system, and the unavailability or damage to one or more database files.

Permission
Only SysAdmin fixed server role members can execute sp_resetstatus.

Example
In the following example, the status of the pubs database is reset.

Exec sp_resetstatus 'pubs'

Insufficient data file space:
Sp_add_data_file_recover_suspect_db
When the restoration of a database cannot be completed due to the "insufficient space" (1105) error in the file group, add a data file to the file group. After a log file is added, the stored procedure closes the suspicious settings and completes database fault recovery. This parameter is the same as the alter database add file parameter.

Example
In the following example, database db1 is marked as suspect due to insufficient space in file group FG1 (error 1105.
Sp_add_data_file_recover_suspect_db db1, FG1, file2,
'C: Program filesmicrosoft SQL servermssqldatadb1_file2.mdf ', '1mb'

Insufficient log space:
Sp_add_log_file_recover_suspect_db
If the restoration fails due to the "insufficient log space" (9002) error in the database, add the log file to the file group. After a log file is added, the stored procedure closes the suspicious settings and completes database fault recovery. The parameters are the same as those in alter database add log file.
Permission
By default, the execution permission is granted to members of the SysAdmin fixed server role. These permissions cannot be passed.

Example
In this example, database db1 is marked as questionable due to insufficient log space (error 9002) during fault recovery.

Sp_add_log_file_recover_suspect_db db1, logfile2,
'C: Program filesmicrosoft SQL servermssqldatadb1_logfile2.ldf ',
'1mb'

Severity Level 22: SQL Server serious error table integrity doubt

These messages indicate that the table or index specified in the message is damaged due to software or hardware problems.

Critical level 22 errors rarely occur. However, if this error occurs, run DBCC checkdb to check whether other objects in the database are damaged. The problem may only exist in the speeding cache, rather than the disk itself. If so, restarting SQL server will fix this problem. To continue working, you must connect to SQL Server again. Otherwise, use DBCC to fix the problem. In some cases, it is necessary to restore the database.

If the restart is not helpful, the problem exists on the disk. Sometimes, destroying the specified object in the error message can solve this problem. For example, if the message says that SQL server finds a row with a length of 0 in a non-clustered index, delete the index and recreate it.

Severity Level 23: SQL Server severe error: database integrity doubt

These messages indicate that the integrity of the entire database is faulty due to hardware or software issues.

Critical Level 23 errors rarely occur. However, if yes, run DBCC checkdb to determine the degree of damage. The problem may only exist in the speeding cache, rather than the disk itself. If so, restarting SQL server will fix this problem. To continue working, you must connect to SQL Server again. Otherwise, use DBCC to fix the problem. In some cases, it is necessary to restart the database.

DBCC checkdb suggestions
In Microsoft? SQL Server? 2000, you can run DBCC checkdb when using the database, because DBCC checkdb changes the type of the lock controlled on the table when checking each database table.

In SQL Server 7.0 and earlier versions, DBCC checkdb (running DBCC checktable and checkalloc in sequence on each table of the database) often controls the shared lock (s) on the table ), thus, all data modification language (DML) statements are blocked.

In SQL Server 2000, DBCC checkdb controls schema locks on the table when checking the table to prevent metadata changes. Therefore, any Data Definition Language (DDL) is allowed on the table being checked) DML statements other than statements. This change provides greater flexibility for deciding when to run DBCC checkdb, because DBCC checkdb does not completely reject users' use of the system.

DBCC checkdb occupies a large amount of CPU and disk resources. Each data page to be checked must first be read into the memory from the disk. In addition, DBCC checkdb uses tempdb for sorting.

If the transaction is dynamically executed when DBCC checkdb is running, the transaction log continues to grow because the DBCC command blocks log truncation before the log is read.

We recommend that you run DBCC checkdb when the server load is low. If you run DBCC checkdb during peak load, both the transaction throughput performance and the completion time performance of DBCC checkdb will be affected.

Some Suggestions on DBCC Performance
Run checkdb when the system usage is low.
Make sure that other disk I/O operations, such as disk backup, are not performed at the same time.
Place tempdb in a separate disk system or fast disk subsystem.
Allow tempdb to have enough extended space on the drive. Use DBCC with estimate only to estimate the space required by tempdb.
Avoid running query or batch processing jobs that occupy a large amount of CPU.
Reduce the number of active transactions when running the DBCC command.
Using the no_infomsgs option significantly reduces processing and tempdb usage.
Consider using DBCC checkdb with the physical_only option to check the physical structure of the page and record header. This operation performs a quick check when errors caused by hardware are in question.