SQL Server Performance

DTS and transactions

Discussion in 'SQL Server DTS-Related Questions' started by haylo75, Aug 1, 2007.

  1. haylo75 New Member

    Hi all,
    I've been reading up on DTS and its transactional capabilities and have a quick question regarding Package vs. Step transaction settings. Let's assume these parameters:
    • DTS Package with "Use Transactions" and "Commit on successful package completion" boxes checked.
    • No transaction related boxes checked in the Workflow Properties of each Package step.
    Given these parameters, will SQL Server still treat the package as a single transaction, or will each qualified step (Execute SQL Task, etc.) be executed as its own autonomous transation?
    Thanks!
    haylo75
  2. satya Moderator

    Transactions within DTS packages serve the same purpose as in other SQL Server based operations - they provide the set of features described collectively using the term ACID, which is an acronym derived from four transactional characteristics - atomicity, consistency, isolation and durability.
    See further information on this DBJournal article.
  3. haylo75 New Member

    Thanks, though I'm familiar with transactions in general and I'd read that article before posting. It doesn't answer my specific query as it deals with checking the "join to transaction" box within a given task. My question was if you do not check that box on any tasks in the DTS package, what happens in relation to transactions when they are enabled on the package level?
    I at first deigned to get into the particulars of our issue, but at this point I will. We've inherited a DTS on our SQL Server with well over 75 steps running all based on success of the prior step. 90% of the steps are Execute SQL tasks while the remainder are Transform Data tasks pulling in data from a remote DW. Our DBA has said our logfile drive's percent free space has been approaching critical (< 3%) while this package is running. As I understand it, committed transactions will release space withinthe logfile itself, so I am trying to identify if there is a specificstep causing this issue or if it's the package as a whole. Is my understanding correct? If so... Transactions are enabled at the package level, however, no tasks whatsoever have the "join to transaction" checkbox checked. Note that the database in question is in Simple recovery mode. Will SQL Server treat the entire package as one transaction if no "join" checkboxes are checked, or will it treat each task as an autonomous entity?
  4. satya Moderator

    As per the design DTS pacakge is a big transaction when it is invoked, as per the policies & options individual tasks/steps can join the transaction and follow the rules you specify i.e if one task fails then everybody else in the transaction fails as well, this is where you specify the workflow properties.
    Package properties are global settings that affect transaction behavior across the entire package. Step properties operate at the level of the individual task. The option within the to join to transaction is cleared, no package transaction is created, and requests by steps to join the transaction are ignored hence updates are carried out one at a time, as they are requested.
  5. haylo75 New Member

    satya,
    Your last reply is how I initially understood package tasks to work in relation to the global settings. I do not feel too crazy now ;) I have identified another issue related to our logfile bloat and believe it is now fixed and consequently so is our logfile drive space issue. Thanks to everyone for their help!
    Have a good day!

  6. satya Moderator

    Thats the way it is, as per the transactions architecture as you have found other issue for that log size you have to control that way... leave a feedback here if your solution is unique.
  7. haylo75 New Member

    Alas, not unique, rather our IT department was erroneously reporting log file space alerts! Of course we didn't find this out until yesterday after doing a lot of work.

Share This Page