Seeing many open transactions in activity monitor during idle hours?

[jsteph]

Hi all,
We have a third-party app used for our daily POS system, it uses a compiled front end against a sql-server db. Not sure what tool the front end was written in, the vendor is cagey about that, but there's no middleware, all the db access is embedded in this compiled front end.

Anyway, at 3AM, when positively nobody is on the system (but screens may be open), I see hundreds of SPID's with open transactions in Activity Monitor (Sql-server 2005).

The Last Batch will show someting like 4:30 PM, and looking at the Details for the spid it will show a call to one of the vendor's stored procs that's basically a SELECT...With(Rowlock).

Like many code-generators, their app has an INS_xxx, UPD_xxx, DEL_xxx, and FET_xxx for every table, where xxx is the table name. The FET procs use "SELECT...FROM xxx With(Rowlock)".

So my question is--would the With(rowlock) hint create an implicit transaction on a Select statement? Or could there be an explicit Begin Tran in their code that I'm not seeing in that last batch? I haven't run a Trace yet, before I do that I just want to know if maybe this is why I see so many open transactions.
Thanks for any insight on this,
--Jim

 

[mrdenny]

If the screens are open, and the code doesn't close the connection then you will still see the connections there. Just because there are connections doesn't mean that there are transactions open.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[jsteph]

mrdenny,
Thanks...Yes, I know the connections are all going to stay there, but some of them have a 1 in the column titled "Open Transactions" (in the sql-2005 Activity Monitor), though most have a zero in this column.

During the day, I can hit refresh on the monitor and see the "Open Transaction" column values 'blink' from 0 to 1 as the workstations are busy.

Yet in the evening, it's still showing a 1 in some of them--this is my concern--I wouldn't think there should be any transactions left open. The Host value on these is a workstation--I know that we have a couple of worker-bee machines that may do batch processing at night so those I don't worry about.

Thanks for any further info,
--Jim

 

[mrdenny]

It could be that the application has simply left a transaction open depending on what screen the user is on and how it was designed. You can use DBCC OPENTRAN to see when the oldest open transaction is.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[jsteph]

The POS vendor is tight lipped but I think their code is pessimistically locking records when the screen has data even if for view only. So we've got users with an order up onscreen but they've left the workstation for the day.
Thanks for your help,
--Jim

 

[mrdenny]

That is totally possible. By looking at the DMVs in the system you should be able to get a good idea about what is going on.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site