I am going to do a special data deletion operation - once and during a maintenance with no user access (Sql 2008 r2).
The result is a large deletion of millions of blob data from a table by re-creating the whole table.
- Remove no more required rows including the (blob) data
- Improve performance
- Reduce data file size (Regarding 3.: shrinking is not yet a part of the maintenance procedure, maybe later on...)
I plan to change the recovery model to
bulk_logged to minimize logging and back to
full after the maintenance.
The main operation performed is a
SELECT INTO statement followed by creation of (non + clustered) indexes and constraints, defaults + truncating the original table + renaming of the new table.
I have read the suggestions at Considerations for Switching from the Full or Bulk-Logged Recovery Model.
There we are advised to run a Log Backup before and after the process. Unfortunately the regular (15 Min interval) log backup is done by third party software (backupexec). I have no control about it. When I tried to do a log backup using T-Sql I failed because there is
no device present
sys.backup_devices => nothing.
I want to do it by script because I have to do it on several different servers and don't want to do it in a user interface.
- Would you agree to the procedure in general?
- How can I enforce the Log Backup?
- Is the log backup enough or is a log shrink required as well at any time?