Change recovery mode and enforce a transaction log backup?

Question

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.

Reasons:

1. Remove no more required rows including the (blob) data
2. Improve performance
3. 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

I checked 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.

So, questions:

1. Would you agree to the procedure in general?
2. How can I enforce the Log Backup?
3. Is the log backup enough or is a log shrink required as well at any time?

Answer 1

1. Yes in general that is a good approach.

2. As Shanky commented if you take a manual backup then the chain within BackupExec will be broken.

   If you have the ability to kick all the users on the server and can wait for the transaction log to be backed up by BackupExec then you will find it easier to start the work without involving another team. Otherwise you will want to get the backup team to take the log backups before and after you have shrunk the data.

3. Only Shrink the database if you really have to reclaim the disk space otherwise leave it as it is. Brent Ozar has a lovely rant on shrinking, especially when scheduling it here and below is an excerpt from Paul S. Randal blog. The long and short of it is that it takes processing power to grow and shrink a log, this can be an unnecessary burden on an SQL Server.

   Shrinking the log is necessary if your log has grown out of control, or as part of a process to remove excessive VLF fragmentation (see Kimberly’s excellent posts on this here and here). However, shrinking the log should be a rare operation and should not be part of any regular maintenance you perform.