June 8, 2003 at 7:55 am
sql server 2000, standard edition
I have a log file that has grown to 30 gb. I tried to shrink it but it will not shrink. I ran the DBCC Loginfo and the FILEID = 2, which I read will prevent the log file from shrinking.
SO what I am trying to do is force sql server to use space at the beginning of the log file, rather than growing it. Then shrink it.
1 of 4 steps for this is to truncate the log:
BACKUP LOG xyz WITH TRUNCATE_ONLY
I am getting an error that says "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_repdone to to mark transactions as distributed."
Help!!!!!
Detailed steps to resolve this please.
June 8, 2003 at 3:51 pm
Get the logreader running and keep it running. The default behavior is to not remove records from the log until the logreader has processed, preventing transactions from being missed as far as replication goes. Rebuilding indexes can generate a ton of log entries that don't pertain to replication, yet the log reader still have to clear them. Usually as long as the reader runs with the -continuous flag you'll never even notice. Turn it off, well, you have to work harder sometimes!
Once you get the error about replication to clear, run a transaction log backup, then run the dbcc shrinkfile, then run a second transaction log backup.
Andy
June 8, 2003 at 9:23 pm
This is snaphot replication. Should the logreader still be running continously?
June 8, 2003 at 9:37 pm
Note:
It's the Publisher database that I am concerned about there.(30 gb log file and growing.)
June 9, 2003 at 5:10 am
You only need a logreader for transactional - pretty common to use transactional just to get the concurrent snapshot. Do you have any other publications set up on the publisher db?
If you don't have much replication in the publisher going on, try dropping all pubs and marking the db as not replicated (wizard is best way), see if that clears the log.
Andy
June 9, 2003 at 7:19 am
isn't there an easier way?
When I run DBCC LOGINFO(db), the FILEID shows 2 for about 600 rows. This is the reason it will not shrink correct?
June 9, 2003 at 8:30 am
HELP!!!!!!!!!!!!!
June 9, 2003 at 10:32 am
I do not want to delete all the publications at this point.
Is there another way to shrink this log?
June 9, 2003 at 11:16 am
Tried sp_repldone per the error message you got?
Andy
June 9, 2003 at 12:24 pm
will this work for snapshot replication?
what are the effects of running this?
are there any parameters I should include for this procedure?
June 9, 2003 at 1:32 pm
sp_repldone
Updates the record that identifies the last distributed transaction of the server. This stored procedure is executed at the Publisher on the publication database.
Syntax
sp_repldone [ @xactid = ] xactid
, [ @xact_seqno = ] xact_seqno
[ , [ @numtrans = ] numtrans ]
[ , [ @time = ] time
[ , [ @reset = ] reset ]
Arguments
[@xactid =] xactid
Is the log sequence number (LSN) of the first record for the last distributed transaction of the server. xactid is binary(10), with no default.
[@xact_seqno =] xact_seqno
Is the LSN of the last record for the last distributed transaction of the server. xact_seqno is binary(10), with no default.
[@numtrans =] numtrans
Is the number of transactions distributed. numtrans is int, with no default.
[@time =] time
Is the number of milliseconds, if provided, needed to distribute the last batch of transactions. time is int, with no default.
[@reset =] reset
Is the reset status. reset is int, with no default. If 1, all replicated transactions in the log are marked as distributed. If 0, the transaction log is reset to the first replicated transaction and no replicated transactions are marked as distributed. reset is valid only when both xactid and xact_seqno are NULL.
Examples
When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log, for example:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
Caution This procedure can be used in emergency situations to allow truncation of the transaction log when transactions pending replication are present. Using this procedure prevents Microsoft® SQL Server™ 2000 from replicating the database until the database is unpublished and republished.
Andy
June 9, 2003 at 1:35 pm
"Using this procedure prevents Microsoft® SQL Server™ 2000 from replicating the database until the database is unpublished and republished."
How does one do a "unpublished and republished"?.
June 9, 2003 at 1:46 pm
When you truncate the log file it should reclaim all the virtual log space allocated
for the log file, but not the physical disk space.
In order to reclaim actual physical disk space you need to use DBCC SHRIKFILE
Run this command, but do not specify the target size to be too low in (MB). Do it
in increments
MW
MW
June 9, 2003 at 9:40 pm
Is there a way to just easily disable snapshot replication?
June 10, 2003 at 9:19 am
You can disable the snap shot agent on the Publishing server
MW
MW
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply