MS SQL Server - Truncate Log File and Pending Replication

Author

Truncate Log File and Pending Replication

Carlos Eduardo Selonke de Souza

2005-07-27, 8:23 pm

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

Narayana Vyas Kondreddi

2005-07-28, 7:23 am

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

Carlos Eduardo Selonke de Souza

2005-07-28, 9:23 am

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
>
>
>

Narayana Vyas Kondreddi

2005-07-28, 9:23 am

Just 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
>
>
>

Sponsored Links