Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.
Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
Showing posts with label writes. Show all posts
Showing posts with label writes. Show all posts
Wednesday, March 7, 2012
DTS - Strange Behavior
Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server
does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server
does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
DTS - Strange Behavior
Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
Friday, February 24, 2012
DTExec is taking all my memory
I have created a SSIS package that reads 500 text files splits them into 4 raw files then reads them again and writes then to 4 database tables different Tables.
The reason form this is that my raw files have multiple types of records in them and it is only 1 Coolum. I split this out into the different types of records and load whole rows into the database.
ie input 1 txt file
<T6>
1:1000178
3:18148821-00
5:40204043
6:1
17:EX201036259NZ
25:0000304862
</T6>
<T1>
1:18148821-00
</T1>
<T5>
1:1511313
4:18126485-00
8:2006032510230300
17:EX201033399NZ
</T5>
<T6>
1:1511158
3:18084863-00
5:40617044
6:1
17:EX201033969NZ
25:0000302981
</T6>
End up begin rows in the T6 Table
1000178 18148821-00 40204043 1 EX201036259NZ 0000304862
1511158 18084863-00 40617044 1 EX201033969NZ 0000302981
T5 Table gets a new record
1511313 18126485-00 2006032510230300 EX201033399NZ
and T1 Table get a record
18148821-00
Anyway all this works find but I find that the DTExec process work fine until it has used up all the memory in the laptop in general it take 400megs to run this SSIS. I'm wondering am I missing something like don't run in a transaction. I know in the old DTS you could commit on each package and how do I turn all logging off eg what you see in the DOS box (can I do this?) would love some help on this and if anyone want a copy of this ssis package ie your trying to do the same then I'm more than happy to email it.
Check the /Rep option of DTExec to reduce the output.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm
-Jamie
|||Thanks Jamie,
This has help the logging (if you know can I set this in the enviroment IDE).
Now bigger problems is the memory, at the moment it fails after 700 files due to luck in memory.
Any ideas on how to find where the memory leak is in the SSIS Package?
|||Just to let other people kno, after some more searching I found that there is a memory leak in the Forloop object, that should be fixed in SP1.
I have download SP1 and tested it and it does appear that there is still a memory leak but only about a 10th of what it was, which now allows me in import 5000 files in one go.
|||Thanks for the info John. That's an important one to know. I certainly wasn't aware of it.
-Jamie
Friday, February 17, 2012
DTC Error and unresolved SQL transaction
I have a procedure that reads data from linked server, a SQL2005 box, and
writes a row in a SQL2000 database. This procedure and configuration have
been working successfully for several years. This Sunday at 4am, this
procedure failed to complete, leaving an unresolved transaction. The symptom
is an insert on this table will timeout and fail because the unresolved
transaction has a lock on the table, and it shows as a blocking transaction.
Otherwise the database is functional and responsive. I have tried to KILL the
unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
under the Activity Monitor command column.
I had this same problem last weekend, and re-starting the SQL Server Service
resolved the transaction. However, this is not a viable option during
production hours.
I tried using "KILL 51 WITH STATUSONLY", and it returned:
SPID 51: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
It it cam back with:
Server: Msg 6114, Level 16, State 1, Line 1
Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
being used by another user. KILL command failed.
My two questions are:
1) Is there anyway to clear this blocking transaction short of re-starting
the SQL Server?
2) Is there anyway to figure out the root cause of the problem? I believe it
is some sort of MSDTC issue, that seems to only happen early on Sunday
mornings.
The following DTC error happened at exactly the same timestamp as the SQL
procedure was executed.
___________________________________________
Application Event Log Error
___________________________________________
Date 7/1/2007 4:09:31 AM
Log Windows NT (Application)
Source MSDTC
Category (3)
Event 3221229829
Computer SERVER002
Message
The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display the message, or you may not have permission to
access them. The following information is part of the
event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
___________________________________________
Thank you in advance,
KenHi Ken
"KenL" wrote:
> I have a procedure that reads data from linked server, a SQL2005 box, and
> writes a row in a SQL2000 database. This procedure and configuration have
> been working successfully for several years. This Sunday at 4am, this
> procedure failed to complete, leaving an unresolved transaction. The symptom
> is an insert on this table will timeout and fail because the unresolved
> transaction has a lock on the table, and it shows as a blocking transaction.
> Otherwise the database is functional and responsive. I have tried to KILL the
> unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
> under the Activity Monitor command column.
> I had this same problem last weekend, and re-starting the SQL Server Service
> resolved the transaction. However, this is not a viable option during
> production hours.
> I tried using "KILL 51 WITH STATUSONLY", and it returned:
> SPID 51: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
> It it cam back with:
> Server: Msg 6114, Level 16, State 1, Line 1
> Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
> being used by another user. KILL command failed.
> My two questions are:
> 1) Is there anyway to clear this blocking transaction short of re-starting
> the SQL Server?
> 2) Is there anyway to figure out the root cause of the problem? I believe it
> is some sort of MSDTC issue, that seems to only happen early on Sunday
> mornings.
> The following DTC error happened at exactly the same timestamp as the SQL
> procedure was executed.
> ___________________________________________
> Application Event Log Error
> ___________________________________________
> Date 7/1/2007 4:09:31 AM
> Log Windows NT (Application)
> Source MSDTC
> Category (3)
> Event 3221229829
> Computer SERVER002
> Message
> The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
> found. The local computer may not have the necessary registry information or
> message DLL files to display the message, or you may not have permission to
> access them. The following information is part of the
> event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
> ___________________________________________
> Thank you in advance,
> Ken
I am not a MSDTC expert!!! Which process did you kill? I would expect a
process on the remote and local (originator) machines, and if there was an
order to be killed then local would be the first. If you stopped the DTC
services (NET STOP MSDTC) it should also rollback, but all distributed
transactions would be affected.
Is this the only time distributed transaction are used? If not then it would
narrow the issue down to either something with the process or something that
happens at that time. If the process is scheduled and works at other times
then it would rule the process out. If it is something that happens at a
specific time, check things like firewalls or antivirus updates/scans etc.
http://support.microsoft.com/default.aspx/kb/306843
Also look for blocking occuring during the process and how you handle errors
such as deadlocks in the code.
You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
check that DTC works ok.
John|||Thank you for the response John.
<Is this the only time distributed transaction are used?
No, there are many procedures on this server that link to databases on
another server. The stored procedure that is failing runs hundreds of times
in a day. It had been reliable for years, up until last Sunday and this
Sunday when I have seen the two failures
<Which process did you kill?
I killed the spid on the SQL server initiating the link
I will review the kb's you referenced
Thanks,
Ken
"John Bell" wrote:
> Hi Ken
> "KenL" wrote:
> > I have a procedure that reads data from linked server, a SQL2005 box, and
> > writes a row in a SQL2000 database. This procedure and configuration have
> > been working successfully for several years. This Sunday at 4am, this
> > procedure failed to complete, leaving an unresolved transaction. The symptom
> > is an insert on this table will timeout and fail because the unresolved
> > transaction has a lock on the table, and it shows as a blocking transaction.
> > Otherwise the database is functional and responsive. I have tried to KILL the
> > unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
> > under the Activity Monitor command column.
> > I had this same problem last weekend, and re-starting the SQL Server Service
> > resolved the transaction. However, this is not a viable option during
> > production hours.
> >
> > I tried using "KILL 51 WITH STATUSONLY", and it returned:
> > SPID 51: transaction rollback in progress. Estimated rollback completion:
> > 100%. Estimated time remaining: 0 seconds.
> >
> > I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
> > It it cam back with:
> > Server: Msg 6114, Level 16, State 1, Line 1
> > Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
> > being used by another user. KILL command failed.
> >
> > My two questions are:
> > 1) Is there anyway to clear this blocking transaction short of re-starting
> > the SQL Server?
> > 2) Is there anyway to figure out the root cause of the problem? I believe it
> > is some sort of MSDTC issue, that seems to only happen early on Sunday
> > mornings.
> >
> > The following DTC error happened at exactly the same timestamp as the SQL
> > procedure was executed.
> > ___________________________________________
> > Application Event Log Error
> > ___________________________________________
> > Date 7/1/2007 4:09:31 AM
> > Log Windows NT (Application)
> >
> > Source MSDTC
> > Category (3)
> > Event 3221229829
> > Computer SERVER002
> >
> > Message
> > The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
> > found. The local computer may not have the necessary registry information or
> > message DLL files to display the message, or you may not have permission to
> > access them. The following information is part of the
> > event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
> > ___________________________________________
> >
> > Thank you in advance,
> > Ken
> I am not a MSDTC expert!!! Which process did you kill? I would expect a
> process on the remote and local (originator) machines, and if there was an
> order to be killed then local would be the first. If you stopped the DTC
> services (NET STOP MSDTC) it should also rollback, but all distributed
> transactions would be affected.
> Is this the only time distributed transaction are used? If not then it would
> narrow the issue down to either something with the process or something that
> happens at that time. If the process is scheduled and works at other times
> then it would rule the process out. If it is something that happens at a
> specific time, check things like firewalls or antivirus updates/scans etc.
> http://support.microsoft.com/default.aspx/kb/306843
> Also look for blocking occuring during the process and how you handle errors
> such as deadlocks in the code.
> You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
> check that DTC works ok.
> John
writes a row in a SQL2000 database. This procedure and configuration have
been working successfully for several years. This Sunday at 4am, this
procedure failed to complete, leaving an unresolved transaction. The symptom
is an insert on this table will timeout and fail because the unresolved
transaction has a lock on the table, and it shows as a blocking transaction.
Otherwise the database is functional and responsive. I have tried to KILL the
unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
under the Activity Monitor command column.
I had this same problem last weekend, and re-starting the SQL Server Service
resolved the transaction. However, this is not a viable option during
production hours.
I tried using "KILL 51 WITH STATUSONLY", and it returned:
SPID 51: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
It it cam back with:
Server: Msg 6114, Level 16, State 1, Line 1
Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
being used by another user. KILL command failed.
My two questions are:
1) Is there anyway to clear this blocking transaction short of re-starting
the SQL Server?
2) Is there anyway to figure out the root cause of the problem? I believe it
is some sort of MSDTC issue, that seems to only happen early on Sunday
mornings.
The following DTC error happened at exactly the same timestamp as the SQL
procedure was executed.
___________________________________________
Application Event Log Error
___________________________________________
Date 7/1/2007 4:09:31 AM
Log Windows NT (Application)
Source MSDTC
Category (3)
Event 3221229829
Computer SERVER002
Message
The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display the message, or you may not have permission to
access them. The following information is part of the
event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
___________________________________________
Thank you in advance,
KenHi Ken
"KenL" wrote:
> I have a procedure that reads data from linked server, a SQL2005 box, and
> writes a row in a SQL2000 database. This procedure and configuration have
> been working successfully for several years. This Sunday at 4am, this
> procedure failed to complete, leaving an unresolved transaction. The symptom
> is an insert on this table will timeout and fail because the unresolved
> transaction has a lock on the table, and it shows as a blocking transaction.
> Otherwise the database is functional and responsive. I have tried to KILL the
> unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
> under the Activity Monitor command column.
> I had this same problem last weekend, and re-starting the SQL Server Service
> resolved the transaction. However, this is not a viable option during
> production hours.
> I tried using "KILL 51 WITH STATUSONLY", and it returned:
> SPID 51: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
> It it cam back with:
> Server: Msg 6114, Level 16, State 1, Line 1
> Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
> being used by another user. KILL command failed.
> My two questions are:
> 1) Is there anyway to clear this blocking transaction short of re-starting
> the SQL Server?
> 2) Is there anyway to figure out the root cause of the problem? I believe it
> is some sort of MSDTC issue, that seems to only happen early on Sunday
> mornings.
> The following DTC error happened at exactly the same timestamp as the SQL
> procedure was executed.
> ___________________________________________
> Application Event Log Error
> ___________________________________________
> Date 7/1/2007 4:09:31 AM
> Log Windows NT (Application)
> Source MSDTC
> Category (3)
> Event 3221229829
> Computer SERVER002
> Message
> The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
> found. The local computer may not have the necessary registry information or
> message DLL files to display the message, or you may not have permission to
> access them. The following information is part of the
> event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
> ___________________________________________
> Thank you in advance,
> Ken
I am not a MSDTC expert!!! Which process did you kill? I would expect a
process on the remote and local (originator) machines, and if there was an
order to be killed then local would be the first. If you stopped the DTC
services (NET STOP MSDTC) it should also rollback, but all distributed
transactions would be affected.
Is this the only time distributed transaction are used? If not then it would
narrow the issue down to either something with the process or something that
happens at that time. If the process is scheduled and works at other times
then it would rule the process out. If it is something that happens at a
specific time, check things like firewalls or antivirus updates/scans etc.
http://support.microsoft.com/default.aspx/kb/306843
Also look for blocking occuring during the process and how you handle errors
such as deadlocks in the code.
You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
check that DTC works ok.
John|||Thank you for the response John.
<Is this the only time distributed transaction are used?
No, there are many procedures on this server that link to databases on
another server. The stored procedure that is failing runs hundreds of times
in a day. It had been reliable for years, up until last Sunday and this
Sunday when I have seen the two failures
<Which process did you kill?
I killed the spid on the SQL server initiating the link
I will review the kb's you referenced
Thanks,
Ken
"John Bell" wrote:
> Hi Ken
> "KenL" wrote:
> > I have a procedure that reads data from linked server, a SQL2005 box, and
> > writes a row in a SQL2000 database. This procedure and configuration have
> > been working successfully for several years. This Sunday at 4am, this
> > procedure failed to complete, leaving an unresolved transaction. The symptom
> > is an insert on this table will timeout and fail because the unresolved
> > transaction has a lock on the table, and it shows as a blocking transaction.
> > Otherwise the database is functional and responsive. I have tried to KILL the
> > unresponsive process, but it wont clear, and just reads "KILLED/ROLLED BACK"
> > under the Activity Monitor command column.
> > I had this same problem last weekend, and re-starting the SQL Server Service
> > resolved the transaction. However, this is not a viable option during
> > production hours.
> >
> > I tried using "KILL 51 WITH STATUSONLY", and it returned:
> > SPID 51: transaction rollback in progress. Estimated rollback completion:
> > 100%. Estimated time remaining: 0 seconds.
> >
> > I tried using KILL "51457D54-4FD7-408A-B5CA-AFF33D601D00"
> > It it cam back with:
> > Server: Msg 6114, Level 16, State 1, Line 1
> > Distributed transaction with UOW {51457D54-4FD7-408A-B5CA-AFF33D601D00} is
> > being used by another user. KILL command failed.
> >
> > My two questions are:
> > 1) Is there anyway to clear this blocking transaction short of re-starting
> > the SQL Server?
> > 2) Is there anyway to figure out the root cause of the problem? I believe it
> > is some sort of MSDTC issue, that seems to only happen early on Sunday
> > mornings.
> >
> > The following DTC error happened at exactly the same timestamp as the SQL
> > procedure was executed.
> > ___________________________________________
> > Application Event Log Error
> > ___________________________________________
> > Date 7/1/2007 4:09:31 AM
> > Log Windows NT (Application)
> >
> > Source MSDTC
> > Category (3)
> > Event 3221229829
> > Computer SERVER002
> >
> > Message
> > The description for Event ID '-1073737467' in Source 'MSDTC' cannot be
> > found. The local computer may not have the necessary registry information or
> > message DLL files to display the message, or you may not have permission to
> > access them. The following information is part of the
> > event:'.\iomgrclt.cpp:204, Pid: 1300, CmdLine: C:\WINNT\System32\msdtc.exe'
> > ___________________________________________
> >
> > Thank you in advance,
> > Ken
> I am not a MSDTC expert!!! Which process did you kill? I would expect a
> process on the remote and local (originator) machines, and if there was an
> order to be killed then local would be the first. If you stopped the DTC
> services (NET STOP MSDTC) it should also rollback, but all distributed
> transactions would be affected.
> Is this the only time distributed transaction are used? If not then it would
> narrow the issue down to either something with the process or something that
> happens at that time. If the process is scheduled and works at other times
> then it would rule the process out. If it is something that happens at a
> specific time, check things like firewalls or antivirus updates/scans etc.
> http://support.microsoft.com/default.aspx/kb/306843
> Also look for blocking occuring during the process and how you handle errors
> such as deadlocks in the code.
> You could use DTCTester http://support.microsoft.com/kb/293799 or DTCPing to
> check that DTC works ok.
> John
Subscribe to:
Posts (Atom)