September 9, 2008 at 2:01 am
HI,
I am new to replication, but have more SQL/Server experience than the other two guys here.
I have a database with 1GB data and a 22GB Log file. Our storage guys are complaining about the size of the log file and I would like to reduce it.
The database is in simple recovery mode. As far as I can tell, the replication is using the Snapshot model.
I have the following jobs (amongst others) set up on the provider:
HSV2051S\PRICING-Rating-HSV2070S\EPICARD-7 Category: REPL-Distribuion, scheduled daily at 22:45
HSV2051S\PRICING-Rating-Rating_Publication1-5 Category: REPL-Snapshot, scheduled daily at 22:46
When I look on the subscriber machine under replication / SudscriptionsI see
[font="Courier New"]Name Database Type Status Last Action Last Updated Replication Type Description
HSV2051S\PRICING: Pricing: Pricing Push Succeeded No replicated transactions are available. 20070531 14:50:07.747 Transactional
HSV2051S\PRICING: Rating: Rating Push Succeeded Applied the snapshot to the Subscriber. 20080908 22:47:23.050 Transactional[/font]
A job has been set up to shrink the log using the following commands
[font="Courier New"]use Rating
go
backup log Rating with no_log
go
dbcc shrinkfile (Rating_Log,2000)
go[/font]
, but this terminates with the message
"[SQLSTATE 01000] (Message 2528) The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. [SQLSTATE 01000] (Message 4215) Cannot shrink log file 2 (Rating_Log) because all logical log files are in use. [SQLSTATE 01000] (Message 9008) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded."
When I do a [font="Courier New"]DBCC opentran [/font]I get the following output:
Transaction information for database 'Rating'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (129712:408:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Looking at the undistributed transactions with
[font="Courier New"]sp_browsereplcmds @xact_seqno_start = '0x00001E4700019FBD00CB00000005' [/font]
gives the following output:
[font="Courier New"]xact_seqno originator_id publisher_database_id article_id type command
---------------------------------- ------------- --------------------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x00019F230000016D01A8000001CF 0 5 0 -2147483641 \\HSV2051S\d$\datasql2k\pricing\MSSQL$PRICING\ReplData\unc\HSV2051S$PRICING_Rating_Rating_Publication1\20080908224603\
0x00019F230000016D01A8000001CF 0 5 29 -2147483598 \\HSV2051S\d$\datasql2k\pricing\MSSQL$PRICING\ReplData\unc\HSV2051S$PRICING_Rating_Rating_Publication1\20080908224603\
0x00019F230000016D01A8000001CF 0 5 29 -2147483597
0x00019F230000016D01A8000001CF 0 5 29 -2147483646 \\HSV2051S\d$\datasql2k\pricing\MSSQL$PRICING\ReplData\unc\HSV2051S$PRICING_Rating_Rating_Publication1\20080908224603\TblDossier_105.sch
0x00019F230000016D01A8000001CF 0 5 29 -2147483646 \\HSV2051S\d$\datasql2k\pricing\MSSQL$PRICING\ReplData\unc\HSV2051S$PRICING_Rating_Rating_Publication1\20080908224603\TblDossier_106.idx
0x00019F230000016D01A8000001CF 0 5 29 -2147483645 sync -t"TblDossier" -d"\\HSV2051S\d$\datasql2k\pricing\MSSQL$PRICING\ReplData\unc\HSV2051S$PRICING_Rating_Rating_Publication1\20080908224603\TblDossier_104.bcp" -u
0x00019F230000016D01A8000001CF 0 5 30 -2147483646 \\HSV2051S\d$\datasql2k\pricing\MSSQL$PRICING\ReplData\unc\HSV2051S$PRICING_Rating_Rating_Publication1\20080908224603\DtaTbl99_5.sch
0x00019F230000016D01A8000001CF 0 5 30 -2147483646 \\HSV2051S\d$\datasql2k\pricing\MSSQL$PRICING\ReplData\unc\HSV2051S$PRICING_Rating_Rating_Publication1\20080908224603\DtaTbl99_6.idx[/font]
the same for the other publications
I was thinking of doing a sp_replcomplete. Is this the correct thing to do? Do I need to reinitialise the subscriber after?
Any other good ideas / pointers?
Thanks in advance for your help
Otto
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 9, 2008 at 2:31 am
You have transactional replication there, not snapshot. Check that the log reader agent is running.
It should be a job, you should also be able to see it from enterprise manager (on the publisher) under replication
You can use sp_repldone but that should be the last resort when nothing else works. If you do, you will have to completely reinitialise the 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
September 9, 2008 at 2:35 am
Otto,
the replication you're dealing with is transactional replication. With transactional replication transaction are kept in the logfile until they are succesfully applied at the subscriber. For some reason in your case no transaction have been sent to the subscriber.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
This basically means that not one transaction has been synchronized. It's difficult to determine why the transactions aren't replicated but you should first check if the snapshot has been applied succesfully or not. If that was succesful, the next step is to check if and when the Log Reader agent nad the distribution agent have been scheduled.
But anyway in your case I would suggest removing replication and set it up again. The backlog of transactions is so big, that it's much faster to recreate publication and subscriptions.
[font="Verdana"]Markus Bohse[/font]
September 9, 2008 at 4:07 am
Thank you both for your responses.
I'm still trying to understand what's going on (or not, as the case might be).
I don't see a log reader job - which is why I was under the impression the replication was a snapshot.
what should I be looking for?
I don't see any error indications on either the publisher or the subscriber.
This is the output from Replication Monitor / Publishers / Rating_Publication1:Rating
[font="Courier New"]Rating_Publication1 Agents Type Status Last Action Action Time Start Time Duration Delivery Rate (cmds/sec) Latency (msec) #Trans #Cmds Avg. #Cmds
Snapshot Succeeded A snapshot of 43 article(s) was generated. 20080908 22:50:02.500 20080908 22:46:02.543 00:04:00 0.0000 1 00
HSV2070S\EPICARD:Rating Push Succeeded Applied the snapshot to the Subscriber. 20080908 22:47:22.107 20080908 22:45:05.630 00:02:17 9.0000 86260347 1 132132[/font]
No mention of a log reader.
If I look under Replication Monitor / Agents / Log Reader Agents, there are none.
It looks like the snapshots are working. Dumb question: even if the snapshots are being applied, are the (stale) transactions still backed up waiting to be applied?
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 9, 2008 at 6:17 am
Well, if the log reader's missing/deleted that would explain the problem with the log. It's definitely a transactional replication, not snapshot.
Can see that from the Replication Type column in the initial information that you posted.
Snapshots don't apply transactions. They apply the copy of the DB. In transactional replication, the log reader reads transactions from the transaction log
and copies them to the distributor. The distributor then applies them to the subscriber.
If the log reader's completely missing, you're best approach here is to probably drop the replication completely, and make sure that all traces are removed from the publisher.
Once that's done, DBCC OPENTRAN should no longer have mentions of distributed transactions. Then you should be able to shrink the log to a reasonable size and recreate the 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
September 9, 2008 at 6:27 am
Thanks again.
I'll try deleting and redefining the replication.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 10, 2008 at 2:44 am
Here are the instructions I put together for the unpublish and republish of the databases. I'd be grateful for any comments.
Have I missed anything? For example: do I need to delete the existing push jobs from the Agent?
Back up user databases
start job 'HP Daily Backup user databases' in the agent
Back up system databases
start job 'HP Daily Backup system databases' in the agent
Mark the pending transactions in database Rating as delivered.
Execute script
[font="Courier New"]use Rating
go
exec sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
Go
DBCC OpenTran
Go[/font]
shrink the log file
start job 'BkpLogWithNo_Log' in the agent
Unpublish database Rating
In Enterprise Manager:
Select Tools -> Replication -> Create and manage Publications
Expand Pricing2
Click on Pricing2_publication1.
Click Delete
Republish Database Rating
Follow the instructions in http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm
Starting at "Create and manage publications"
Select database Rating instead of pubs."
Publish the following 43 tables:
DtaTbl00
...(snip)
TblDossier
Name the publication 'Rating_Publication1'
Do not elect to create filters or other properties.
Create a push schedule for database Rating
Create a push subscription to HSV2070S (Continue with the setup instructions)
Select database rating instead of pubs
Schedule the distribution for every 10 minutes
Choose to initialise the data
Check the schedule of the Rating snapshot job
Schedule daily at 22:45
Back up the distribution database
Back up the distribution database
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 17, 2008 at 12:25 am
I thought I'd post a follow-up.
It turned out the replication in question was set up as a snapshot replication. I think a predecessor had tried to set up transactional replication, but this was unsuccessful because there are no primary keys (!? - not my design) on the tables being replicated. He then successfully implemented snapshot replication. The unsuccessful transaction replication seems to have left traces behind in the DB, evidenced by the [font="Courier New"]DBCC Opentran [/font]output.
I did the database maintenance to remove and recreate the replication using the instructions above. The only addition was to run [font="Courier New"]sp_removedbreplication [/font]on the database after removing the snapshot replication, as the [font="Courier New"]DBCC opentran[/font] was still showing undistributed transactions. The maintenance was successful, the log file size is now 28 MB instead of 20GB.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 17, 2008 at 12:52 am
Good to hear.
Yeah, replication can be a pain sometimes. Glad you came right.
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 23, 2008 at 9:55 am
Question - In SQL2K you would set the replicationdboption "Sync with backup" to True in order to get the commands to wait in the log until a backup of them had taken place. Is this standard behaviour in 2005 or do you still need to change this setting? Reading above that sounds the same.
September 25, 2009 at 8:32 am
Hi Otto
Was just about to post an answer to this, when I A saw that it had allready been answred.
And B saw that the name was very familyer to me.
Dave
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply