What is the purpose of "sp_resetstatus" system stored procedure ?

 Posted by PandianS on 5/20/2011 | Category: Sql Server Interview questions | Views: 6190 | Points: 40

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 | Alert Moderator 

Comments or Responses

Login to post response