Crash recovery operates by rolling back incomplete transactions and writing to the disk completed committed transactions that were still in memory when the crash occurred.<\/p>
A quick look at the causes of a crash recovery could be:<\/strong><\/h2>- A hardware failure like disk, CPU, memory, or network.<\/li>
- A fatal operating system error<\/li>
- A power failure on the server.<\/li>
- The DBMS software crashed<\/li>
- Human error<\/li><\/ul>
A database recovery process is a crucial requirement for all RDMS and it can end up being a mind-numbing process. The quest to improve the recovery process has birthed different procedures, but still only few have really understood RECOVERY.<\/p>
In this article, I’ll demonstrate the impact of a dirty shutdown of the database instance while a transaction is running and provide several techniques and tools that are available for faster and successful recovery even in the worst-case scenario of a corrupted database. Let’s stay under the assumption that we currently have a LIVE database with no issues and OLTP transactions<\/a> being run against the database(s). In this simulation, I would have to create a test database and try to mimic the above assumptions.<\/p>First, we run this code to create a blank database for simulation purposes<\/strong><\/p>Use Master\nGo\n--Create a new database\nCREATE DATABASE CrashRecoverySimulator\nGo\n<\/pre>Next, using the database context of CrashRecoverySimulator, we create a dummy table with 3 columns and insert test data of replicated data values.<\/p>
--Use it\nUse CrashRecoverySimulator\nGo\n--Create a new table\nCREATE TABLE CRS\n(\nColA VARCHAR(50) NOT NULL,\nColB VARCHAR(50) NOT NULL,\nColC VARCHAR(50) NOT NULL\n)\nGo\n--Insert a record\nINSERT INTO CRS VALUES\n(\nREPLICATE('A', 25),\nREPLICATE('B', 25),\nREPLICATE('C', 25)\n)\nGo\n<\/pre>Now this is where we want to begin a transaction and keep it open to simulate the crash recovery of the SQL Server database<\/p>
--Begin a new transaction without committing it…\nBEGIN TRANSACTION\nUPDATE CRS\nSET ColA = REPLICATE('X',50)\nGO\n<\/pre>With the transaction still running, perform a dirty shutdown with code below. This mimics the scenario of a server crashing or forced shutdown.<\/p>
NOTE<\/strong>: Run this in a separate query window or editor.<\/p>--Simulate a dirty shutdown of instance\nSHUTDOWN WITH NOWAIT\nGO\n<\/pre>Now that SQL Server has stopped, we need to restart it by going to SQL Configuration Manager, Right Click on the SQL instance and Click Start.<\/p>
With SQL Server running successfully and all databases ONLINE, including CrashRecoverySimulator. You can check database state with the code below.<\/p>
--Get query result of databases and their state\nselect name,state_desc from sys.databases order by name<\/pre>Result<\/strong><\/p>Now let’s query the CRS table and see if the UPDATE we performed in the running transaction is available.<\/p>
--Use it\nUse CrashRecovery\nGo\n--The transaction was rolled back\nSELECT * FROM CRS\nGo\n<\/pre>Result<\/strong><\/p>The UPDATE statement was rolled back hence the data in ColA still remains the same.<\/p>
Want to know the INTERNALS?<\/strong><\/h2>Logging serves a provision for a variety of operations in SQL Server.\nThere are so many reasons as to why logging is vital in any RDMS. A few are\npresented below:<\/p>
- It makes sure that if a crash occurs, a committed transaction will be correctly reflected in the database after the crash.<\/li>
- It ensures that an uncommitted transaction will be correctly rolled back and not reflected in the database after a crash.<\/li>
- It ensures that it is possible to cancel an in-flight transaction<\/a> and have all its operations rolled back.<\/li>
- It allows a backup copy of the transaction log to be taken so that a database can be restored and the transaction log backups replayed to bring the database to a specific point in time with transactional consistency.<\/li>
- It supports features that rely on reading the transaction log, such as replication, database mirroring, and change data capture.<\/li><\/ul>
The majority of these uses of logging involve a mechanism called recovery. Recovery is the procedure of having the changes found in log records restated or reverted in the database. Recovery has three phases: analysis, redo and undo. Database is completely accessible only after all three phases of recovery has completed. There is a feature in the enterprise edition called FAST recovery which allows the database to come online after REDO phase. Aside this, all these phases are required to happen before database can be in a consistent state.<\/p>
Replaying log records is called the REDO (or roll forward) phase of recovery. Reverting log records is called the UNDO (or roll back) phase of recovery. In other words, recovery will make sure that a transaction and all its constituent log records are either redone or undone.<\/p>
The simple form of recovery is when a single transaction is canceled, in\nwhich case it is undone and there is no net effect on the database. The most\ncomplex form is crash recovery—when SQL Server crashes (for whatever reason)\nand the transaction log must be recovered to bring the database to a\ntransactionally consistent point. This means that all transactions that were\ncommitted at the time of the crash must be rolled forward to ensure their\neffects are persisted in the database. And all in-flight transactions that had\nnot committed at the time of the crash must be rolled back to ensure their\neffects are not persisted in the database.<\/p>
This is because there is no facility for a transaction in SQL Server to continue after a crash. Thus, if the effects of a partially complete transaction were not rolled back, the database would be left in an inconsistent state (possibly even structurally corrupt, depending on what the transaction was in the middle of doing). (Randal, 2009)<\/p>
Why is my recovery taking so LONG?<\/strong><\/h3>Did you look at your SQL Server error longs and notice the below information? You are possibly experiencing a slow database recovery process. The three mentioned phases talked above is occurring in this scenario and the possible ONLY solution is to WAIT for it to complete. The wait time can be exponentially long based of factors of size of database files, number of VLFs, and computer resources.<\/p>
Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28890 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p>Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28595 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p>Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28499 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p>Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28202 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p>Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28067 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p>Query rollback operation is usually single threaded while when same query executes it can use parallelism\/multiple threads to perform same operation making it very fast as compared to the rollback. Even restarting SQL Server service is will not help much, as it will actually make it worst as the rollback process will restart again. Luckily there are some dmv’s that help give you an estimated completion time.<\/p>
Sample:<\/strong><\/p>Select session_id, command,status, percent_complete, estimated_completion_time\nfrom sys.dm_exec_requests; \nwhere command IN ('killed\/rollback','rollback','db_startup')<\/pre>Result<\/strong><\/p> Session_id <\/td> command <\/td> status<\/td> percent_complete <\/td> estimated_completion_time<\/td><\/tr> 35<\/td> DB STARTUP<\/td> background<\/td> 86.06061<\/td> 289093<\/td><\/tr><\/tbody><\/table><\/figure>From the result, you can see that there is a background process running a DB STARTUP command which is at an estimate percent of 86% with an estimated completion time of 289093 milliseconds. Convert the time to minutes or hours to get a rough idea of when the process will complete.<\/p>
What happens if recovery FAILS?<\/strong><\/h4>In a case of failed recovery of the database, we would either have to restore the database from good known backups or attempt to repair the database. Allow me to introduce to you a tool that is trusted by Microsoft MVPs. Stellar SQL Database Repair<\/strong> is the most recommended SQL Recovery software<\/a> <\/strong>amongst database administrators. <\/p>
A database recovery process is a crucial requirement for all RDMS and it can end up being a mind-numbing process. The quest to improve the recovery process has birthed different procedures, but still only few have really understood RECOVERY.<\/p>
In this article, I’ll demonstrate the impact of a dirty shutdown of the database instance while a transaction is running and provide several techniques and tools that are available for faster and successful recovery even in the worst-case scenario of a corrupted database. Let’s stay under the assumption that we currently have a LIVE database with no issues and OLTP transactions<\/a> being run against the database(s). In this simulation, I would have to create a test database and try to mimic the above assumptions.<\/p> First, we run this code to create a blank database for simulation purposes<\/strong><\/p> Next, using the database context of CrashRecoverySimulator, we create a dummy table with 3 columns and insert test data of replicated data values.<\/p> Now this is where we want to begin a transaction and keep it open to simulate the crash recovery of the SQL Server database<\/p> With the transaction still running, perform a dirty shutdown with code below. This mimics the scenario of a server crashing or forced shutdown.<\/p> NOTE<\/strong>: Run this in a separate query window or editor.<\/p> Now that SQL Server has stopped, we need to restart it by going to SQL Configuration Manager, Right Click on the SQL instance and Click Start.<\/p> With SQL Server running successfully and all databases ONLINE, including CrashRecoverySimulator. You can check database state with the code below.<\/p> Result<\/strong><\/p> Now let’s query the CRS table and see if the UPDATE we performed in the running transaction is available.<\/p> Result<\/strong><\/p> The UPDATE statement was rolled back hence the data in ColA still remains the same.<\/p> Logging serves a provision for a variety of operations in SQL Server.\nThere are so many reasons as to why logging is vital in any RDMS. A few are\npresented below:<\/p> The majority of these uses of logging involve a mechanism called recovery. Recovery is the procedure of having the changes found in log records restated or reverted in the database. Recovery has three phases: analysis, redo and undo. Database is completely accessible only after all three phases of recovery has completed. There is a feature in the enterprise edition called FAST recovery which allows the database to come online after REDO phase. Aside this, all these phases are required to happen before database can be in a consistent state.<\/p> Replaying log records is called the REDO (or roll forward) phase of recovery. Reverting log records is called the UNDO (or roll back) phase of recovery. In other words, recovery will make sure that a transaction and all its constituent log records are either redone or undone.<\/p> The simple form of recovery is when a single transaction is canceled, in\nwhich case it is undone and there is no net effect on the database. The most\ncomplex form is crash recovery—when SQL Server crashes (for whatever reason)\nand the transaction log must be recovered to bring the database to a\ntransactionally consistent point. This means that all transactions that were\ncommitted at the time of the crash must be rolled forward to ensure their\neffects are persisted in the database. And all in-flight transactions that had\nnot committed at the time of the crash must be rolled back to ensure their\neffects are not persisted in the database.<\/p> This is because there is no facility for a transaction in SQL Server to continue after a crash. Thus, if the effects of a partially complete transaction were not rolled back, the database would be left in an inconsistent state (possibly even structurally corrupt, depending on what the transaction was in the middle of doing). (Randal, 2009)<\/p> Did you look at your SQL Server error longs and notice the below information? You are possibly experiencing a slow database recovery process. The three mentioned phases talked above is occurring in this scenario and the possible ONLY solution is to WAIT for it to complete. The wait time can be exponentially long based of factors of size of database files, number of VLFs, and computer resources.<\/p> Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28890 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p> Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28595 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p> Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28499 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p> Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28202 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p> Recovery of database ‘CrashRecoverySimulator’ (6) is 77% complete (approximately 28067 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.<\/em><\/p> Query rollback operation is usually single threaded while when same query executes it can use parallelism\/multiple threads to perform same operation making it very fast as compared to the rollback. Even restarting SQL Server service is will not help much, as it will actually make it worst as the rollback process will restart again. Luckily there are some dmv’s that help give you an estimated completion time.<\/p> Sample:<\/strong><\/p> Result<\/strong><\/p> From the result, you can see that there is a background process running a DB STARTUP command which is at an estimate percent of 86% with an estimated completion time of 289093 milliseconds. Convert the time to minutes or hours to get a rough idea of when the process will complete.<\/p> In a case of failed recovery of the database, we would either have to restore the database from good known backups or attempt to repair the database. Allow me to introduce to you a tool that is trusted by Microsoft MVPs. Stellar SQL Database Repair<\/strong> is the most recommended SQL Recovery software<\/a> <\/strong>amongst database administrators. <\/p>Use Master\nGo\n--Create a new database\nCREATE DATABASE CrashRecoverySimulator\nGo\n<\/pre>
--Use it\nUse CrashRecoverySimulator\nGo\n--Create a new table\nCREATE TABLE CRS\n(\nColA VARCHAR(50) NOT NULL,\nColB VARCHAR(50) NOT NULL,\nColC VARCHAR(50) NOT NULL\n)\nGo\n--Insert a record\nINSERT INTO CRS VALUES\n(\nREPLICATE('A', 25),\nREPLICATE('B', 25),\nREPLICATE('C', 25)\n)\nGo\n<\/pre>--Begin a new transaction without committing it…\nBEGIN TRANSACTION\nUPDATE CRS\nSET ColA = REPLICATE('X',50)\nGO\n<\/pre>--Simulate a dirty shutdown of instance\nSHUTDOWN WITH NOWAIT\nGO\n<\/pre>
--Get query result of databases and their state\nselect name,state_desc from sys.databases order by name<\/pre>
--Use it\nUse CrashRecovery\nGo\n--The transaction was rolled back\nSELECT * FROM CRS\nGo\n<\/pre>
Want to know the INTERNALS?<\/strong><\/h2>
Why is my recovery taking so LONG?<\/strong><\/h3>
Select session_id, command,status, percent_complete, estimated_completion_time\nfrom sys.dm_exec_requests; \nwhere command IN ('killed\/rollback','rollback','db_startup')<\/pre> Session_id <\/td> command <\/td> status<\/td> percent_complete <\/td> estimated_completion_time<\/td><\/tr> 35<\/td> DB STARTUP<\/td> background<\/td> 86.06061<\/td> 289093<\/td><\/tr><\/tbody><\/table><\/figure> What happens if recovery FAILS?<\/strong><\/h4>
![\"free](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/04\/free-download-1-1.png\")
<\/a><\/figure><\/div>