September 26, 2008 at 10:23 pm
we r havin merge replication implemented on our server
we are using sqlserver 2005 Express as subcriber
Recovery model simple ....
dump transaction {DatabaseName} with no_log is not working in sqlserver 2005
BACKUP LOG {DatabaseName} WITH NO_LOG is working
but i hvnt used it on subcriber database as
my log size is more than 4 gb and mdf is less then 3 gb
i want to shrink my log size to 100 mb but the issue is can i use this command BACKUP LOG {DatabaseName} WITH NO_LOG as my recovery model is simple ...
used log size is more thn 3 gb
September 28, 2008 at 7:50 am
No, the backup log command does not work with simple mode.
You would use dbcc shrinkfile.
September 28, 2008 at 9:12 am
saby (9/26/2008)
i want to shrink my log size to 100 mb but the issue is can i use this command BACKUP LOG {DatabaseName} WITH NO_LOG as my recovery model is simple ...
Backup log does not shrink logs. It just discards inactive log entries.
used log size is more thn 3 gb
Is your merge replication working? Is the log reader up to date? If the log reader is not working, then log records will be retained, even under simple recovery, in order to facilitate the replication.
Please run the following and post the results
DBCC OPENTRAN
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
September 28, 2008 at 9:00 pm
yes gail replication is working..
.... gail few week back my data twice got currpted
............ so i have runed dbcc checkdb thrice in two weeks..
can i switch my database to full recovery model..
so that i can back up a log and then shrink the log file and again make it to simple .
dbcc opentran output
Transaction information for database 'tbloy'.
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.
September 29, 2008 at 1:35 pm
saby (9/28/2008)
yes gail replication is working..Transaction information for database 'tbloy'.
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.
From that I have to say that your replication is not working, or that you have a transactional replication publication that's not working.
That's saying that there's a lot of transactions that need to be replicated and that this DB has never replicated a transaction successfully. If it had, the oldest distributed LSN would have a value.
.... gail few week back my data twice got currpted
............ so i have runed dbcc checkdb thrice in two weeks..
can i switch my database to full recovery model..
so that i can back up a log and then shrink the log file and again make it to simple .
In simple the log auto truncates, so there's no need to switch to full to do a log backup. In fact, you wouldn't be able to unless you did a full backup as well.
Shrink's not going to do much until you fix the replicated transactions
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
September 29, 2008 at 10:17 pm
gail we r having merge replication implemented .... actually wat happen is some one changed the recovery model to full and then changed it to simple so this might be the reason for increased in the log size...
......... plz provide me the solution..
September 30, 2008 at 2:52 am
That alone won't cause the log to be full, which it appears to be.
The solution is to sort out the replication and find out why you have so many undistributed transactions. What kind of publications exist on this DB?
please run sp_helppublication in that database and post what it returns
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
September 30, 2008 at 6:29 am
gail,fix replication first
September 30, 2008 at 6:42 am
Vinesh (9/30/2008)
gail,fix replication first
What do you mean by this?
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 2, 2008 at 11:25 pm
gail it is showing sucessfully complted ...
plz provide me the suggestion
October 3, 2008 at 1:32 am
So help_publication doesn't show any result set 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 4, 2008 at 12:17 am
gail it is not giving me any result
October 4, 2008 at 2:03 am
That's odd, because merge replication doesn't use the transaction log. Transactional replication does to track changes from the publisher. According to the opentran output you posted earlier, there are definitly t4ransactions in the log marked for replication.
Is there a chance that this DB ever was published in the past (transactional replication)? Could it have been restored/moved from a server where there was replication?
Can you run this and post the results?
exec sp_helpreplicationdboption
exec sp_replcounters
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 8, 2008 at 8:13 am
hi gail this is what i hav got from the sp s out
sp_replcounters
database
replicated transactions
replication rate trans/sec
replication latency (sec)
replbeginlsn
replnextlsn
sp_helpreplicationdboption
name
id
transpublish
mergepublish
dbowner
dbreadonly
FicRuralHO
1
0
1
1
0
October 8, 2008 at 8:24 am
Is there a chance that this DB ever was published in the past (transactional replication)? Could it have been restored/moved from a server where there was 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
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply