log file shrinking problems

Hello,
I have currently problems shrinking transaction log files in my ms-sql = server 2000 DB. The problems started after moving to new hardware, so = there was a fresh install of ms sql2k and the db's were created from = backups taken from the old system.
The usual steps :
backup log to file
dbcc shrinkfile( dbname, targetsize)
don't shrink the logfile anymore. I can still do "backup log with = truncate_only", that will reduce the logfile to its original size, but = that is just an emergency procedure to re-claim some HD space. I do = need full recovery mode and have a sequence of full, differential, and = log backups worked out that used to work just fine.
I have read all the microsoft and other articles on how virtual logfiles = work and how to successfully shrink logfiles, indeed I did this without = problems on the old system.
Here is the interesting bit and probably the cause of my problem. When = doing "DBCC OPENTRAN" on the db in question I get this output:
Transaction information for database 'customerinformation'.
Replicated Transaction Information:
Oldest distributed LSN : (1739:217:2)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your = system administrator.
I'm not exactly sure what this means but have read that open replication = type transactions will prevent shrinking of the logfile. This looks = like one to me. BUT....
The sql server I'm using doesn't have any replication enabled. It is = neither a publisher nor a distributor nor a subscriber. I never touched = replication since sql server got installed.
Does anyone know how to fix this? Anyone have any idea how this could = have happened? Any advice/hints would be greatly appreciated, = maintaining backups at he moment is an administrative nightmare.
BTW, the problem applies to all user DB's on that server, not just = 'customerinformation'.
Thanks
Ray
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004when you have an oldest non-distributed LSN value of 0, it means that the
log reader has read all of the transactions from the log and marked them as
replicated. You can truncate your log now.
I can't explain why this database thinks it is being replicated. You might
want to do this
sp_replicationdboption 'databasename','published','false'
and see what happens. It should drop any publications in this database or
any related replication metadata if there is any.
"Raymond Delevaux" <ray@.seekmedia.com> wrote in message
news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
Hello,
I have currently problems shrinking transaction log files in my ms-sql
server 2000 DB. The problems started after moving to new hardware, so there
was a fresh install of ms sql2k and the db's were created from backups taken
from the old system.
The usual steps :
backup log to file
dbcc shrinkfile( dbname, targetsize)
don't shrink the logfile anymore. I can still do "backup log with
truncate_only", that will reduce the logfile to its original size, but that
is just an emergency procedure to re-claim some HD space. I do need full
recovery mode and have a sequence of full, differential, and log backups
worked out that used to work just fine.
I have read all the microsoft and other articles on how virtual logfiles
work and how to successfully shrink logfiles, indeed I did this without
problems on the old system.
Here is the interesting bit and probably the cause of my problem. When
doing "DBCC OPENTRAN" on the db in question I get this output:
Transaction information for database 'customerinformation'.
Replicated Transaction Information:
Oldest distributed LSN : (1739:217:2)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I'm not exactly sure what this means but have read that open replication
type transactions will prevent shrinking of the logfile. This looks like
one to me.
BUT....
The sql server I'm using doesn't have any replication enabled. It is
neither a publisher nor a distributor nor a subscriber. I never touched
replication since sql server got installed.
Does anyone know how to fix this? Anyone have any idea how this could have
happened? Any advice/hints would be greatly appreciated, maintaining
backups at he moment is an administrative nightmare.
BTW, the problem applies to all user DB's on that server, not just
'customerinformation'.
Thanks
Ray
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004|||Thanks for your answer, Hilary.
It appears that replication was at one time enabled on the original =server, and since the new server was loaded with backups from the =original, replicated transaction information seems to remain embedded in =the logs. When I detach and then re-attach the DB, letting it build a =new logfile, the replicated transaction info is gone for good from the =log.
I've applied the sp you suggested and some other replication related =sp's and am confident the DB's concerned are not enabled for replication =and never were.
That proves my theory wrong of course, from you I learnt that there =weren't any open transactions in the logs so this wasn't the reason why =my log files don't shrink. At least thats eliminated and I can dig =elsewhere now.
Thank you
Ray
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message =news:ec2xoBktEHA.3292@.TK2MSFTNGP12.phx.gbl...
> when you have an oldest non-distributed LSN value of 0, it means that =the > log reader has read all of the transactions from the log and marked =them as > replicated. You can truncate your log now.
> > I can't explain why this database thinks it is being replicated. You =might > want to do this
> > sp_replicationdboption 'databasename','published','false'
> > and see what happens. It should drop any publications in this database =or > any related replication metadata if there is any.
> > "Raymond Delevaux" <ray@.seekmedia.com> wrote in message > news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
> > I have currently problems shrinking transaction log files in my ms-sql =
> server 2000 DB. The problems started after moving to new hardware, so =there > was a fresh install of ms sql2k and the db's were created from backups =taken > from the old system.
> > The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
> > don't shrink the logfile anymore. I can still do "backup log with > truncate_only", that will reduce the logfile to its original size, but =that > is just an emergency procedure to re-claim some HD space. I do need =full > recovery mode and have a sequence of full, differential, and log =backups > worked out that used to work just fine.
> > I have read all the microsoft and other articles on how virtual =logfiles > work and how to successfully shrink logfiles, indeed I did this =without > problems on the old system.
> > Here is the interesting bit and probably the cause of my problem. =When > doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
> > Replicated Transaction Information:
> Oldest distributed LSN : (1739:217:2)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your =
> system administrator.
> > I'm not exactly sure what this means but have read that open =replication > type transactions will prevent shrinking of the logfile. This looks =like > one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is > neither a publisher nor a distributor nor a subscriber. I never =touched > replication since sql server got installed.
> > Does anyone know how to fix this? Anyone have any idea how this could =have > happened? Any advice/hints would be greatly appreciated, maintaining > backups at he moment is an administrative nightmare.
> > BTW, the problem applies to all user DB's on that server, not just > 'customerinformation'.
> > Thanks
> > Ray
> > > > > --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004 > > Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19/10/2004