I have done as many tricks as I know (forcing a shrink with dummy records, backing up tx log regularly, shrinking files, etc.) and while this has worked well in the past I seem to have a transaction stuck in my active portion of my log that I can't get rid of. This is indicated by the status 2 transaction being at the top (not the bottom), having several VLF's with 0 status and then the usual active portion at the bottom.
Is there any way to know what transaction this is and to force it to the inactive portion of the log?
DBCC OPENTRANDisplays information about the oldest active transaction
I use this with sp_who to find and kill the process that is holding a transaction open, as long as it is open it will remain in the "active" portion of the transaction log.
I have had to resort to setting the database to simple recovery mode, and dump the transaction log to overcoe this issue. I would also suggest after shrinking the log file, set the recovery mode back to Full, and perform a full database backup.
We walk in the dark places no others will enterWe stand on the bridge and no one may pass