March 28, 2016 at 5:40 am
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
March 28, 2016 at 6:36 am
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.
March 28, 2016 at 7:06 am
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 !!
March 28, 2016 at 11:01 am
Run this and post results:
select
name DatabaseName,
log_reuse_wait_desc
from
sys.databases;
March 29, 2016 at 2:07 am
hi Lynn
for this db, result is
DatabaseNamelog_reuse_wait_desc
Plato CronusACTIVE_TRANSACTION
March 29, 2016 at 2:28 am
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.
March 29, 2016 at 3:31 am
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
March 29, 2016 at 7:54 am
Glad Hugo and I could be of assistance.
March 29, 2016 at 8:33 am
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
Change is inevitable... Change for the better is not.
March 29, 2016 at 8:38 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply