Long running transaction problem

I have a situation occasionally where I get an open transaction from a day
or so back. I find this using the:
DBCC OPENTRAN
This shows that the transaction is a day or more old. I then do a:
sp_who xx
Where xx is the SPID obtained from the DBCC command. This shows a few more
items but still does not tell me the process that openned or created the
locks. When I run sp_locks the above transaction has tons of locks, like
1,500,000 locks. Without knowing what else to do I do a:
kill xx
This causes the server to go into recorvery, and due to the size it recovers
for a relativly long time.
How can I find out the service, application or process that is responsible
for the hung transaction? Just from the size I have an idea it is a process
which is updating 290k records.
TIA, MarkIf the process is still running, you could try grabbing the sql stmt that is
running, either via DBCC INPUTBUFFER or use the ::fn_get_sql function if you
have SP3 installed, and try to narrow down your search from there.
--
Regards
Ray Mond
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:%233cbtgk2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> I have a situation occasionally where I get an open transaction from a day
> or so back. I find this using the:
> DBCC OPENTRAN
> This shows that the transaction is a day or more old. I then do a:
> sp_who xx
> Where xx is the SPID obtained from the DBCC command. This shows a few
more
> items but still does not tell me the process that openned or created the
> locks. When I run sp_locks the above transaction has tons of locks, like
> 1,500,000 locks. Without knowing what else to do I do a:
> kill xx
> This causes the server to go into recorvery, and due to the size it
recovers
> for a relativly long time.
> How can I find out the service, application or process that is responsible
> for the hung transaction? Just from the size I have an idea it is a
process
> which is updating 290k records.
> TIA, Mark
>|||I know it's kind of obvious, but how about looking at the HostName and
ProgramName columns when running sp_who2, to trace the application?
Depending on your security model, you could ask the user directly too, since
you can see the UserName.
--
Regards
Ray Mond
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:%233cbtgk2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> I have a situation occasionally where I get an open transaction from a day
> or so back. I find this using the:
> DBCC OPENTRAN
> This shows that the transaction is a day or more old. I then do a:
> sp_who xx
> Where xx is the SPID obtained from the DBCC command. This shows a few
more
> items but still does not tell me the process that openned or created the
> locks. When I run sp_locks the above transaction has tons of locks, like
> 1,500,000 locks. Without knowing what else to do I do a:
> kill xx
> This causes the server to go into recorvery, and due to the size it
recovers
> for a relativly long time.
> How can I find out the service, application or process that is responsible
> for the hung transaction? Just from the size I have an idea it is a
process
> which is updating 290k records.
> TIA, Mark
>|||Thanx for the response Ray. You are right that I should see the user name
but the user name in this case is the asministrator which means it is one of
the automated services that are run. The suspected application is an EDI
package that automatically retreives information via FTP and runs a script
that updates the SQL database. This process runs on the server itself.
--
Mark Butler
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:ulcMjCn2DHA.2408@.tk2msftngp13.phx.gbl...
> I know it's kind of obvious, but how about looking at the HostName and
> ProgramName columns when running sp_who2, to trace the application?
> Depending on your security model, you could ask the user directly too,
since
> you can see the UserName.
> --
> Regards
> Ray Mond
> "Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
> news:%233cbtgk2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> > I have a situation occasionally where I get an open transaction from a
day
> > or so back. I find this using the:
> >
> > DBCC OPENTRAN
> >
> > This shows that the transaction is a day or more old. I then do a:
> >
> > sp_who xx
> >
> > Where xx is the SPID obtained from the DBCC command. This shows a few
> more
> > items but still does not tell me the process that openned or created the
> > locks. When I run sp_locks the above transaction has tons of locks,
like
> > 1,500,000 locks. Without knowing what else to do I do a:
> >
> > kill xx
> >
> > This causes the server to go into recorvery, and due to the size it
> recovers
> > for a relativly long time.
> >
> > How can I find out the service, application or process that is
responsible
> > for the hung transaction? Just from the size I have an idea it is a
> process
> > which is updating 290k records.
> >
> > TIA, Mark
> >
> >
>sql