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

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...

quote:

---

> 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

quote:

---

> 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

quote:

---

> 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

quote:

---

> 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...

quote:

---

> 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

quote:

---

> 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

quote:

---

> 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

quote:

---

> 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...

quote:

---

> 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

quote:

---

> you can see the UserName.
> --
> Regards
> Ray Mond
> "Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
> news:%233cbtgk2DHA.2604@.TK2MSFTNGP09.phx.gbl...
day[QUOTE]
> more
like[QUOTE]
> recovers
responsible[QUOTE]
> process
>

---

Login Problems using .net 2 Login Control

Hi There,

I have a really strange problem. I am creating a website with an administration back end. We have a seperate sql 2000 server which we are using hold authentication credientials (after having used the aspnet_regsql.exe to setup the appropriate tables.

When testing the application on the local machine, everything works fine. The web admin works, I can login, I can retrieve data to display on the rest of the site. Once uploaded to the live environment things change.

I can still connect to the sql server i.e. I have information being displayed on the public area of the site which is coming from the sql 2000 db. I can type in the incorrect login details to the admin section of the site and the system will return an error, which is correct as well, however, if I type in the correct login information, the page hangs for awhile and then displays this error: My question is, why is it doing this when it works find on my local box and the DB connection is actually working on other parts of the site? Can anyone help?

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Make sure TCP/IP activated as a connection method to the sql server and firewall doesn't block the connection.

|||

HI.

There is no firewall between the two servers and TCP/IP is enabled. The fact is, a connection is working elsewhere on the site, but the login just doesnt work.

|||How do you know the connection is working? Do you mean the conneciton can be established to the remote SQL 2000 instance? You can turn on Audit Login for the SQL 2000 instance (right click the instance in Enterprise Manager->check Properties->switch to Security tab->change the Audit Level to All->restart server, then if there is any login failure, you can find the login failure error message in SQL ERRORLOGs.|||

I think I have figured out what the problem is. I had the membership data source defined but not the roles, this meant that the roles were being stored in the sqlexpress DB while the membership details were being stored on SQL 2000. On my local machine, it could see both DB but when uploaded to the servers (that do not support sqlexpress DB) it was failing to connect once a successful login was taking place and roles needed to be allocated.

Pity the error reported gave no indication of what the problem really was.

Now I am having issues with creating the necessary permissions tables on the server. Once I get it all going and tested, I will clost this post as being solved.