473,856 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Transaction Log Cannot Be Truncated Nor Shrinked, HELP!

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
Jul 20 '05 #1
5 23115
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(logi cal_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******@hotmai l.com (Jay Chan) wrote in message news:<c7******* *************** ****@posting.go ogle.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

Jul 20 '05 #2
Jay Chan (ja******@hotma il.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
Jul 20 '05 #3
I find that there is some replication transactions that got stuck
using:
DBCC OPENTRAN(db_nam e)

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
Jul 20 '05 #4
> 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
Jul 20 '05 #5
> 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_nam e)" every morning to see if there is
any replication transactions left over from the replication in the
previous evening.

Thanks.

Jay Chan
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2112
by: Charles Nadeau | last post by:
I was trying to relocate my transaction log to a bigger drive using sp_movedevice but I made a mistake in the syntax of the second parameter and put only the path, not the path and the file name. Now my database is marked as "suspect" and I get an error message in my log upon database start up saying that the log file cannot be open. Is there a way to have MS SQL 6.5 "forget" all the logs of this database, create new ones and restart the...
5
18347
by: BashiraInTrouble | last post by:
Hi Friends, I have tried almost everything but I cant seem to shrink the transaction log. Executing DBCC SQLPERF(LOGSPACE) gives me this info: Database Log Size (MB) Log Space Used (%) Status MY_eems 368.49219 16.034182 0 I made a complete backup of the database and transaction log and then executed this statement: DBCC SHRINKFILE (MYeems_log, 1)
1
439
by: Avanish Pandey | last post by:
Hello All We have 3 differen services (in 3 different server) Service A,B,C . We want to implement distributed transaction when call methods of B and C from A. Is it possible? if yes then how? I have read the doc regarding this: http://www.developer.com/net/asp/article.php/3385631 but it will not work when methods are in different services on
2
1727
by: mokles | last post by:
Hello All, The setting is SQL server 7, on Windows NT. If the distributor can not access the log on the publisher database ( but subscribers are not deleted yet), will the full backup of the publisher database truncate the transaction log? My guess is, it will not truncate the log. Can any one confirm please? Thanks,
2
1797
by: jorgemiguel.paulino | last post by:
Hi all, Hi have a database with a huge transaction log file (plus then 13gb) and I only have 600mb disk space left. I remove the option that allows increasing log automatically, but there are some things that I don't know: 1 - If we could not see the transaction log in an easy way, why he exists? 2 - How can I delete it or how can I erase some information in order
1
725
by: Thapliyal, Deepak | last post by:
Can I use a "set transaction" type mechanism within a function? thx Deep -----Original Message----- From: Joshua D. Drake Sent: Tuesday, January 13, 2004 10:17 AM To: Thapliyal, Deepak Cc: 'Richard Huxton'; Anton.Nikiforov@loteco.ru;
16
3836
by: DataPro | last post by:
New to Sql Server, running SQL Server 2000. Our transaction log file backups occasionally fail as the size of the transaction log gets really huge. We'd like to schedule additional transaction log backups. Does that require an exclusive on the database or can the db be used during a transaction log backup? Also, does switching to a bulk mode recovery model before a bulk operation then switching back to full recovery mode after present...
2
3249
by: francois1 | last post by:
I am running a website with a SQL Server database attached. My transaction logs are full and my hosting co. won't allocate more disk space for me. I need to delete my database transaction logs and asume I will need to run an SQL script to do this. Problem: I do not have MS Enterprise Manager of any database utility on my website apart from MS Access. Where can I download a free SQL tool that I can use to delete the transaction logs...
3
2703
by: sifrah | last post by:
Hi All, My SQL server transaction log is getting bigger every day and my HDD if running out of space. So i follow the MS KB about how to Shrinking the Transaction Log. After doing so the log is much much smaller as i can see the size of it under enterprise manager. The problem is that the HDD still shows the same size. If i shrink the DB why the and reduce its size why the HDD does not
0
9906
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11051
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10694
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10774
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7929
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7088
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4571
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4171
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.