Post reply

AlwaysOn Readable Secondary - Query Failure

  • January 19, 2017 at 12:45 am

    #334116

    Hi All

    I'm running a 2 node cluster with AlwaysOn. The database on the secondary server as readable.

    A query has been running fine since the implementation until today where it failed with the message "Transaction aborted when accessing versionedrow in table X in database Y. Requested versioned row was notfound because the readable secondary access is not allowed for the operationthat attempted to create the version. This might be timing related, so try thequery again later. [SQLSTATE 42000] (Error 3949).  The step failed."

    The query ran successfully when it ran again without any changes (no restarts or pausing of alwayson)...just merely ran the query again and it completed fine.

    Found some blogs that speak about Tempdb size...the current size is 120GB.

    Furthermore...the error number report is 3949.  Errors related to Tempdb and row version are either 3959 or 3967, so doesn't seem like a Tempdb issue to me.

    Any assistance will be greatly appreciated.
    Thanks  

  • January 19, 2017 at 9:11 am

    #1923754

    how much of the 120GB is in use at any one time

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 23, 2017 at 5:55 am

    #1924377

    Hi Perry

    Unfortunately I'm not sure on the usage as the query is in a SQL job that runs in the early hours of the morning.

  • January 26, 2017 at 9:53 pm

    This was removed by the editor as SPAM

  • January 26, 2017 at 10:14 pm

    #1925229

    Thanks for the response.

    Will look into increasing Tempdb

  • January 27, 2017 at 5:08 am

    #1925281

    Denesh Naidoo - Thursday, January 26, 2017 10:14 PM

    Thanks for the response.Will look into increasing Tempdb

    why not setup a monitor to track the database space usage to gauge what you may need

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 27, 2017 at 6:44 pm

    #1925463

    Denesh Naidoo - Thursday, January 26, 2017 10:14 PM

    Thanks for the response.Will look into increasing Tempdb

    A much better thing to do is to find out why it needs to be so large to begin with and then fix those things.  I have several databases on the same instance that are each approaching the 1TB mark in size but my tempdb is only 8*2 (16GB total) with only a 2GB log file and I've never had to shrink it.  Something is seriously wrong with the code, possibly in the form of accidental Cross Joins that most people refer to as "many-to-many" joins.  Such code usually has one or more DISTINCTs in it to make up for the duplication of rows cause by such things and can usually be found on large queries that have lots of table joins but can be caused by even a bad join between just two tables.  "Triangular Joins" are also a frequent villain and you can read about those in the following article.  Hidden RBAR: Triangular Joins

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply