Log files will not Shrink:

Question

Log files will not Shrink:

dopydb

SSCertifiable

Points: 6687
More actions
March 28, 2016 at 5:40 am

#399927

hi
I have inherited some dbs that all have the same logical name for database and log file, which I am not sure if this is part of my issue with 2 of the dbs log files that will not shrink.
i have taken full back ups, ran log back ups then tried shrinking - no use
i have put into simple recovery mode, tried shrinking - no use
i actually think i have managed to increase the size of one of them ! :hehe:
is the logical name causing some sort of conflict? or any ideas on what i should do?
thanks
mal

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

Please read http://www.karaszi.com/sqlserver/info_dont_shrink.asp.

As the URL suggests, most of this article is a discussion on why you should be careful before shrinking a data or log file. However, it also explains how to shrink a data or log file, and why sometimes you have to take some extra steps before you can shrink a log file.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

dopydb SSCertifiable Points: 6687 More actions · Answer 2

thanks Hugo

but I think I have something really weird happening, by running below (which is pretty much what I have been trying, only I was doing log backups)

USE [Plato Cronus]

CHECKPOINT

--First param below is fileno for log file, often 2. Check with sys.database_files

--Second is desired size, in MB.

DBCC SHRINKFILE(2, 500)

--DBCC SQLPERF(LOGSPACE) --Optional

DBCC LOGINFO --Optional

If anything, I am actually growing the log file size , which is made up of 1762 VLF !!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Run this and post results:

select

name DatabaseName,

log_reuse_wait_desc

from

sys.databases;

dopydb SSCertifiable Points: 6687 More actions · Answer 4

hi Lynn

for this db, result is

DatabaseNamelog_reuse_wait_desc

Plato CronusACTIVE_TRANSACTION

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 5

This result means that at least one transaction in the database is open (neither committed or rolled back), and SQL Server cannot release log records until it knows whether this transaction commits or rolls back.

To find the oldest transaction, you can use DBCC OPENTRAN:

USE [Plato Cronus];

go

DBCC OPENTRAN;

go

Once you know which transaction it is, determine whether you should commit it, kill/rollback it, or wait for it to finish. After that you can try shrinking the log again.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

dopydb SSCertifiable Points: 6687 More actions · Answer 6

Excellent ! it seems one of our applications are leaving residual transactions open that will never be killed of until sql would be rebooted!

I have now cleared the old processes, and now the log files are back to a normal size!

thanks guys for your help 🙂

mal

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

March 29, 2016 at 7:54 am

#1868236

Glad Hugo and I could be of assistance.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

dopydb (3/29/2016)
Excellent ! it seems one of our applications are leaving residual transactions open that will never be killed of until sql would be rebooted!
mal

THAT needs to be fixed. Such a problem will play hell with much more than a simple log file shrink and may be an indication of a "connection leak". Seriously... they need to find the problem in the app and fix it NOW (that's sooner than ASAP, BTW).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dopydb SSCertifiable Points: 6687 More actions · Answer 9

hi Jeff

yea I have been onto the Software company already, and apparently its an issue they know about and can be fixed in the next upgrade.