9

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.

Improve this question
3
  • I'm not a sql dba but did you do log backup? serverfault.com/questions/54958/…
    – rene
    Commented Dec 24, 2012 at 17:42
  • @rene, Yes, I should have mentioned that, but the database has a full backup performed daily and a log backup every six hours.
    – Mitch
    Commented Dec 24, 2012 at 18:53
  • Hi Mitch, probably unrelated to this issue, but wouldn't have been surprised if this had been responsible. Your version is RTM (Release to Manufacture). Microsoft, like other virtually all software vendors will push to release the next major release with a number of small defects in the product. [link]microsoft.com/en-us/download/details.aspx?id=44271 This is the link to the latest Service Pack for SQL 2008 R2. Best to keep your servers upto date for security, bug fixes & Performance reasons.
    – DamagedGoods
    Commented Nov 21, 2014 at 23:19

3 Answers 3

Reset to default
4

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

Improve this answer
4

For whatever reason, there appeared to be nothing holding the log file open. By running multiple log backups (>10), the end of the log was released and the shrink could occur. Not sure why... but it worked.

backup log db to disk = '\\l-backup1\drop\2012-12-23_db_log.bak' with stats = 1
go 15
dbcc shrinkfile('db_log', 10240)
go
Improve this answer
3

If I am understanding your question correctly, you have a 40Gb transaction log with 39Gb free? The log is a circular structure, made up of smaller Virtual Log Files. Each time a VLF is full, SQL starts using the next VLF. (Not necessarily in the same order as the VLFs are in the file). When you shrink the log file it releases free space from the END of the log. If the active portion of the log is at the end, no space can be freed, if it is in the middle somewhere then you would only be able to reclaim some of the space. DBCC LOGINFO will show you all the VLFs in the log and a status showing the that VLF currently contains any active log. I believe that status 2 is active and 0 is inactive. I'm sure google can provide more information if needed.

If your problem is just that the active portion is currently at the end then your best bet is just to wait until it rolls round to the start again, then shrink the log. This can take a surprising amount of time, be patient. It will get there.
Also remember that if ANY part of a VLF is currently active then the entire VLF is kept active.

You should however monitor the size of the log file, if it unexpectedly grows again then you'll need to do some investigation into the cause. You should avoid unnecessary shrinking of the log file, when it grows again it can slow performance.

More information on VLFs can be found in Kimberly Tripps blog post here.

Improve this answer
1
  • thank you for the DBCC LOGINFO command, I was unaware of that one. That being said, I am aware of the structure of the log and don't consider shrinking the file unneccesarrily. As mentioned, we only regularly use around 1gb of log in our current backup structure and I would like to reclaim some of the free space. The problem is that the log space at the end of the log is not being released even after several weeks of waiting. During that period, the database has had many full and log backups which leads me to believe something is prevent the natural circling.
    – Mitch
    Commented Dec 24, 2012 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.