Space occupied by each Transaction in Log

• rao_dinesh

  Grasshopper

  Points: 15

  More actions

  December 18, 2015 at 11:20 pm

  #306346

  Hi, is there a way to find space occupied by each transaction in tran log. CDC is holding up the log and I want to know what is the size of each transactions (most of these tran would be commited but they could be large and I don't find anything when I query DM views) in active portion of the log. if we have lot of large transactions in the log would it make sense to reduce CDC Maxtrans setting from 1000 trans to say 500 or even less, may be 50 or 100(especially during batch processing) to avoid filling up 300GB log?

• JeremyE

  SSCoach

  Points: 16199

  More actions

  December 21, 2015 at 2:31 pm

  #1847274

  Sounds like sys.dm_tran_database_transactions is what you are looking for.

  https://msdn.microsoft.com/en-us/library/ms186957.aspx%5B/url%5D