![]() In SQL Server, we have following three phases of database recovery. In the following screenshot, you can notice the database recovery time difference in a graphical way. Here is the difference you can notice between both the executions. Recovery completed for database SQLShackDemo_ADR (database ID 6) in 12 second(s) (analysis 8162 ms, redo 2593 ms, undo 236 ms.) This is an informational message only. Let us go to the error log, and we get the following message. We do not wait for long to wait in a painful situation for refreshing the error logs and wait to see the message that database is online. We can see that the database is online now. Once the server is back, connect to the instance. Let us repeat scenario 2 and restart the SQL Server while the query is still executing. ![]() Rollback with Accelerated Database Recovery database performed the rollback quickly. Rollback without Accelerated Database Recovery database took approximately 60 minutes to finish rollback. Run the query to insert bulk records in tblSQLShackDemo table and Kill the session after approximately 3 minutes. Let us perform both the scenario with this Accelerated Database Recovery enabled database. In the following screenshot, we can see that Accelerated Database Recovery is enabled for SQLShackDemo_ADR database. Now, run the sys.database command mentioned above. It might get improved in future releases of SQL Server 2019. ![]() It took approx 7 minutes for me to enable this feature on a blank database. We can see following pain points for DBA until now in SQL Server.ĪLTER DATABASE SQLSHACKDEMO_ADR SET ACCELERATED_DATABASE_RECOVERY = ON We will cover more about recovery stages in the later part of the section. It might take longer depending on the work SQL Server to do to bring database in a consistent state after recovery. SQL Server took 1802 seconds approximately 30 minutes to recover this database. Recovery completed for database SQLShackDemo (database ID 5) in 1802 second(s) (analysis 1375 ms, redo 551401 ms, undo 1246756 ms.) This is an informational message only. Once the database recovery is completed, we get the following message in the error log. Wait, the database is accessible but SQL Server still making the recovery of the database. At this point, database is available for the users. It is indeed a helpless condition for DBAs.Īs per following screenshot recovery of database recovery phase 3 is started. The worst part is that we cannot do anything apart from refreshing the error logs and monitor the progress. ![]() We need to wait for the database to come fully online. Really?! Do we need to wait for SQL Server database to come online for 10 hours? It is true. No user action is required.Īs per the error log entry, it will take approximately 36,351 seconds that is approximately 10 hrs. Recovery of database ‘SQLShackDemo’ (5) is 0% complete (approximately 36351 seconds remain). In the logs, you get the following message. We can check more details in the SQL Server Logs. We cannot access the database at this time. In the following screenshot, you can see that database status is In Recovery. Once the SQL Server is back online, expand the databases. However, the user database is still performing recovery. Once the system is up, you need to start the SQL Services. Let us imagine another scenario in which you started a transaction to insert a large number into our sample table. Scenario 2: Abnormal shutdown while the query is running We cannot do anything in this scenario except waiting to get it complete. It also blocks the current transactions on the particular table. You need to bear the extra load in terms of CPU, Memory in rollback as well. If the query goes longer before you kill it, it might take a few hours as well before the rollback completes. In the following screenshot, you can see it shows estimated rollback time is 3567 seconds that is approximately 60 minutes.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |