Modify Logshipping Restore Job

In my previous post I shared a stored procedure that dynamically creates and executes a restore log script in order to ease the recovery of logshipping after one or more files have been added to a logshipped database.

As I described in that post I was running into this problem on a monthly basis due to a sliding partition window scenario I maintain on several databases. After I wrote the stored procedure it was a lot easier to fix the problem after new files were added. But I still needed to wait for the logshipping restore job to fail and then intervene and execute the SP manually to recover from the failure. Being busy, not to mention forgetful, there were times when logshipping would be broken for several hours at a time.

Unfortunately, you cannot pro-actively inform the failover server that new files are on their way. But what finally did occur to me was to modify the job that restores the transaction log to include the stored procedure. In short, I added a second step that is run only if the first step fails. It works a treat.

Quick Cheat to Shrink Database Log Files

This is a quick way to reduce the size of a database log file. As a caveat to beginners, this should not be used on mission critical db systems. It is intended as a quick and dirty fix for non-essential systems.

For those not familiar with the Attach and Detach commands:
--You need to make sure the database is not being used by anyone else/any other process. That is why there is a set single_user statement.
--If your database has multiple files/filegroups, you need only specify the primary file in the create statement (if you haven't made any file modifications after detaching).
-- You may need to enable xp_cmdshell therefore I've included the sp_configure statements.