Monthly Archives: November 2013

Why log shipping backups fail in an Always On Availability Group – Resolved

Sometimes it bother me how long it takes to throw a single switch which resolves a problem.  This was one of those times.

Here’s the scenario.  An availability group is set up and you want to log ship one of the databases to a designated DR server so you have both HA and DR at your disposal.  Everything sets up just as it should, but when you run the backup no file appears in your backup directory.  What’s going on?

For a while I went around in circles trying to second guess my log shipping setup.  Then I made a second database, successfully configured log shipping, but the moment I added it to the availability group it began exhibiting the same behaviour.  There are no errors in the logs, the backup job says it’s successful, but when you go into the error logs all you find is it is deleting old backups that have fallen out of retention.  No backup is made.  The databases last backup date does not change.

I went so far as reading the entire support notes on books online around availability groups trying to find anything to suggest this couldn’t be done.  Then I repeated my experiments to confirm that log shipping worked fine outside the availability group, but failed when it was added.  Finally I came to the conclusion that should have been obvious an hour earlier.  The problem must be something to do with the group set up.  Once I got there the likely culprit was clear – the availability group was trying to enforce a backup being taken off the secondary replica, and the log shipping job didn’t like that.  It seems to check if it can take a local backup, see that it can’t and so skip straight to the historic backup purge.

Here’s how to correct it.

  1.  In management studio expand Always On High Availability and then Availability Groups.
  2. Right click on your chosen group and choose properties.
  3. In the left pane select backup preferences.
  4. Select ‘Primary’ as your backup source.  Click Okay.

Now run your log shipping log backup job and everything should perform as expected.

 

How to be a programmer………

This article was linked to from a recent Brent Ozar PLC newsletter.  It contains a bunch of tips about how to be a programmer(Not how to program, but how to be a programmer – the distinction is important).  I think it was absolutely brilliant and has huge areas of crossover to other technical roles…such as DBA’s.

 

(Dead Link)http://samizdat.mines.edu/howto/HowToBeAProgrammer.html?x#id2855381(Dead Link)

I particularly liked the sections on how to deal with difficult people, how to provide estimates, and perhaps best was knowing when to go home:)

How to Find Which Databases Have Indexes With Page Level Locking Disabled

I’ve recently had to look over a client server where a maintenance plan had been failing for some time.  The inhouse DBA was unaware of the failures because he had no alerting on the jobs and the big red crosses weren’t big or red enough to have caught his attention.  When I looked at the cause of the failures it was due to a native maintenance plan not being able to reoganise an index with page level locking disabled.  Whenever a reorganise plan hits a database with page level locking disabled it generates the following error:

Executing the query “ALTER INDEX Indexname] ON [dbo]….” failed with the following error: “The index “Indexname” (partition 1) on table “tablename” cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

There are two ways of getting around this.  The first way is to generate TSQL scripts to enable Page level locking prior to the re-organise and then re-enable it after the maintenance is completed.  JP Chen has the scripts to do that here.  Note:  Haven’t tested those, but they look right.  I suspect you may need to exclude the system tables as well.

The second, and easier way, is to set up a rebuild step and move the affected databases into that step of your maintenance plan.  Rebuilds do not require a page lock because the index is completely rebuilt rather than shuffled around.  The TSQL to figure out which database you need to move from the re-organise to the rebuild step is:

exec sp_MSforeachdb ‘use ? select db_name(), (case when count(*)>0 then ”Page Locks Disabled:” ELSE ”Page Locks” END) from sys.indexes where allow_page_locks=0 and name not in(”queue_clustered_index”,”queue_secondary_index”)’

and if you want to get the specific indexes you can use this:

exec sp_MSforeachdb ‘use ? select db_name(), object_id,name from sys.indexes where allow_page_locks=0 and name not in(”queue_clustered_index”,”queue_secondary_index”)’

Standard disclaimer about sp_MSforeachdb being an undocumented stored procedure and maintenance plans being somewhat limited form of doing your index mainteance apply:)