October 9, 2008 at 7:29 am
No never ....
It is the merge replication we hav never made it publication ...
this issue came wen some one changed the recovery to full from simple ... as we hav again changed the recovery to simple but still the transaction log is increasing day by day...
Plz gail provide me some thing so that i can reduce the log size....
October 9, 2008 at 10:17 am
samsql (10/9/2008)
Plz gail provide me some thing so that i can reduce the log size....
I'm trying to! That's why I'm asking so many questions to try and get to the root cause.
Please humour me and run the following again please (in the database that's giving the problems):
DBCC OPENTRAN
exec sp_repltrans -- May return a lot or rows. Please just post 1 or 2 and the number of rows it returns
exec sp_replshowcmds -- May return a lot or rows. Please just post 1 or 2 and the number of rows it returns
SELECT name, recovery_model_desc, log_reuse_wait_desc from sys.databases where database_id = @DB_ID()
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
October 9, 2008 at 10:37 pm
hi gail
... this is the output from the dbcc commands....
Transaction information for database 'B8017'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (15474:13292:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 18757, Level 16, State 6, Procedure sp_repltrans, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
Msg 18757, Level 16, State 1, Procedure sp_replshowcmds, Line 21
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
October 10, 2008 at 2:27 am
That DB definitely has traces of transactional replication from somewhere in the past.
Ok. Try this.
Create a publication (transactional replication) in that database. Doesn't matter what's included. Pick any table.
Once it's been created, drop it.
Run the following
EXEC sp_replicationdboption
@dbname = < Database Name >,
@optname = N'publish',
@value = N'false';
Run DBCC OPENTRAN again, see if the references to replicated and non-replicated LSNs has gone.
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
October 10, 2008 at 3:35 am
gail ..
its a subcription database ..... and a live database
we are using sqlexpress edition for subcription database
October 10, 2008 at 7:01 am
saby (10/10/2008)
gail ..its a subcription database ..... and a live database
we are using sqlexpress edition for subcription database
Yes, I know. You said so before.
The above commands will create and drop transactional replication, as that's what's filling the log up. Merge replication (as far as I can tell) doesn't use the tran log at all
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
October 11, 2008 at 3:15 am
Hi Gail,
This might happen due to conflicts happens some time mainly in this subcription database ... due to which it might showing transaction still pending while truncating the log....
Can i use sp_repldone and then truncate the size of the log...
October 11, 2008 at 7:45 am
saby (10/11/2008)
Hi Gail,This might happen due to conflicts happens some time mainly in this subcription database ... due to which it might showing transaction still pending while truncating the log....
Merge replication does not use the transaction log. It uses change tracking tables. Besides, you said that the merge was working properly. The results of opentran say that none of the transactions marked for replication have ever been replicated (indicated by Oldest distributed LSN = 0). That indicates that for some reason there are pieces of a transactional (not merge) replication publication that need to be cleared out
Can i use sp_repldone and then truncate the size of the log...
You can, however that is not a permanent fix. After you issue the repldone any new transactions added to the log (ie all changes made to the db) will be marked for replication and in a week or so, you'll be right back in the same situation, with a log that's growing massively and can't be shrink.
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
October 11, 2008 at 10:21 pm
gail then wat shud i do... i thing i cannot run this sp on subcription database
October 12, 2008 at 2:29 am
saby (10/11/2008)
gail then wat shud i do... i thing i cannot run this sp on subcription database
Which proc can't you run and why not?
sp_repldone and the others I gave you above affect transactional replication, not merge. Merge does not use the transaction log.
Take a backup before doing anything and it it breaks something just restore the backup.
I've said what I think you should do. If you disagree, then I'm out of suggestions as I don't know what else you could do.
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
October 14, 2008 at 6:13 am
wat shud i do ... little confused .... gail i really trust u tht is the reason i m asking u for help
October 14, 2008 at 6:39 am
As I said before, I've said what I think you should do. If you disagree, then I'm out of suggestions as I don't know what else you could do.
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
October 20, 2008 at 1:48 am
hi gail i hav tried that option but it is not working ....
October 20, 2008 at 2:43 am
What exactly have you tried and what exactly do you mean by 'it's not working'?
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 18, 2008 at 4:25 am
Hi gail thkz for the help,
repldone was not working on merge replication ...
wat i did set the database into emergerce mode
then i stoped the services and rename the log file and restarted it ....last bring the database online it created a new log file.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply