0

I have a fundamental question but unable to find precise information online.

I opened up a DB transaction (SQL Server) and making multiple inserts, updates, delete etc; the entire process takes about 5 hours to complete (Due to the volume of Data; Assume this is valid).

All the above happens within the scope of a transaction. Will this longer transaction make calls to other DB on the same server wait OR disturb them in anyway? My understanding is, this will impact only the calls made to the concerned DB and NOT other databases under the same instance.

PS: Transaction is initiated from C# side (i.e. using TransactionScope)

CodeMad
  • 950
  • 2
  • 12
  • 30
  • 1
    If you're doing heavy operations for 5 hours, it will definitely have impact on all the resources that are shared between the databases, like CPU, memory and possibly disc depending on your setup. Also, transaction log can grow quite huge as a result. – James Z Jul 28 '15 at 14:29
  • 1
    Once, I tried to insert 600+ Million rows in a single transaction and due to it Ram usage, CPU usage went very high and it even dropped the free space on Disk from about 200 GB to few MB and then it failed giving "no space left to allocate" message after few hours. Due to this transaction, queries on other DB which normally took few secs; took very long to run. So, I think I can safely say if your transaction is big enough it will affect calls to other DB's as well under the same instance. – Abhay Chauhan Jul 28 '15 at 14:29
  • Thanks JamesZ, AbhayChauhan for your inputs. – CodeMad Jul 28 '15 at 15:02

1 Answers1

0

first at all... 5 hours with a transaction active is too much time... you can validate if its posible that you make more transactions with less time processing... you can affect performance from others DB because are in the same Server and you make queries to them... remember that in older versions of MSSQL RAM consumption is a issue and this RAM has not auto release... If this process is made from a programming language you can do this.. in other case, you can schedule in Jobs to make some stuff periodical and not just for one time and 5 hours...

Hope this solve your doubt!

Fernando Canul
  • 158
  • 4
  • Thanks for your inputs; I understand 5 hrs is too long. But I'm trying to help a system that's already developed. Getting out of txn, and using snapshot isolation is possible though but the system is developed so much and proposing a change at this time may not be good idea (if at all it's not possible to run longer txns, then we may scrap this approach and go with something else). Also splitting them to many smaller txns is not an option because we would like to rollback the entire txn in the event of any error. PS: Txn is made from C# (not initiated from SQL Server) – CodeMad Jul 28 '15 at 19:17