SQL Server – Changing Recovery Model from Full to Bulk Logged whilst a Tranasction is active.Posted: May 10, 2017 Filed under: Microsoft SQL Server, Microsoft SQL Server 2016, Uncategorized | Tags: Microsoft, Microsoft SQL Server, Microsoft SQL Server 2016 Leave a comment
If have a SQL Server database and change the Recovery Model from Full to Bulk Logged whilst a transaction is open what happens?
If the ongoing transaction which was started under Recovery Model Full does an operation which can be minimally logged what happens?
Does the operation become minimally logged which then means the VLF is tagged as a minimally logged logfile which then does not allow certain operations e.g. restores from the next log backup with STOPAT.
We test with STOPAT and also use fn_dump_dblog to see exactly what ends up in the log backup and how we can identify minimally logged operations in a log backup file.