Closed transaction keeps running...awaiting command

I am debugging an app which blocks many processes in a SQL7 server DB.
The app log writes every transaction "open" and "close".
The weird thing is : when the app logfile says the transaction is
dropped (object closed) the db keeps showing the process "running", in
a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
command status.

We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
IIS side, an equal box for MTS (same patches and updates) and a SQL
Server 7 (on NT4, same fixes ans SPs) database on another box.

Is this normal? Those sleeping processes are blocking other apps and
everything gets slow and messy...the only solution is to kill those
blocking processes.

Thanks![posted and mailed, please reply in news]

Rittercorp (ritter_cl@.yahoo.com) writes:
> I am debugging an app which blocks many processes in a SQL7 server DB.
> The app log writes every transaction "open" and "close".
> The weird thing is : when the app logfile says the transaction is
> dropped (object closed) the db keeps showing the process "running", in
> a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
> command status.
> We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
> IIS side, an equal box for MTS (same patches and updates) and a SQL
> Server 7 (on NT4, same fixes ans SPs) database on another box.
> Is this normal? Those sleeping processes are blocking other apps and
> everything gets slow and messy...the only solution is to kill those
> blocking processes.

No, this is not normal. Apparently your application has a problem with
transaction scope. The fact that your application log looks good proves
little. The application log may know about transaction it starts, but
what about transactions that starts in SQL code, for instance in
stored procedures?

There are a couple of gotchas. For instance, if you call a stored procedure
and that stored procedure starts a transaction, but the application
cancels the procedure before the execution completed, for instance
because of the dreaded "Timeout expired", the transaction started by
the SP is *not* rolled back. The same is true, if the procedure
starts a transaction, and then is aborted because of a reference to
a non-existing table.

On http://www.sommarskog.se/sqlutil/aba_lockinfo.html I have a
utility that gives yuu a lot of information about locks in the server,
including a list of which are the locked objects. This might be helpful
for you to understand where you are leaking transactions. (Although it
might be messy to find out. Because once a process has missed a
COMMIT or ROLLBACK, it will accumulate locks from all over the place.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Client Hang

Hello,
We han a SQL Server Developer Edition (64-Bit) versión 8.00.760(SP3) that hangs our SQLServer Clients after starting a DTC transaction.
SQL Server service starts automatically at startup and it starts DTC.
?Does anybody know how to solve the problem and what is happening?

> Hello,
> We han a SQL Server Developer Edition (64-Bit) versión 8.00.760(SP3)
> that hangs our SQLServer Clients after starting a DTC transaction.
> SQL Server service starts automatically at startup and it starts DTC.
> ?Does anybody know how to solve the problem and what is happening?
>
It could be an MS DTC problem. You can use a tool named DTCPing to
troubleshoot MS DTC operation:
HOWTO: Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Client Hang

Hello,
We han a SQL Server Developer Edition (64-Bit) versión 8.00.760(SP3) that h
angs our SQLServer Clients after starting a DTC transaction.
SQL Server service starts automatically at startup and it starts DTC.
?Does anybody know how to solve the problem and what is happening?
> Hello,
> We han a SQL Server Developer Edition (64-Bit) versión 8.00.760(SP3)
> that hangs our SQLServer Clients after starting a DTC transaction.
> SQL Server service starts automatically at startup and it starts DTC.
> ?Does anybody know how to solve the problem and what is happening?
>
--
It could be an MS DTC problem. You can use a tool named DTCPing to
troubleshoot MS DTC operation:
HOWTO: Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Clearing Transaction Log automatically...

I know this question has been asked before but I've found no answers that
seem to work.
I want to automatically clear my database transaction log to 10mb or so. My
current method is to detach the database. Rename the tlog file. Single file
attach the database. Then delete the tlog file.
I've tried DBCC SHRINKFILE (MYDB, 10) but this doesn't work. And I have
yet to get BACKUP LOG to work. Can someone offer any advise?
Thanks in advance for any help you can offer.What errors do you get when you try the backup log statement?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Mike" <mschultz@.doitsmarter.com> wrote in message
news:O1ZPqJtuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> I know this question has been asked before but I've found no answers that
> seem to work.
> I want to automatically clear my database transaction log to 10mb or so.
My
> current method is to detach the database. Rename the tlog file. Single
file
> attach the database. Then delete the tlog file.
> I've tried DBCC SHRINKFILE (MYDB, 10) but this doesn't work. And I have
> yet to get BACKUP LOG to work. Can someone offer any advise?
> Thanks in advance for any help you can offer.
>
>|||1. To backup the log you must be in FUll recovery mode.
2. To backup the log you must have first backed up the entire database at
least once.
After backing up the log, then try shrinkfile..
"Mike" <mschultz@.doitsmarter.com> wrote in message
news:O1ZPqJtuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> I know this question has been asked before but I've found no answers that
> seem to work.
> I want to automatically clear my database transaction log to 10mb or so.
My
> current method is to detach the database. Rename the tlog file. Single
file
> attach the database. Then delete the tlog file.
> I've tried DBCC SHRINKFILE (MYDB, 10) but this doesn't work. And I have
> yet to get BACKUP LOG to work. Can someone offer any advise?
> Thanks in advance for any help you can offer.
>
>|||Thank you for the suggestions. Will give them a shot.
Mike
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:e1FNUGzuDHA.1512@.TK2MSFTNGP10.phx.gbl...
> 1. To backup the log you must be in FUll recovery mode.
> 2. To backup the log you must have first backed up the entire database at
> least once.
> After backing up the log, then try shrinkfile..
> "Mike" <mschultz@.doitsmarter.com> wrote in message
> news:O1ZPqJtuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > I know this question has been asked before but I've found no answers
that
> > seem to work.
> >
> > I want to automatically clear my database transaction log to 10mb or
so.
> My
> > current method is to detach the database. Rename the tlog file. Single
> file
> > attach the database. Then delete the tlog file.
> >
> > I've tried DBCC SHRINKFILE (MYDB, 10) but this doesn't work. And I
have
> > yet to get BACKUP LOG to work. Can someone offer any advise?
> >
> > Thanks in advance for any help you can offer.
> >
> >
> >
>

Clearing LOG Files?

I know that the Transaction LOG files are cleared when the DB is backed-up, but is there any way to clear the log files when they become too big, without backing-up the DB?
Thanx..
The log files are not emptied when you do BACKUP DATABASE. They are emptied when you do BACKUP LOG.
If you don't do log backups, just set the database to simple recovery mode...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is backed-up, but is there any way to clear
the log files when they become too big, without backing-up the DB?
> Thanx..
|||What recovery mode is your database is in?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
I know that the Transaction LOG files are cleared when the DB is backed-up,
but is there any way to clear the log files when they become too big,
without backing-up the DB?
Thanx..
|||Rival,
Actually, the Transaction Logs are cleared when the Transaction Logs are
backed up, not when the Database is backed up. So, if only the DB is being
backed up the log will grow and grow and grow. So, you first step is to
make sure that the transaction log is being backed up.
If you need to abandon part of the log, you options are:
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
or, switch to the SIMPLE recovery model:
ALTER DATABASE dbname SET RECOVERY SIMPLE
If the physical LOG file is too large, you would need to use DBCC SHRINKFILE
to physically shrink it after truncation.
Russell Fields
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is
backed-up, but is there any way to clear the log files when they become too
big, without backing-up the DB?
> Thanx..
|||Thanx a lot.
Misconception cleared up... :-) I'll give it a try.

