The Suspect state of SQL Server database is a state when the user cannot connect with the database. At such a state, users are not able to open, backup or restore their database. SQL Server 2005 announced a new table in msdb database, which is known as suspect_pages that logs some pages that are suspected as bad. Any time when the engine of database arises across the ID of page, which is bad, whether via a query of reading a page, DBCC operation or a backup/restore, the id of page is updated in this table with details related to the event. All these details can be found in the column of event_type in the table. It is important to keep in mind that the suspect_pages are maintained by the DBA or a user who is having permissions in make the updates in the msdb database. The table is limited to 1000 rows, so if there are records in the table, which are for no longer usage then, they should be deleted so that new rows can be added if needed. Before this, it is important to understand the reason behind the suspect database.
Cause Behind Suspect Database
The main reason of corruption is because of the I/O subsystem level, which means the problem with controllers drives, and maybe even drivers. However, the specific cause can be varied, for this, one can use CHKDSK (Disk-Check utility), which scans for bad sectors, and other storage issues, which can creep up in storage environments.
Setup Monitoring
Firstly, it is required to take the information from the suspect_pages table. As this table holds non-descriptive ids therefore, join it with sys.databases and sys.master_files so that the alert will have all the things, which are required in tracking down for resolving the issue.
As there is a query, which is needed along with all the data information for wrapping this query via logic for sending mail at the time of finding the data. By making the use of the database of mail various HTML formatting email alert can be formatted nicely. T-SQL code is discussed below that makes easy in adding in existing as well newly creating SQL alert.
With this benefit, supposing that the users will be running at some point where daily, monthly, weekly, yearly they will decide the suitable environment and clean up this table at the same time. By adding, other step with the similar work takes T-SQL that can be purge with anything on this table, which is older than 90 days.
Conclusion
By getting, the proper alert about the alert for the suspect database makes easy for users to be updated while working. Therefore, in the above discussion, we have discussed a way to create alert for suspect database for the future utilization of data.
EmoticonEmoticon