Post reply

TempDB growth due to version store on AlwaysOn secondary server

  • November 6, 2016 at 10:31 pm

    #331782

    Comments posted to this topic are about the item TempDB growth due to version store on AlwaysOn secondary server

  • November 7, 2016 at 12:12 am

    #1910836

    Great article, well written. I'd hope for something this clear if I was on call.

    Any idea what caused spid 484 to hang around?

    LinkedIn Profile

  • November 7, 2016 at 7:02 am

    #1910910

    I believe at one of the Microsoft sessions here in St. Louis it was also mentioned that statistics for querying the RO node also was stored in tempdb

  • November 7, 2016 at 7:35 am

    #1910921

    David.Poole (11/7/2016)

    Great article, well written. I'd hope for something this clear if I was on call.

    Any idea what caused spid 484 to hang around?

    That was going to be my question, as well. It would seem that, whatever that was, was the cause of this problem. That means the root cause of this problem is knowing what SPID 484 was doing, what caused it to hang, and how to prevent it from happening again.

    --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)

  • February 18, 2017 at 12:15 pm

    #1929249

    I'm having the same issue but couldn't find the exact spid that caused the tempdb growth. i killed the sleeping sessions.

    Also, we used to get corrupted tempdb stats on read only databases in always on group. every time we get that issue, we have to drop the corrupted stats and query optimizer will re-create them again. We reached out to Microsoft support and they are working on a bug fix.

    But for version store space growth in tempdb , i'm not sure if that was because of temporary stats ? Also, there is only one database that has RCSI enabled out of 15 other other databases that are part of always on. I have just enabled RCSI on 3TB database yesterday because of blocking issues. not sure how this is going to behave, although we have more than a TB space on tempdb but still after looking at the version store space yesterday (growth was 750GB), i'm kind of concerned. 

    Any help on this is greatly appreciated.

    thanks

  • February 18, 2017 at 11:51 pm

    #1929259

    Robin35 - Saturday, February 18, 2017 12:15 PM

    I'm having the same issue but couldn't find the exact spid that caused the tempdb growth. i killed the sleeping sessions.

    Also, we used to get corrupted tempdb stats on read only databases in always on group. every time we get that issue, we have to drop the corrupted stats and query optimizer will re-create them again. We reached out to Microsoft support and they are working on a bug fix.

    But for version store space growth in tempdb , i'm not sure if that was because of temporary stats ? Also, there is only one database that has RCSI enabled out of 15 other other databases that are part of always on. I have just enabled RCSI on 3TB database yesterday because of blocking issues. not sure how this is going to behave, although we have more than a TB space on tempdb but still after looking at the version store space yesterday (growth was 750GB), i'm kind of concerned. 

    Any help on this is greatly appreciated.

    thanks

    Hi Robin,

    which version of SQL Server are you using ? I have not seen version store growing huge because of temporary readonly stats in our servers so far. Have you checked the longest transaction running time - performance counter ? Is it showing a bigger value by any chance ?

    Thanks,
    Ramya

  • February 18, 2017 at 11:58 pm

    #1929260

    ramya.sqlservr - Saturday, February 18, 2017 11:51 PM

    Robin35 - Saturday, February 18, 2017 12:15 PM

    I'm having the same issue but couldn't find the exact spid that caused the tempdb growth. i killed the sleeping sessions.

    Also, we used to get corrupted tempdb stats on read only databases in always on group. every time we get that issue, we have to drop the corrupted stats and query optimizer will re-create them again. We reached out to Microsoft support and they are working on a bug fix.

    But for version store space growth in tempdb , i'm not sure if that was because of temporary stats ? Also, there is only one database that has RCSI enabled out of 15 other other databases that are part of always on. I have just enabled RCSI on 3TB database yesterday because of blocking issues. not sure how this is going to behave, although we have more than a TB space on tempdb but still after looking at the version store space yesterday (growth was 750GB), i'm kind of concerned. 

    Any help on this is greatly appreciated.

    thanks

    Hi Robin,

    which version of SQL Server are you using ? I have not seen version store growing huge because of temporary readonly stats in our servers so far. Have you checked the longest transaction running time - performance counter ? Is it showing a bigger value by any chance ?

    Thanks,
    Ramya

    Also, below query can help find the current active transactions that are using the version store currently.

    select CONVERT (varchar(30), getdate(), 121) AS runtime,a.*,b.kpid,b.blocked,b.lastwaittype,b.waitresource,b.dbid,b.cpu,b.physical_io,b.memusage,b.login_time,b.last_batch,b.open_tran,b.status,b.hostname,b.program_name,b.cmd,b.loginame,request_id
    from sys.dm_tran_active_snapshot_database_transactions a
    inner join sys.sysprocesses b
    on a.session_id = b.spid

  • September 30, 2021 at 6:50 am

    #3934922

    Thanks for sharing troubleshooting TEMPDB growth in database setting in RCSI.

    I checked TEMPDB row versioning is 160GB but  row versioning  cleanup showing zero values always in performance monitor. checked oldest transaction time.  found 10 days back SPID and status is SLEEPING. more than 1000 records.

    SPID : 483 , Login time: 17-09-2021 16:38:36, last batch: 19-09-2021 17:59:01.  does it means the same SPID reuse on 19-09-2021?. or session not cleared at SQL instance side? if KILLED that SPID again the same session will created with new SPID.

    This is not Alwayson setup.

     

    • This reply was modified 3 years, 2 months ago by  SQL Galaxy.

  • July 31, 2023 at 1:48 pm

    #4249219

    Thanks for sharing the troubleshooting steps of version store.

    I had the same issue yesterday, where i have seen version store reaching big numbers. I didn't understand how to check on this. This article which helped me to find understand what version store and deal is with it.

    I found the sleeping session and it was lying since two days. I killed it. Version store cleansed  its space.

    But I have a question, appriciate if someone anwers.

    My database is not RCSI enabled how come it can use version store?

  • July 31, 2023 at 1:50 pm

    #4249220

    Thanks for sharing the troubleshooting steps of version store.

    I had the same issue yesterday, where i have seen version store reaching big numbers. I didn't understand how to check on this. This article which helped me to find understand what version store and deal is with it.

    was lying since two days. I killed it. Version store cleansed  its space.

    But I have a question, appriciate if someone anwers.

    My database is not RCSI enabled how come it can use version store?

Viewing 10 posts - 1 through 9 (of 9 total)

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