Clearing LOG Files?

I know that the Transaction LOG files are cleared when the DB is backed-up, but is there any way to clear the log files when they become too big, without backing-up the DB?
Thanx..The log files are not emptied when you do BACKUP DATABASE. They are emptied when you do BACKUP LOG.
If you don't do log backups, just set the database to simple recovery mode...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is backed-up, but is there any way to clear
the log files when they become too big, without backing-up the DB?
> Thanx..|||What recovery mode is your database is in?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
I know that the Transaction LOG files are cleared when the DB is backed-up,
but is there any way to clear the log files when they become too big,
without backing-up the DB?
Thanx..|||Rival,
Actually, the Transaction Logs are cleared when the Transaction Logs are
backed up, not when the Database is backed up. So, if only the DB is being
backed up the log will grow and grow and grow. So, you first step is to
make sure that the transaction log is being backed up.
If you need to abandon part of the log, you options are:
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
or, switch to the SIMPLE recovery model:
ALTER DATABASE dbname SET RECOVERY SIMPLE
If the physical LOG file is too large, you would need to use DBCC SHRINKFILE
to physically shrink it after truncation.
Russell Fields
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is
backed-up, but is there any way to clear the log files when they become too
big, without backing-up the DB?
> Thanx..

Clearing LOG Files?

I know that the Transaction LOG files are cleared when the DB is backed-up,
but is there any way to clear the log files when they become too big, withou
t backing-up the DB?
Thanx..The log files are not emptied when you do BACKUP DATABASE. They are emptied
when you do BACKUP LOG.
If you don't do log backups, just set the database to simple recovery mode..
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is backed-up, but is
there any way to clear
the log files when they become too big, without backing-up the DB?
> Thanx..|||What recovery mode is your database is in?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
I know that the Transaction LOG files are cleared when the DB is backed-up,
but is there any way to clear the log files when they become too big,
without backing-up the DB?
Thanx..|||Rival,
Actually, the Transaction Logs are cleared when the Transaction Logs are
backed up, not when the Database is backed up. So, if only the DB is being
backed up the log will grow and grow and grow. So, you first step is to
make sure that the transaction log is being backed up.
If you need to abandon part of the log, you options are:
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
or, switch to the SIMPLE recovery model:
ALTER DATABASE dbname SET RECOVERY SIMPLE
If the physical LOG file is too large, you would need to use DBCC SHRINKFILE
to physically shrink it after truncation.
Russell Fields
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is
backed-up, but is there any way to clear the log files when they become too
big, without backing-up the DB?
> Thanx..|||Thanx a lot.
Misconception cleared up... :-) I'll give it a try.