Transaction Log Cannot Be Truncated Nor Shrinked, HELP!

[Jay Chan]

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

[david_0]

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
ja******@hotmail.com (Jay Chan) wrote in message news:<c7**************************@posting.google. com>...

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

[Erland Sommarskog]

Jay Chan (ja******@hotmail.com) writes:

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?

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, so****@algonet.se

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

[Jay Chan]

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

[Jay Chan]

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

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

[Jay Chan]

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

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