This system stored procedure is used to reset the database status from SUSPECT to normal.
The following are the considerations :
1. You should be under 'sysadmin' server role.
2. Should not be under Transaction. It will throw an err as follows
i.e: The procedure 'sp_resetstatus' cannot be executed within a transaction.
3. Database name should be valid and available
i.e: The database '<DatabaseName>' does not exist. Supply a valid database name. To see available databases, use sys.databases
4. The database should not be a snapshot. It should be a source database.
i.e: Cannot run sp_resetstatus against a database snapshot.
5. The database should be already in SUSPECT mode.
i.e: The suspect flag on the database "<DatabaseName>" is already reset.
Asked In: Many Interviews |