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