Very large t-log with backups

Question

Very large t-log with backups

Viewing 4 posts - 16 through 18 (of 18 total)

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

Greg Goss SSCrazy Points: 2160 More actions · Answer 1

Looking at the data, the B2GRT column can only have 3 values ('','Y','Z'). So the default value seems to be enforced by the app and is an empty string, not a NULL. So, I can check with our ERP vendor to see if that column can be changed to a char(1).

I also can't understand why there would be an index on B2GRT, so I can ask if that's important to the App.

Now onto the indexes! I never picked up on the PK containing an identity column. I do know that non-trivial amount of queries are written using B2VNR as a filter (they use it as the transaction number).

B2PLT (Plant Number) is another column that is filtered on quite a bit. We have several plants and a "cost reset" is performed against one plant at a time. B2BNR isn't that important in most cases. that's company number. If we had multiple business units on one ERP system, it would matter, but that value in TXMYBW00 is always '001'. So it wouldn't need to be included. Again, I'd need to ask though.

B2ANR is the product number. I can see that being used quite a bit.

B2DAT is the transaction date in yyyymmdd format.

I would say that the most selected columns would be B2VNR, B2DAT, B2PLT, B2ANR in no particular order. So as long as the ERP vendor doesn't think it's a big deal, I can start playing with your ideas in the dev environment (more likely play with your ideas while I wait on them to respond because I'm a rebel like that). Then if the non-clustered indexes look like they aren't being used I can drop them.

I really appreciate the insight Jeff! This has been a learning experience.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Thank you for the thoughtful reply. Indexes can be a load of "fun", especially when they belong to 3rd parties that have such things a VARCHAR(1) and other datatype attrocities.

You might also want to check sys.dm_db_index_usage_stats. You might be amazed at how many indexes aren't actually being used in 3rd party apps. I never delete such indexes even if I have permission to. I'll just disable them and then only if they aren't enforcing UNIQUEness.

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

Greg Goss SSCrazy Points: 2160 More actions · Answer 3

As a closure to this conversation, I think I've been able to isolate where my growth is coming from. While the index issues mentioned are present and need to be addressed, that wasn't the root cause of the growth! As it turns out, our ERP system sometimes has what should be very small transactions that would stay open for days or even a week (They show as implicit transactions when I run opentran). Even though I am performing tlog backups, the tlog isn't truncating because of those active transactions, causing growth that eventually fills the disk. Because I don't have access to the ERP system code, I'm having to work with the vendor to work through the issue.

Thank you for everyone's input on this!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Wow. Just WOW! Thanks for the feedback on what you found, Greg.

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