Tuesday, 13 May 2014

Automating Sliding Partition Windows - Part 3

It's been a long while since I've worked on this - and believe me I want to as every month I'm spending too much time manually sliding my various partitions! Which is very prone to mistakes.

In the meantime I found a really good post describing an automated sliding partition window that achieves something a little different. It works on the following premise:

  • 7 day partitions that, at the end of each week, are merged into
  • 5 weekly partitions that are then merged into 
  • 6 monthly partitions that are finally merged into 
  • 1 large archive partition

I'm looking at implementing something like this in my company's OLAP database and combined with SQL Server 2012's columnstore indexes I am hoping to get some great performance improvement!

Here's the post:

Tuesday, 4 March 2014

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.

You can find the stored procedure I wrote in my post An SP to Fix Logshipping After Adding a New Database File. See the screenshots below for details on adding the step to the logshipping restore job.
  • In SSMS expand the SQL Server Agent Jobs tree and double-click on the LSRestore_xxxx job
  • Click on 'Steps' and then click 'New'
  • Add a title, select the database the stored procedure has been deployed to and then enter the EXEC statementinto the 'Command' box, for example: EXEC dbo.LogShippingAutoRestore @DBname = 'AdventureWorks',@Filepath = 'J:\Logshipping\AdventureWorks\'
  • Click on the 'Advanced' tab and modify the 'On success action' to 'Quit the job reporting success' and click 'Ok'

  • Select step 1 and click on 'Edit'
  • Click on the 'Advanced' tab and modify the 'On failure action' to 'Go to step: [2] exec LogshippingAutoRestore' or whatever you named that step and click 'Ok'
  • Now finally, click ok to save the job

Friday, 10 January 2014

An SP to Fix Logshipping After Adding a New Database File

UPDATE: 22 Jan 2014 - Fixed some bugs, added multi-file support and added a few notes

Every month I need to maintain the various sliding partition windows on several databases. If you've read some of my previous posts you may remember that this involves adding new files for the upcoming partitions. My company's databases are also logshipped as part of the disaster recovery strategy.

What invariably happens every month is I add new files make the necessary partition changes and then wonder why, a little later in the day, I'm receiving alerts for failed transaction log restores. The logshipped database obviously doesn't "know" about the new file and I end up with an error message like this:

2014-01-09 08:10:18.12 *** Error: Could not apply log backup file 'C:\SQL\Logshipping\PartWindow_20140109080941.trn' to secondary database 'PartWindow_ls'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 08:10:18.12 *** Error: The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\logshiptest.ndf' cannot be overwritten.  It is being used by database 'PartWindow'.
File 'logshiptest' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\logshiptest.ndf'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2014-01-09 08:10:18.16 *** Error: The log backup file 'C:\SQL\Logshipping\PartWindow_20140109080941.trn' was verified but could not be applied to secondary database 'PartWindow_ls'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 08:10:18.16 Deleting old log backup files. Primary Database: 'PartWindow'
2014-01-09 08:10:18.16 The restore operation completed with errors. Secondary ID: '32ef56ad-d8ee-4437-b3a0-e5658f6d2d4b' 

Fixing this is not difficult, it is just a bit fiddly. There is a really good post on mssqltips.com explaining the steps necessary to fix the problem. Basically, I've gotten bored by it all and decided to write a stored procedure that can more easily fix the problem.

You need to pass the logshipped database name and the file path to where the transaction log backups are on the failover server. I imagine the bast place for this stored proc is either on the msdb database or an admin database on the failover server. You also need xp_cmdshell to be enabled.

The stored procedure performs the following steps:
  • Identifies the last file that was successfully restored
  • Identifies the subsequent file - the first file to fail
  • Identifies the new logical file name
  • Identifies the physical location of the most recent database file
  • Creates and executes the restore log script with the appropriate WITH MOVE clause
  • Will move any number of files that have been added to the source database
CREATE PROC dbo.asp_LogShippingRestoreNewFile
      @DBName SYSNAME ,
      @Filepath VARCHAR(260)
        SET nocount ON
        DECLARE @FailedLogFile NVARCHAR(260)
        DECLARE @LastRestoredFile NVARCHAR(260)
        DECLARE @LogicalName SYSNAME
        DECLARE @PhysicalName NVARCHAR(260)
        DECLARE @MoveToLocation NVARCHAR(260)
        DECLARE @dbfilelist TABLE
              LogicalName VARCHAR(256) ,
              PhysicalName NVARCHAR(260) ,
              Type CHAR(1) ,
              FileGroupName VARCHAR(256) ,
              Size NUMERIC (25,0) ,
              MaxSize NUMERIC (25,0) ,
              FileId NUMERIC (25,0) ,
              LSN NUMERIC (25,0),
              DropLSN NUMERIC (25,0),
              UniqueID UNIQUEIDENTIFIER ,
              ReadOnlyLN NUMERIC (25,0),
              ReadWriteLSN NUMERIC (25,0),
              backupsize NUMERIC (25,0) ,
              sourceblocksize INT ,
              FileGroupID INT ,
              LogGruopGUID UNIQUEIDENTIFIER ,
              DiffBaseLSN NUMERIC (25,0) ,
              DiffBaseGUID UNIQUEIDENTIFIER ,
              isreadOnly BIT ,
              IsPresent BIT ,
              TDEThumbprint VARBINARY(32)
        DECLARE @CopiedFiles TABLE
              id INT IDENTITY(1, 1) ,
              filepath VARCHAR(260)

        SELECT  @LastRestoredFile = last_restored_file
        FROM    msdb.dbo.log_shipping_secondary_databases
        WHERE   secondary_database = @DBName
        SET @sql = 'xp_cmdshell ''dir "' + @Filepath + '" /b /s /o'''
        INSERT  INTO @CopiedFiles
                EXEC ( @sql
        SELECT TOP 1
                @FailedLogFile = filepath
        FROM    @CopiedFiles
        WHERE   id > ( SELECT   id
                       FROM     @CopiedFiles
                       WHERE    filepath = @LastRestoredFile
        ORDER BY id

        INSERT  INTO @dbfilelist
                EXEC ( 'RESTORE filelistonly FROM DISK = N''' + @FailedLogFile + ''''
              SELECT  @PhysicalName = LEFT(physical_name,
                                     LEN(physical_name) - CHARINDEX('\',
                                                              1) + 1)
        FROM    sys.master_files AS mf
                JOIN sys.databases AS d ON d.database_id = mf.database_id
        WHERE   d.name = @DBName
        ORDER BY file_id DESC
              SET @sql = (SELECT TOP 1 'RESTORE LOG ' + @DBName + ' from disk = ''' + @FailedLogFile
                                  + ''' WITH' + substring((SELECT ', MOVE ''' + a.LogicalName + ''' TO ''' + @PhysicalName + a.LogicalName + '.ndf'', NORECOVERY'
                                  FROM @dbfilelist AS a
                                  WHERE a.Type  = b.TYPE
                                  AND a.LogicalName NOT IN (SELECT mf.name FROM sys.master_files AS mf
                                  JOIN sys.databases AS d ON mf.database_id = d.database_id)                 
                                  FOR XML PATH('')),2,65536) AS a1 FROM @dbfilelist AS b

              PRINT @sql
        EXEC (@sql)


 Feel free to let me know if this has been helpful and how it has worked for you.