Transaction Log Cannot Be Truncated Nor Shrinked, HELP!

Jay

There is a logical marker in the transaction log that you have to move
to the end of the file in order to shrink it.

Try using this script, it was given to me by another dba and works
well.
Besure to have a GOOD backup and run it during a non-critical time for
you server: database_name and the logical_name_of_log need to be
replaced with the appropriate values


use database_name
go
create table shrinkfile(
col1 int,
col2 char(2048)
)

dump tran database_name with no_log
dbcc shrinkfile(logical_name_of_log, 50, TRUNCATEONLY)
go


set nocount on
declare @i int
declare @limit int

select @i = 0
select @limit = 10000

while @i < @limit
begin
insert into shrinkfile values(@i, 'Shrink the log...')
select @i = @i + 1
end

-- if needed
update shrinkfile
set col2 = 'Shrink the log again...'

--Clean up
drop table shrinkfile


Join Bytes! (Jay Chan) wrote in message news:<c7e5acb2.0310140403.6614a184@posting.google.com>...[color=blue]
> The transaction log in a database in our SQLSERVER-2000 server has
> grown to 16GB. I cannot shrink the transaction log manually because it
> says that the entire 16GB log size is not free. This is strange
> because we backup the transaction log every hour, and that should have
> truncated the transaction log, and should have limited the size of the
> transaction log; somehow, the entire transaction log is still marked
> as being used.
>
> I was under the impression
>
> I believe that must have something to do with the fact that the
> database is a part of our nightly replication. The reason is that when
> I tried the following commands, I got that error message:
>
> checkpoint
> dump transaction isprod with no_log
>
> 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.
>
> What does this mean? How can I get away from this mess?
>
> Thanks.
>
> Jay Chan[/color]

Jay Chan (jaykchan@hotmail.com) writes:[color=blue]
> The transaction log in a database in our SQLSERVER-2000 server has
> grown to 16GB. I cannot shrink the transaction log manually because it
> says that the entire 16GB log size is not free. This is strange
> because we backup the transaction log every hour, and that should have
> truncated the transaction log, and should have limited the size of the
> transaction log; somehow, the entire transaction log is still marked
> as being used.
>
> I was under the impression
>
> I believe that must have something to do with the fact that the
> database is a part of our nightly replication. The reason is that when
> I tried the following commands, I got that error message:
>
> checkpoint
> dump transaction isprod with no_log
>
> 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.
>
> What does this mean? How can I get away from this mess?[/color]

It seems that the error message gives you quite explicit instructions.

Apparently, you have set up the database as a publisher, but it does
not seem to be any replication process reading the log. Therefore the
log cannot be truncated. (And you don't really want to use NO_LOG, unless
you immediately take a full backup.)

Since you mention a nightly replication, it might be that you are
rolling your own replication, and not using the one in SQL Server.
Then again, maybe you are, in which case you should wreck the log.

So before you cut any transaction logs, you need to understand how
your system is actually set up, and what is supposed to happen.


--
Erland Sommarskog, SQL Server MVP, Join Bytes!

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

I find that there is some replication transactions that got stuck
using:
DBCC OPENTRAN(db_name)

I get rid of the replication transactions that are stuck:
use <db_name>
go
sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,
@time = 0,
@reset = 1
go

Then, I shrink the transaction log file size:
dump transaction <db_name> with no_log
go
dbcc shrinkfile (<db_name>_Log, 200)
go

Finally, I do a full-database backup. A sigh of relief...

I find the solution in this newsgroup. I should have read the messages
here before I posted my original request. Oh well...

Jay Chan

> There is a logical marker in the transaction log that you have to move[color=blue]
> to the end of the file in order to shrink it.[/color]

Turn out that was not the case. There were some unending replication
transactions stuck in the transaction log. As soon as I removed those
transactions, I could shrink the transaction log.

I am still not sure why there were unending replication transactions.
They might be coming from replication conflict caused by the same
records updated by two different database servers??? I would like to
see exactly what those replication transactions are. But I don't know
where to look.

Jay Chan

> It seems that the error message gives you quite explicit instructions.[color=blue]
>
> Apparently, you have set up the database as a publisher, but it does
> not seem to be any replication process reading the log. Therefore the
> log cannot be truncated. (And you don't really want to use NO_LOG, unless
> you immediately take a full backup.)
>
> Since you mention a nightly replication, it might be that you are
> rolling your own replication, and not using the one in SQL Server.
> Then again, maybe you are, in which case you should wreck the log.[/color]

I am using replication (both snapshot and merge) provided by MS SQL
Server.

You are correct to say that the server is the publisher.

Actually, the replication is/was doing fine. I could replicate info
between two database servers right before I removed the unending
replication transactions and shrinked the transaction log (and I had
reviewed the data in both servers to make sure that the recent data
was actually replicated). Seem like the replication is doing fine most
of the time. But in an unknown circumstance, there could have some
replication transactions left. I still don't know in which
circumstance that some replication transactions were left behind.

I will use "DBCC OPENTRAN(db_name)" every morning to see if there is
any replication transactions left over from the replication in the
previous evening.

Thanks.

Jay Chan