Sql-server – SQL Server Leaked Transactions

sql server

I have a database accessed by around 50 clients via TDS over TCP which does not seem to be releasing log space. The number of processes stays around the expected 50, and some of them are quite long lived (>120 days).

The database now has 40 gb in log space (it only has 14 gb data), 39 gb free. Due to space limitations on the drive, I would like to shrink to something more reasonable (10gb-ish). When I execute DBCC SHRINKFILE('db_log', 10000), it returns an error that the end of the log is in use.

In order to free access to the end of the log, I attempted to place the database in single user mode with the following:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE db SET MULTI_USER
GO

but the script is returning the following message repeated hundreds of times:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Which leads me to believe that somewhere, I am leaving some transactions uncommitted. I am not aware of any process that would intentionally open this many transactions at one time, so I think they must accumulate over time, never being closed.

Question: How do I locate the offending process or script or why is the log not being released?

sys.dm_tran_active_transactions is showing a reasonable 18 transactions with understandable purposes. sp_who shows only the processes I am aware of.

SQL Server Version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Apr  2 2010 15:48:46 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Server Version:

Windows Server 2008 R2 x64 – Datacenter
4 vCPUs, 16GB memory, Pass through disk for data and log, OS disk is VHD

on Hyper-V (Windows Server 2008 R2 SP1 x64 Datacenter)
Dual Intel X5650 (6 core, 12 thread at 2.67GHz)
72 GB memory

Hypervisor only has three VMs and does not show high resource use. SQL Server VM shows ~40% CPU under load and 99% cache hits.

Best Answer

There is a SQL command that will show OPEN transactions. (DBCC OPENTRAN)

http://msdn.microsoft.com/en-us/library/ms182792.aspx

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information

Related Question