When ever we have to restore existing database, we always get error "Exclusive access could not be obtained because the database is in use" And what we do to kill all spids we either take database offline or in single_user_mode.
But is this the right solution ?
1. Drawbacks for taking database in offline:
- Some times it takes very long time to take database offline / online depend on size of database.
- If data file \ log file have internal corruption then you will not able to take database online again.
2. Drawbacks for taking database in single_user_mode:
- If you are taking database in single user mode and you didn't have any sessions on database you might not able to get session on database again to make it multi user or to run restore.
- Many times you will struggle to make database in multi user and some time if you are unlucky you have to restart sql database
- Some times by the time you make database in single_user_mode, some of system spid will take session and you cant kill system spid the only way to recover is restart sql server.
- If database is having any negative spid on it then you cant kill negative spid if database is in single user mode.
So best option is to kill all spid
How To Kill All SPID In Database
How To Kill All SPID On Multiple Databases
No comments:
Post a Comment