Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I'm not a sql dba but did you do log backup? serverfault.com/questions/54958/… –  rene Dec 24 '12 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 Dec 24 '12 at 18:53
add comment

migrated from stackoverflow.com Dec 24 '12 at 19:52

This question came from our site for professional and enthusiast programmers.

3 Answers

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

share|improve this answer
add comment

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

share|improve this answer
    
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 Dec 24 '12 at 18:52
add comment
up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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