February 16, 2009 at 3:10 pm
Hi,
You have some open transaction on database.
Check it with DBCC OPENTRAN
February 16, 2009 at 4:28 pm
Nope, I don't. Here's the result:
Transaction information for database 'XXXXX'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (349:125846:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So, it seems the 'problem' is caused by non-distributed transactions.
February 17, 2009 at 12:29 am
You've got transactional replication set up and a log reader that's never run. Is that database supposed to be replicated?
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
February 17, 2009 at 1:41 pm
Exeucte the following command sp_repltrans this gives you information about transactions that are marked for replication but are not transmitted to distrbution.
If it shows zero rows then physicall verify the rowcount for each table if possible and also you can validate the publications through replication monitor, if every thing is ok, then you can execute sp_repldone stored proc, this will mark all transactions being replicated. Once this is done, you can truncate your log.
February 17, 2009 at 1:53 pm
murthykalyani (2/17/2009)
Once this is done, you can truncate your log.
Um, if this database is in full recovery and log backups are been done, then a log backup can be done, with a shrink to a suitable size afterwards
If this database is in simple recovery, then the log will auto-truncate on checkpoint and the truncation is not necessary. Just run a checkpoint and then shrink the log to a reasonable size.
Truncate log breaks log chains. Not a good idea if the DB needs point-in-time recovery.
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
February 17, 2009 at 2:10 pm
Gail I agree 100 % with you. No need to truncate log, just backup the log.
In case if by mistake it is truncated, take a differential backup if the database is huge. You can save time so that tlog backup happens.
Murthy
February 17, 2009 at 2:44 pm
The funny thing here is that the database is configured for snapshot replication, not for transactional replication. And the db is in simple recovery model.
Unfortunately, I'm stuck with another problem in a production server; so I won't be able to try your suggestions today.
Thanks for your kind replies.
February 17, 2009 at 11:28 pm
Aaron Gonzalez (2/17/2009)
The funny thing here is that the database is configured for snapshot replication, not for transactional replication.
Then there's some left-over, misconfigured transactional replication somewhere. Snapshot doesn't use the transaction logs and would not give the kind of output that OPENTRAN did here.
Was the DB restored from one that did have replication? Has transactional ever been enabled at all?
The permanent fix for something like this is to create a transactional replication publication, then drop all publications and mark the DB as not replicated. Then you can recreate your snapshot publication and check OPENTRAN to ensure there are not more traces of transactional replication.
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
July 2, 2009 at 1:10 am
hi... can you tell me weather we can shrink a DB which is in subscriber side of replication.... if YES... will it lead to any issue or do we need to take any preventive measures before doing that...
your immediate response is appreciated
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply