14 September,2014 by Tom Collins
When a database is in Simple Recovery Mode , log space is automatically reclaimed when a transaction finishes. This means log space management is not required .
In the event of database loss , only data from the last back is recoverable. Any data since the last backup is lost, and needs to be recreated.
If the recovery Point required is different, change the recovery mode to FULL and take log backups. Regular log backups will ensure a data file loss does not mean a catastrophic failure, but only data loss up to the last log backup. Recovering a database to a specific point in time is critical to some applications , such as financial data systems.
I review databases as part of my daily routine, I check for databases in Full Recovery Mode with no log backups. I speak to the application owner to establish the recovery point. If the recovery point is last full backup , there is an option to use Simple Recovery . If the recovery point after the last full backup , a log backup schedule is set up to support the recovery point
Establishing a recovery point is part of creating a Disaster Recovery Plan
Factors that can delay Log Truncation – LOG BACKUP
SQL Server - How to find Open Transactions
SQL Server SLEEPING MODE , locks and transactions
MS DTC and sys.dm_tran_active_transactions
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |