November 21, 2014 at 11:41 am
Hi All. I Hope you can help me resolve this. I've already shrunk the tlog from 350 GB to 313.
My DB Server (2008 R2 Sp2) cannot be restarted and the db cannot go offline or detach due to company policy.
My DB after changing from full to simple mode still has 313GB tlog file and when I run DBCC OPENTRAN I get
Transaction information for database 'DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (2882:26:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Which means this DB is participating in a High Availability process like replication, mirroring or log shipping.
So I run EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1.
This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.
But I get an error:
Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
There are currently 9 connections and all are sleeping.
What else can i try in order to shrink the tlog file?
Thank You
November 24, 2014 at 2:35 am
Take a Full backup of the database then perform transaction log backups.
run the code below will help identifying what causes the log to be big
select log_reuse_wait_desc from sys.databases where name = 'DBName'
you should perform regular transaction lob backups to limit the growth of log file
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
November 24, 2014 at 2:43 am
Hi,
i belive your are shrinking the log file through query if no do it is good practice.
1st check the checkpoint than shrink tlog file through query
DBCC shrinkfile ('LOG FILE NAME', 5)
Hope it will work...
November 24, 2014 at 2:58 am
kenneth.mofokeng (11/24/2014)
Take a Full backup of the database then perform transaction log backups.
No.
Firstly the DB is in simple recovery, second the problem has nothing whatsoever to do with log backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2014 at 3:04 am
AlexSQLForums (11/21/2014)
Replicated Transaction Information:Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (2882:26:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Which means this DB is participating in a High Availability process like replication, mirroring or log shipping.
Replication or Change Data Capture.
Mirroring requires full recovery model and would show up as a log reuse wait of MIRRORING. Log shipping also requires full recovery and would show up as a log reuse of LOG BACKUP. Neither would show distributed/non-distributed LSNs
First thing to do, please check whether you have CDC (change data capture) enabled on this database.
http://msdn.microsoft.com/en-us/library/cc627369.aspx
To determine if a database is already enabled, query the is_cdc_enabled column in the sys.databases catalog view.
If it is not, the please create a transactional replication publication, publish a single article. You don't need to create the snapshot or any subscribers. Once you've created the publication, drop it. This should clean out any left-over bits of replication.
After that you should be able to shrink the log to a sensible size (please don't shrink to 1 MB). If you don't know how large it needs to be for the regular operation, then shrink it to the size of the data file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2014 at 9:21 am
Kenneth and Gail Thank you very much 🙂
is_cdc_enabled was 0 but the log_reuse_wait_desc = REPLICATION.
I followed Gail's steps and was able to resolve it.
May 19, 2015 at 3:15 pm
Thanks Gail, I ran into the same issue, it helped!
February 1, 2016 at 9:46 am
I had the same problem. What Gail recommended did the trick! thanks
July 2, 2017 at 5:54 pm
I also followed the steps by Gila, It works, TranslogFile usage comes from 67GB with 89%of usage to 1GB with 0.02% of usage, SUCH a BIG WOW.
Hatts off to Gila Guru.
June 18, 2021 at 6:25 pm
Hello. I have essentially this issue and was excited to find Gail's promising solution. However, I am working with a SQL Server Express instance and cannot create a "dummy" Publication. So now I'm stumped again. Does anyone have any suggestions for this situation?
DBCC OPENTRAN: Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (902229:26:1)
log_reuse_wait_desc = REPLICATION
sp_repldone null, null, 0,0,1 -> Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
is_cdc_enabled = 0
June 18, 2021 at 6:35 pm
Solved! I found the following on another site, for the same Express situation as mine, and it worked:
declare @db as varchar(100) = 'dbname'
exec sp_removedbreplication @db
The log space (50G!) was then deemed free and I was able to shrink. Phew!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply