Problem with uncommited transactions in log-file

[Holyman]

Hi group

I have an application running on a couple of pc's all connecting to
the same SQL-serve databaser. The other day one of my applications
started a transaction which it never committed (programming error) .
The application continiued running for a couple og hours filling every
insert and update statement to the sql-server log-file, where they
remain rigt now. The applications on all the other machines
deadlocket because they waited for the first application to commit. I
eventually shot down the ms sql service and restarted it. Now all the
transactions which were not commited are not to bee seen in the
database, because they remain in the log file (ldf-file) waiting for
af commit I cannot perform. Are there a way to browse an ms-server
..ldf file to see which uncommitted records are in it

Thanks if anyone can helo

Regards
jsa

[Simon Hayes]

If you restarted the service, then the open transaction will simply
have been rolled back, as if it never happened. The easiest thing is
probably simple to redo the operations, but if that's not possible then
there are third-party tools which can read logs and recreate individual
statements. This is one (which I've never used):

http://www.lumigent.com/products/le_sql.html

Simon

[Holyman]

Thanks - I will try this one.

I have tried som other 3rd partie apps to view the log file.. But it
does not show the uncommited transactions..

But I know they are still in the ldf file, because when I open the
file in a hex-editor, I can see some of the data.. But it is not very
readable

On 7 Sep 2005 08:23:46 -0700, "Simon Hayes" <sq*@hayes.ch> wrote:

If you restarted the service, then the open transaction will simply
have been rolled back, as if it never happened. The easiest thing is
probably simple to redo the operations, but if that's not possible then
there are third-party tools which can read logs and recreate individual
statements. This is one (which I've never used):

http://www.lumigent.com/products/le_sql.html

Simon

[Erland Sommarskog]

Holyman (ja*@oncable.dk) writes:

The applications on all the other machines
deadlocket because they waited for the first application to commit.

No, they did not.

Terminology time: a deadlock occurs when two processes are waiting
on each other to release a resource that is locked by the other process.
This condition is detected by SQL Server, and SQL Server will select one
of the processes as a deadlock victim, and rollback that transaction.

If the application was waiting for the run-away transaction to committed
they were simply blocked, but that is not a deadlock situation.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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