Read time 5 minutes
SQL Server saves its data in MDF/LDF files. If the SQL Server gets crashed while performing a transaction due to any hardware or software failure, then the transaction will not be complete, and the next time may give you an error or the table will remain inaccessible. In such a case, you should run the DBCC CHECKDB command to check the health of the database and see the outcome. If the database could not complete the transaction and it is in the deferred state, then it will give an error:
Msg 7929, Level 16, State 1, Line 1
Check Statement aborted. Database contains deferred transactions
What is a deferred transaction?
A deferred transaction is an uncommitted transaction which did not run properly and failed to complete the roll-forward procedure. The deferred transaction occurs in enterprise SQL server due to a high number of transactions whereas a startup error occurs in other SQL server editions. It happens due to an input-output error that prevents reading a page needed for the transaction.
When the SQL Server was either abruptly started or stopped, SQL Server stops the rolling forward of transaction which requires data from the table for completion. Another reason behind a deferred transaction is that a partially complete restore process stops at a point where a transaction requires offline data and rollback is necessary.
Resolve the 7929 error from SQL server
The reason behind the Error 7929 is a deferred transaction, and if the user can fix the transaction, then the error will remove automatically. Let’s go through the process in detail:
- Restart the SQL Server, and if the reason behind the deferred transaction is temporary, then it will get sorted automatically.
- If the error was due to an offline filegroup, then bring the filegroup online by applying the command:
- Restore the database with the help of a recent backup. The backup will remove any deferred transaction and resolve it automatically. The online restore will also sort the issue of corrupt page.
- If the deferred transaction occurs due to the offline filegroup, then defunct the filegroup. The transactions related to the defunct filegroup will also become defunct, thus removing the deferred situation.
- If you did not take a recent backup or the deferred transaction occurs due to a bad page in the database, then you need to set the database at an emergency mode:
NOTE: You cannot recover a defunct filegroup.
Now run the repair command DBCC REPAIR_ALLOW_DATA_LOSS for the database along with DBCC CHECKDB.
NOTE: The repair command deallocates the corrupt pages and allows to recover deleted records in the SQL Server and achieves a consistent state. But it may result in some critical data loss that you cannot recover it. That’s why you should run the command when other methods are not working.
Whenever you face such error which stops you from accessing your database, and its manual recovery method is risky, then use Kernel SQL Database Recovery tool which doesn’t need any backup file to recover the database. This SQL Recovery tool allows to accesses the MDF/LDF files and removes any error to save database in full health.
Here is the total procedure of recovering the data from a corrupt SQL database file –
- Install and run the software. A wizard automatically opens up. Click the Browse button.
- After browsing the database file, choose the scan mode based on the severity of corruption. Also, check the box to detect the database version automatically. Finally, click Recover.
- The tool recovers the complete data and brings you the whole database along with all the objects as they were saved in the SQL Server. Check any object to get its preview. Click the Save button.
- To save the data on SQL Server, input the Server name, database name, and authentication method (the second option is to keep the offline batch file at any location). Click OK.
- The tool saves the data at the selected database, thus removes any deferred transaction. Click OK.
Conclusion
Kernel SQL Database Recovery software is a light-weighted and minimalistic software which can access SQL database from any version and remove any corruption or error. It saves you from making any manual troubleshooting, which can cause further deletion of the data.