Generally, if SQL database recovery is taking a long time, it means either the SQL Server service was cycled in the mid of a single & a small number of large transactions or there are extremely large number of transactions active at the moment that SQL Server is abruptly shutdown.
SQL Server database may also take long time to recover when it get unexpectedly shutdown in following conditions:
1. The SQL Server unexpectedly shutdown occurs shortly after an transaction rate extremely get increased, and even the checkpoint has not run since the transaction rate increased dramatically.
2. SQL Server unexpectedly shut down when there were several active transactions.
3. SQL Server was not shut down properly.
4. The sp_configure stored procedure setting recovery interval is set to a non-default value .
SQL Server database also take long time to recover, if the log file has grown several times.
Generally users suggest to stop and restart the SQL Server service. But it is bad idea because Stopping and restarting the SQL Server service will abort the active recovery process. The work that has already been completed in recovery process, will have to be repeated again when you restart SQL Server. Therefore, the overall recovery process time get more increase.
If the recovery process is taking a long time to complete and there doesn't appear any activity to resolve this issue then you may need to restore from backups, If it is due to log file size then you should decrease the size of the log file.
No comments:
Post a Comment