June 4, 2011 at 5:49 am
Ninja's_RGR'us (6/4/2011)
GilaMonster (6/4/2011)
With normal row versions the version can't be removed until all queries that could possibly need it have finished. May be similar with triggers, I'm not sure.I don't see how it could be any different with triggers. The only difference I see is that you only have 1 procedure to wait on with triggers instead of possibly infinity for "normal" row version.
What I'm not sure about is if the trigger's versioned rows are treated like normal versioned rows and require ALL queries that started before the versioned rows were added to be finished before they can be removed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2011 at 10:48 am
Strangely what I can see here in my case is, its getting stuck everytime in case of one particular table which has total around 70k records and out of which its trying to delete just 30k records. Don't know hy it's getting stuck at same point of time.
June 6, 2011 at 12:12 pm
You need to find out the oldest transaction sequence number associated with a version store. The version store is maintained till no associated open trans are attached to it. And subsequent stores will also be maintained till the version stores prior to it are cleaned up.
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
June 7, 2011 at 12:33 am
There is a varbinary datatype as well. Can this be the issue for filling up the tempdb ?
June 7, 2011 at 2:22 am
No more than any other data type, depending on the size. If it's VARBINARY(MAX) with GB-sized files stored in, then you're looking at GB of storage per row. There's nothing special about varbinary.
Check the blog post that troubleshootingsql posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply