Log File Issue

I am still struguling with shrinking a log file that is
almost 5GB. The database is being replicated using merge
replication. I ran the following commands without any
success
backup log Main_DB with truncate_only
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.
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Server: Msg 18757, Level 16, State 1, Procedure
sp_repldone, Line 1
The database is not published.
dbcc opentran
Transaction information for database 'Main_DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (305:22434:1)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
How do I go about shrinking/truncating the log file
without having to remove replication from the database or
is removing and reestablishing replication my only
solution?
Thanks
Emma
Open a support case with PSS.
There is no such thing as a pending replicated transaction with merge as it
does not use the tran log. There is also no such thing as a log reader with
merge either. Sp_repldone won't do anything since it operates against the
distribution database and will have no effect with merge since merge doesn't
use the distribution database for transactions.
You have something else going on and if you don't have transactional
replication configured, you did at some point and something has been left
hanging.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||What happens if you stop and start SQL Server, and then try to truncate the
log?
"Emma" <eeemore@.hotmail.com> wrote in message
news:791d01c4312f$425272a0$a501280a@.phx.gbl...
> I am still struguling with shrinking a log file that is
> almost 5GB. The database is being replicated using merge
> replication. I ran the following commands without any
> success
> backup log Main_DB with truncate_only
> 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.
>
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
> Server: Msg 18757, Level 16, State 1, Procedure
> sp_repldone, Line 1
> The database is not published.
>
> dbcc opentran
> Transaction information for database 'Main_DB'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (305:22434:1)
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
>
> How do I go about shrinking/truncating the log file
> without having to remove replication from the database or
> is removing and reestablishing replication my only
> solution?
> Thanks
> Emma
|||Hilary,
The same thing happens when I stop and restart the
service.
Emma

>--Original Message--
>What happens if you stop and start SQL Server, and then
try to truncate the[vbcol=seagreen]
>log?
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:791d01c4312f$425272a0$a501280a@.phx.gbl...
merge[vbcol=seagreen]
the[vbcol=seagreen]
messages,[vbcol=seagreen]
or
>
>.
>
|||Michael,
What is PSS and how do I contact them?
Thanks
Emma

>--Original Message--
>Open a support case with PSS.
>There is no such thing as a pending replicated
transaction with merge as it
>does not use the tran log. There is also no such thing
as a log reader with
>merge either. Sp_repldone won't do anything since it
operates against the
>distribution database and will have no effect with merge
since merge doesn't
>use the distribution database for transactions.
>You have something else going on and if you don't have
transactional
>replication configured, you did at some point and
something has been left
>hanging.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||Emma,
this is the relevant webpage:
http://support.microsoft.com/default.aspx?scid=fh;en-
us;Prodoffer41a&sd=MVP
Regards,
Paul
|||I setup a standby/test server with the database and
removed replication completely and recreated it and I was
able to shrink the log file. There must have been a
problem in the original setup of the database or from the
applications accessing the database living transactions
open. I will go through the process again and document
everything I do, and hope it works on the production
server.
I will have to find the appropriate time to try this on
the production server as well. It will be a pain doing
this on the production server because there are about 12
publications there. I do not want to script the
publications because if there is an error in the original
setup, it may be carried over.
Thanks for all your help.
Emma
|||Hi all,
I had a similar problem but seemed to solve it by backing up the database first; the documentation suggests the log needs to be checkpointed and that occurs on a backup. I did this through the Query Analyzer; can't vouch that this works via Enterprise Manager or SQLDMO.
/* Backup */
use master
exec sp_addumpdevice 'disk', 'databak', 'c:\databak.dat'
exec sp_addumpdevice 'disk', 'logbak', 'c:\logbak.dat'
backup database target to databak
backup log target to logbak
/* Shrink log */
use target
declare @.fid int
select @.fid = File_ID('target_log')
dbcc shrinkfile (@.fid)|||Hi everyone,
I got a solution to this issue. I got stuck with this problem when I restored a database from a production server to a development server. On Production server this database was being used in replication also.
I restored this database from production server to development server and after that I wanted to truncate the log file of it on production server.
When I executed this query:
Backup Log <MyDatabaseName> With Truncate_Only
I got this nice 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 used sp_repldone then I got the message:
Database is not published.
DBCC Shrinkfile also did not work.
Then I tried a trick, and guys it worked. What I did, I am writing in steps:
1. I published this database using the following query.
Execute SP_ReplicationDbOption <MyDatabaseName>,Publish,True,1
Here 1 is used for the parameter @.ignore_distributor, which can be 0 or 1.
If it set to 0 then this stored procedure will try to connect to ditributor database and update it with the new status of publication database. I used '1' because as I told earlier I restored my database on development server and so there was no distribution database.
2. Then I used that so called 'SP_Repldone' to mark the logs as distributed as given below:
Execute sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time = 0, @.reset = 1
3. Then I used DBCC ShrinkFile(<MydatabaseLogFileName>,0)
The log was shrinked successfully.
So, this was all the happy story guys.
sujeet4u <s.sp007@.yahoo.co.in>