Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
This error occurs because the transaction log becomes full due to LOG_BACKUP. Therefore, you can’t perform any action on this database, and In this case,
the SQL Server Database Engine will raise a 9002 error.
To solve this issue, you have to do the following:
- Take a Full database backup.
- Shrink the log file to reduce the physical file size.
- Create a LOG_BACKUP.
- Create a LOG_BACKUP Maintenance Plan to take backup logs frequently.
Note: The Shrink operation effects on SQL Server Performance during executing shrink command. it also causes index fragmentation and can slow the performance of queries that search a range of the index.
So, it’s recommended before goes live you should prepare LOG_BACKUP Maintenance Plan to back up the log file frequently to avoid the shrink operation on Production.
For more details check The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP
Hope this helps you
Best Answer
You may want to run the deletes in smaller chunks to avoid excessive log file growth for this one-time cleanup, particularly because your
msdbdatabase is on the C:\ drive.If you have 5 years of data to clean up, the easiest way would likely be to simply call
sp_delete_backuphistoryseveral times, to clear out the backup history in 6 month or 1-year chunks.For example:
Simply by running this and experimenting with the
@oldest_date, you could pretty easily determine how quickly you can chunk through. Depending on the number of databases and frequency of backups, doing yearly chunks will likely be sufficient. If this is a one-server, one-time cleanup, this would be the method I use.You could also script out the definition of
sp_delete_backuphistoryand create your own copy of it that does the cleanup more efficiently. Erik Darling has created an alternative that is available on GitHub distributed as part of my own DBA Database project. I would use this option for ongoing/long term cleanup, and if the problem is more pervasive than a single server needing a one-time cleanup.