I'm trying to shrink my Transaction log. Its taking 8 GB of space and I'm getting out of space on this drive. The database is in SIMPLE recovery model.
The problem is that when I see the log info using DBCC LogInfo() it shows 2723 rows with Status of 2. I issued the Checkpoint command many times as well. There are very few rows with Status 0 which are removed when I shrink the file. But these 2723
rows with Status of active-tran are taking up the most space and are not getting cleared.
I had restared the sql server service manytimes (just for experimenting). Eventually, I exectued the DBCC OPENTRANS command; which tells me that there is a non-distributed transaction in T.Log. This transaction is the very first transaction in T.Log file
with Status 2 and Parity 128.
This server used to work as Publisher few months back. All the Subscriptions have been deleted and the relavant Distributor and Subscriber have been re-formatted and re-installed since then. Now when I try to drop the Local Publications from
this local server it gives me error for relevant Distributor or Subscribor machines which do not exist any more.
How should I truncate this Log file and unmark this pending non-distributed transaction from T.Log.
Kindly help me out with this :(.
View Complete Post