Hi Friends, I would like to share SQL related issue which I facing at present and hope you will give me your valuable suggestions. I have 10 database in application with different size 4 database size is bigger than 300 GB, and 5 DB in range of 75 – 100 GB and on DB is approx 10 GB and their log is approx 1.25 GB with full recovery mode. Recently I notice when we restart our server or restart sql server services, the smallest database goes in recovery. And take approx 20-30 min to come up in normal condition. Rest all database work fine. No any job or maintenance plane is created on server. I’m stuck in find out root cause of this problem. Is it a virus related issue? Or application related issue ? this database is not access by huge network. Only 7-10 people access this DB thorough UI. Thanks
The recovery process is where transactions that are incomplete are rolled forward or rolled back as the database starts up. Even if the database is small, if it has large numbers of transactions that it has to work through, that might explain why it's taking so long to recover. I'd suggest getting the wait statistics from your server immediately after the start up to see what is causing things to run slowly. You can do this by querying sys.dm_os_wait_statistics. That's a good place to start.