Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
Truncate Log File and Pending ReplicationHello I have restored a database from production to development. On the production server the database is configured with transactional replication. On the development server there is no need to maintain replication. I have restored the database without keeping replication, change the recovery model to simple, and tried to truncate the log to a smaller size (its 12 Gbytes on the production server). When I try to truncate the log I got the message: "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." I checked sp_dboption 'databasename' and it is not configured as a repl database. I also executed sp_repldone (EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1) but got the message: "Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 The database is not published." I also notice that are the dbcc opentan shows: "Replicated Transaction Information: Oldest distributed LSN : (133810:177754:123) Oldest non-distributed LSN : (133811:126175:1) DBCC execution completed. If DBCC printed error messages, contact your system administrator." I read that one way to solve this problema is to detach, delete the log file, use attach_single_file and SQL Server will create a new log file. The problem is that my database have 3 log files and because that I cant use sp_attach_single_fil e_db. The production server is running SQL Server 2000 64 bits SP3a. Development server is running SQl Server 2000 32bits SP4. Does anyone have any idea how to solve that issue? Any help would be appreciated. Regards -- Carlos Eduardo Selonke de Souza http://carlos.geekbunker.org
Post Follow-up to this message
Re: Truncate Log File and Pending ReplicationCan you try marking the database as published and then try repldone and see if it clears the log? EXEC sp_dboption 'DatabaseName', 'Publish', 'true' GO EXEC sp_repldone.................. -- HTH, Vyas, MVP (SQL Server) SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ "Carlos Eduardo Selonke de Souza" < CarlosEduardoSelonke deSouza@discussions.microsoft.com> wrote in message news:ADD11B4F-E043-4D31-8482- 0EB7B62DF7C9@microso ft.com... Hello I have restored a database from production to development. On the production server the database is configured with transactional replication. On the development server there is no need to maintain replication. I have restored the database without keeping replication, change the recovery model to simple, and tried to truncate the log to a smaller size (its 12 Gbytes on the production server). When I try to truncate the log I got the message: "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." I checked sp_dboption 'databasename' and it is not configured as a repl database. I also executed sp_repldone (EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1) but got the message: "Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 The database is not published." I also notice that are the dbcc opentan shows: "Replicated Transaction Information: Oldest distributed LSN : (133810:177754:123) Oldest non-distributed LSN : (133811:126175:1) DBCC execution completed. If DBCC printed error messages, contact your system administrator." I read that one way to solve this problema is to detach, delete the log file, use attach_single_file and SQL Server will create a new log file. The problem is that my database have 3 log files and because that I cant use sp_attach_single_fil e_db. The production server is running SQL Server 2000 64 bits SP3a. Development server is running SQl Server 2000 32bits SP4. Does anyone have any idea how to solve that issue? Any help would be appreciated. Regards -- Carlos Eduardo Selonke de Souza http://carlos.geekbunker.org
Post Follow-up to this message Re: Truncate Log File and Pending ReplicationHello It worked as you said. Since I do not want replication on the developmente server, after that I revove the distributor from the server. Is ther any way to avoid that problem? -- Carlos Eduardo Selonke de Souza http://carlos.geekbunker.org "Narayana Vyas Kondreddi" wrote: > Can you try marking the database as published and then try repldone and se e > if it clears the log? > > EXEC sp_dboption 'DatabaseName', 'Publish', 'true' > GO > EXEC sp_repldone.................. > -- > HTH, > Vyas, MVP (SQL Server) > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > "Carlos Eduardo Selonke de Souza" > < CarlosEduardoSelonke deSouza@discussions.microsoft.com> wrote in message > news:ADD11B4F-E043-4D31-8482- 0EB7B62DF7C9@microso ft.com... > Hello > > I have restored a database from production to development. > On the production server the database is configured with transactional > replication. On the development server there is no need to maintain > replication. > > I have restored the database without keeping replication, change the > recovery model to simple, and tried to truncate the log to a smaller size > (its 12 Gbytes on the production server). > > When I try to truncate the log I got the message: > "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." > > I checked sp_dboption 'databasename' and it is not configured as a repl > database. > I also executed sp_repldone (EXEC sp_repldone @xactid = NULL, @xact_segno = > NULL, @numtrans = 0, @time = 0, @reset = 1) but got the message: > "Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 The database is > not published." > I also notice that are the dbcc opentan shows: > "Replicated Transaction Information: > Oldest distributed LSN : (133810:177754:123) > Oldest non-distributed LSN : (133811:126175:1) > DBCC execution completed. If DBCC printed error messages, contact your > system administrator." > > I read that one way to solve this problema is to detach, delete the log > file, use attach_single_file and SQL Server will create a new log file. Th e > problem is that my database have 3 log files and because that I cant use > sp_attach_single_fil e_db. > > The production server is running SQL Server 2000 64 bits SP3a. > Development server is running SQl Server 2000 32bits SP4. > > Does anyone have any idea how to solve that issue? > Any help would be appreciated. > > Regards > > -- > Carlos Eduardo Selonke de Souza > http://carlos.geekbunker.org > > >
Post Follow-up to this message Re: Truncate Log File and Pending ReplicationJust run sp_dboption 'DBName', 'publish', 'false' -- HTH, Vyas, MVP (SQL Server) SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ "Carlos Eduardo Selonke de Souza" < CarlosEduardoSelonke deSouza@discussions.microsoft.com> wrote in message news:A8ECA39F-8B7D-4F42-A46C- F74360842647@microso ft.com... Hello It worked as you said. Since I do not want replication on the developmente server, after that I revove the distributor from the server. Is ther any way to avoid that problem? -- Carlos Eduardo Selonke de Souza http://carlos.geekbunker.org "Narayana Vyas Kondreddi" wrote: > Can you try marking the database as published and then try repldone and see > if it clears the log? > > EXEC sp_dboption 'DatabaseName', 'Publish', 'true' > GO > EXEC sp_repldone.................. > -- > HTH, > Vyas, MVP (SQL Server) > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > "Carlos Eduardo Selonke de Souza" > < CarlosEduardoSelonke deSouza@discussions.microsoft.com> wrote in message > news:ADD11B4F-E043-4D31-8482- 0EB7B62DF7C9@microso ft.com... > Hello > > I have restored a database from production to development. > On the production server the database is configured with transactional > replication. On the development server there is no need to maintain > replication. > > I have restored the database without keeping replication, change the > recovery model to simple, and tried to truncate the log to a smaller size > (its 12 Gbytes on the production server). > > When I try to truncate the log I got the message: > "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." > > I checked sp_dboption 'databasename' and it is not configured as a repl > database. > I also executed sp_repldone (EXEC sp_repldone @xactid = NULL, @xact_segno = > NULL, @numtrans = 0, @time = 0, @reset = 1) but got the message: > "Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 The database is > not published." > I also notice that are the dbcc opentan shows: > "Replicated Transaction Information: > Oldest distributed LSN : (133810:177754:123) > Oldest non-distributed LSN : (133811:126175:1) > DBCC execution completed. If DBCC printed error messages, contact your > system administrator." > > I read that one way to solve this problema is to detach, delete the log > file, use attach_single_file and SQL Server will create a new log file. The > problem is that my database have 3 log files and because that I cant use > sp_attach_single_fil e_db. > > The production server is running SQL Server 2000 64 bits SP3a. > Development server is running SQl Server 2000 32bits SP4. > > Does anyone have any idea how to solve that issue? > Any help would be appreciated. > > Regards > > -- > Carlos Eduardo Selonke de Souza > http://carlos.geekbunker.org > > >
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
