our Teamcenter (Siemens' software) oftens runs transactions for hours, even days. As if they were forgotten. There easily are even 20+ transactions running longer than an hour, even 20 hours. They fill temp db enormously and usually do not terminate on their own (must be killed). Our Teamcenter integrator says that it is OK, because there are "translation" processes that take a long time. But is it correct to let a transaction run for so long?
Thank you
There easily are even 20+ transactions running longer than an hour, even 20 hours.
A transaction running for that long is a sign that something is badly wrong, usually with the transaction itself (coding) but, sometimes, with the underlying table structures, indexing and all the other "Fun" stuff that we DBAs play with.
They fill temp db enormously and usually do not terminate on their own (must be killed). Our Teamcenter integrator says that it is OK
No, it's not "OK".
When a transaction is killed, it gets rolled back, leaving the database in a state as if the transaction had never happened, so whatever work they think is happening, isn't!
For any process to take this long suggests that it's might be doing a lot of updates, which hints at poorly normalised data structures or might be the use of complex, "document" data types, like JSON, that require a ton of processing power to rewrite them, every time any part of them is updated.
Send the Application team back to the Drawing Board and get them to come up with a better way of doing this.
how long should an sql-server transaction run at most
While Dan is correct, the way your question is worded leads it to be potentially answered a little subjectively (which unfortunately makes it not very fitting for this site), I think the following is objective enough of an answer:
It's generally best practice to hold transactions open for the least amount of time possible that is necessary to complete the workflow of a task. The shorter (and obviously less frequent) occurrences of transactions will typically lead to a better performing system overall, when concurrency is involved.
A system with concurrency (e.g. multiple users) where transactions are held open for long periods of time, runs the risk of increased blocking on the objects that are being transacted on, since the transaction will lock access to that object.
Even worse with long running transactions, is if they are rolled back either due to an error or when their process is killed (like you mentioned you are already doing). The rollback always is (mostly) single-threaded, which can lead to the rollback taking significantly longer to occur than the transaction process has taken so far (still keeping the objects within the transaction locked throughout the entire rollback).
A transaction taking an hour to run is not something I would be a fan of personally (but not unheard of). I try to keep my own transactions as quick as possible (few seconds at most). A transaction being held open for days is definitely not normal / commonplace. But if the system you're using doesn't support concurrency and only permits one user at a time, then at least it only affects you (the current user) who is waiting on such slow processes to finish anyway.
In my opinion, it sounds like your Teamcenter software is inefficiently written.
