I have truncated the log file and then used the DBCC Shrinkfile command to
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.
|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.
Showing posts with label shrinking. Show all posts
Showing posts with label shrinking. Show all posts
Wednesday, March 28, 2012
log grows after shrinking
I have truncated the log file and then used the DBCC Shrinkfile command to
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.sql
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.sql
Friday, March 23, 2012
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. =20
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. =20
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
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
|||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=20
> log reader has read all of the transactions from the log and marked =
them as=20
> replicated. You can truncate your log now.
>=20
> I can't explain why this database thinks it is being replicated. You =
might=20
> want to do this
>=20
> sp_replicationdboption 'databasename','published','false'
>=20
> and see what happens. It should drop any publications in this database =
or=20
> any related replication metadata if there is any.
>=20
> "Raymond Delevaux" <ray@.seekmedia.com> wrote in message=20
> news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
>=20
> 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=20
> was a fresh install of ms sql2k and the db's were created from backups =
taken=20
> from the old system.
>=20
> The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
>=20
> don't shrink the logfile anymore. I can still do "backup log with=20
> truncate_only", that will reduce the logfile to its original size, but =
that=20
> is just an emergency procedure to re-claim some HD space. I do need =
full=20
> recovery mode and have a sequence of full, differential, and log =
backups=20
> worked out that used to work just fine.
>=20
> I have read all the microsoft and other articles on how virtual =
logfiles=20
> work and how to successfully shrink logfiles, indeed I did this =
without=20
> problems on the old system.
>=20
> Here is the interesting bit and probably the cause of my problem. =
When=20
> doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
>=20
> 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.
>=20
> I'm not exactly sure what this means but have read that open =
replication=20
> type transactions will prevent shrinking of the logfile. This looks =
like=20
> one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is=20
> neither a publisher nor a distributor nor a subscriber. I never =
touched=20
> replication since sql server got installed.
>=20
> Does anyone know how to fix this? Anyone have any idea how this could =
have=20
> happened? Any advice/hints would be greatly appreciated, maintaining=20
> backups at he moment is an administrative nightmare.
>=20
> BTW, the problem applies to all user DB's on that server, not just=20
> 'customerinformation'.
>=20
> Thanks
>=20
> Ray
>=20
>=20
>=20
>=20
> --
> 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=20
>=20
>=20
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
sql
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. =20
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. =20
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
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
|||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=20
> log reader has read all of the transactions from the log and marked =
them as=20
> replicated. You can truncate your log now.
>=20
> I can't explain why this database thinks it is being replicated. You =
might=20
> want to do this
>=20
> sp_replicationdboption 'databasename','published','false'
>=20
> and see what happens. It should drop any publications in this database =
or=20
> any related replication metadata if there is any.
>=20
> "Raymond Delevaux" <ray@.seekmedia.com> wrote in message=20
> news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
>=20
> 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=20
> was a fresh install of ms sql2k and the db's were created from backups =
taken=20
> from the old system.
>=20
> The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
>=20
> don't shrink the logfile anymore. I can still do "backup log with=20
> truncate_only", that will reduce the logfile to its original size, but =
that=20
> is just an emergency procedure to re-claim some HD space. I do need =
full=20
> recovery mode and have a sequence of full, differential, and log =
backups=20
> worked out that used to work just fine.
>=20
> I have read all the microsoft and other articles on how virtual =
logfiles=20
> work and how to successfully shrink logfiles, indeed I did this =
without=20
> problems on the old system.
>=20
> Here is the interesting bit and probably the cause of my problem. =
When=20
> doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
>=20
> 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.
>=20
> I'm not exactly sure what this means but have read that open =
replication=20
> type transactions will prevent shrinking of the logfile. This looks =
like=20
> one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is=20
> neither a publisher nor a distributor nor a subscriber. I never =
touched=20
> replication since sql server got installed.
>=20
> Does anyone know how to fix this? Anyone have any idea how this could =
have=20
> happened? Any advice/hints would be greatly appreciated, maintaining=20
> backups at he moment is an administrative nightmare.
>=20
> BTW, the problem applies to all user DB's on that server, not just=20
> 'customerinformation'.
>=20
> Thanks
>=20
> Ray
>=20
>=20
>=20
>=20
> --
> 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=20
>=20
>=20
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
sql
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. =20
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. =20
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
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
|||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=20
> log reader has read all of the transactions from the log and marked =
them as=20
> replicated. You can truncate your log now.
>=20
> I can't explain why this database thinks it is being replicated. You =
might=20
> want to do this
>=20
> sp_replicationdboption 'databasename','published','false'
>=20
> and see what happens. It should drop any publications in this database =
or=20
> any related replication metadata if there is any.
>=20
> "Raymond Delevaux" <ray@.seekmedia.com> wrote in message=20
> news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
>=20
> 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=20
> was a fresh install of ms sql2k and the db's were created from backups =
taken=20
> from the old system.
>=20
> The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
>=20
> don't shrink the logfile anymore. I can still do "backup log with=20
> truncate_only", that will reduce the logfile to its original size, but =
that=20
> is just an emergency procedure to re-claim some HD space. I do need =
full=20
> recovery mode and have a sequence of full, differential, and log =
backups=20
> worked out that used to work just fine.
>=20
> I have read all the microsoft and other articles on how virtual =
logfiles=20
> work and how to successfully shrink logfiles, indeed I did this =
without=20
> problems on the old system.
>=20
> Here is the interesting bit and probably the cause of my problem. =
When=20
> doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
>=20
> 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.
>=20
> I'm not exactly sure what this means but have read that open =
replication=20
> type transactions will prevent shrinking of the logfile. This looks =
like=20
> one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is=20
> neither a publisher nor a distributor nor a subscriber. I never =
touched=20
> replication since sql server got installed.
>=20
> Does anyone know how to fix this? Anyone have any idea how this could =
have=20
> happened? Any advice/hints would be greatly appreciated, maintaining=20
> backups at he moment is an administrative nightmare.
>=20
> BTW, the problem applies to all user DB's on that server, not just=20
> 'customerinformation'.
>=20
> Thanks
>=20
> Ray
>=20
>=20
>=20
>=20
> --
> 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=20
>=20
>=20
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
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. =20
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. =20
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
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
|||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=20
> log reader has read all of the transactions from the log and marked =
them as=20
> replicated. You can truncate your log now.
>=20
> I can't explain why this database thinks it is being replicated. You =
might=20
> want to do this
>=20
> sp_replicationdboption 'databasename','published','false'
>=20
> and see what happens. It should drop any publications in this database =
or=20
> any related replication metadata if there is any.
>=20
> "Raymond Delevaux" <ray@.seekmedia.com> wrote in message=20
> news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
>=20
> 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=20
> was a fresh install of ms sql2k and the db's were created from backups =
taken=20
> from the old system.
>=20
> The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
>=20
> don't shrink the logfile anymore. I can still do "backup log with=20
> truncate_only", that will reduce the logfile to its original size, but =
that=20
> is just an emergency procedure to re-claim some HD space. I do need =
full=20
> recovery mode and have a sequence of full, differential, and log =
backups=20
> worked out that used to work just fine.
>=20
> I have read all the microsoft and other articles on how virtual =
logfiles=20
> work and how to successfully shrink logfiles, indeed I did this =
without=20
> problems on the old system.
>=20
> Here is the interesting bit and probably the cause of my problem. =
When=20
> doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
>=20
> 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.
>=20
> I'm not exactly sure what this means but have read that open =
replication=20
> type transactions will prevent shrinking of the logfile. This looks =
like=20
> one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is=20
> neither a publisher nor a distributor nor a subscriber. I never =
touched=20
> replication since sql server got installed.
>=20
> Does anyone know how to fix this? Anyone have any idea how this could =
have=20
> happened? Any advice/hints would be greatly appreciated, maintaining=20
> backups at he moment is an administrative nightmare.
>=20
> BTW, the problem applies to all user DB's on that server, not just=20
> 'customerinformation'.
>=20
> Thanks
>=20
> Ray
>=20
>=20
>=20
>=20
> --
> 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=20
>=20
>=20
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
Wednesday, March 21, 2012
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
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
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. =20
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. =20
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=20
> log reader has read all of the transactions from the log and marked =
them as=20
> replicated. You can truncate your log now.
>=20
> I can't explain why this database thinks it is being replicated. You =
might=20
> want to do this
>=20
> sp_replicationdboption 'databasename','published','false'
>=20
> and see what happens. It should drop any publications in this database =
or=20
> any related replication metadata if there is any.
>=20
> "Raymond Delevaux" <ray@.seekmedia.com> wrote in message=20
> news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
>=20
> 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=20
> was a fresh install of ms sql2k and the db's were created from backups =
taken=20
> from the old system.
>=20
> The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
>=20
> don't shrink the logfile anymore. I can still do "backup log with=20
> truncate_only", that will reduce the logfile to its original size, but =
that=20
> is just an emergency procedure to re-claim some HD space. I do need =
full=20
> recovery mode and have a sequence of full, differential, and log =
backups=20
> worked out that used to work just fine.
>=20
> I have read all the microsoft and other articles on how virtual =
logfiles=20
> work and how to successfully shrink logfiles, indeed I did this =
without=20
> problems on the old system.
>=20
> Here is the interesting bit and probably the cause of my problem. =
When=20
> doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
>=20
> 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.
>=20
> I'm not exactly sure what this means but have read that open =
replication=20
> type transactions will prevent shrinking of the logfile. This looks =
like=20
> one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is=20
> neither a publisher nor a distributor nor a subscriber. I never =
touched=20
> replication since sql server got installed.
>=20
> Does anyone know how to fix this? Anyone have any idea how this could =
have=20
> happened? Any advice/hints would be greatly appreciated, maintaining=20
> backups at he moment is an administrative nightmare.
>=20
> BTW, the problem applies to all user DB's on that server, not just=20
> 'customerinformation'.
>=20
> Thanks
>=20
> Ray
>=20
>=20
>=20
>=20
> --
> 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=20
>=20
>=20
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
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. =20
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. =20
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=20
> log reader has read all of the transactions from the log and marked =
them as=20
> replicated. You can truncate your log now.
>=20
> I can't explain why this database thinks it is being replicated. You =
might=20
> want to do this
>=20
> sp_replicationdboption 'databasename','published','false'
>=20
> and see what happens. It should drop any publications in this database =
or=20
> any related replication metadata if there is any.
>=20
> "Raymond Delevaux" <ray@.seekmedia.com> wrote in message=20
> news:uuCWM3jtEHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
>=20
> 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=20
> was a fresh install of ms sql2k and the db's were created from backups =
taken=20
> from the old system.
>=20
> The usual steps :
> backup log to file
> dbcc shrinkfile( dbname, targetsize)
>=20
> don't shrink the logfile anymore. I can still do "backup log with=20
> truncate_only", that will reduce the logfile to its original size, but =
that=20
> is just an emergency procedure to re-claim some HD space. I do need =
full=20
> recovery mode and have a sequence of full, differential, and log =
backups=20
> worked out that used to work just fine.
>=20
> I have read all the microsoft and other articles on how virtual =
logfiles=20
> work and how to successfully shrink logfiles, indeed I did this =
without=20
> problems on the old system.
>=20
> Here is the interesting bit and probably the cause of my problem. =
When=20
> doing "DBCC OPENTRAN" on the db in question I get this output:
> Transaction information for database 'customerinformation'.
>=20
> 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.
>=20
> I'm not exactly sure what this means but have read that open =
replication=20
> type transactions will prevent shrinking of the logfile. This looks =
like=20
> one to me.
> BUT....
> The sql server I'm using doesn't have any replication enabled. It is=20
> neither a publisher nor a distributor nor a subscriber. I never =
touched=20
> replication since sql server got installed.
>=20
> Does anyone know how to fix this? Anyone have any idea how this could =
have=20
> happened? Any advice/hints would be greatly appreciated, maintaining=20
> backups at he moment is an administrative nightmare.
>=20
> BTW, the problem applies to all user DB's on that server, not just=20
> 'customerinformation'.
>=20
> Thanks
>=20
> Ray
>=20
>=20
>=20
>=20
> --
> 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=20
>=20
>=20
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
Log file Shrinking
how can we shrink the log file to the required size without affecting the live database ....
Thanks in advanceI hope it can help you:
--Truncating the transaction log
BACKUP LOG { database_name | @.database_name_var }
{
WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
Regards
Kris Zywczyk|||It is working.Thanks for this
Thanks in advanceI hope it can help you:
--Truncating the transaction log
BACKUP LOG { database_name | @.database_name_var }
{
WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
Regards
Kris Zywczyk|||It is working.Thanks for this
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>
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>
Monday, March 12, 2012
Log file auto shrink question
Hi, I am trying to automate shrinking the transaction log file on SQL server.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.
I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...ThanksThe error mentioned does not refer to transaction log, but rather to data device of your database. Usually it's caused by either BULK INSERT/BCP...IN or an INSERT/UPDATE where the amount of resulting data exceeds the growth capacity of the database. These operations also affect the transaction log but the error would be different if that was the case. There are multiple sources on the net with similar approach. You can check here (http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp) for a fancy SQL-DMO version of it. For a known technique to handle shrinking of transaction log files using T-SQL go to this (http://dbforums.com/t515230.html) post.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.
I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...ThanksThe error mentioned does not refer to transaction log, but rather to data device of your database. Usually it's caused by either BULK INSERT/BCP...IN or an INSERT/UPDATE where the amount of resulting data exceeds the growth capacity of the database. These operations also affect the transaction log but the error would be different if that was the case. There are multiple sources on the net with similar approach. You can check here (http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp) for a fancy SQL-DMO version of it. For a known technique to handle shrinking of transaction log files using T-SQL go to this (http://dbforums.com/t515230.html) post.
log file 30GB
I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||that mean can i shrinkfile(abc_log, 1M)''
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> that mean can i shrinkfile(abc_log, 1M)''
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
it[vbcol=seagreen]
> the
10MB[vbcol=seagreen]
data[vbcol=seagreen]
>|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||that mean can i shrinkfile(abc_log, 1M)''
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> that mean can i shrinkfile(abc_log, 1M)''
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
it[vbcol=seagreen]
> the
10MB[vbcol=seagreen]
data[vbcol=seagreen]
>|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>
log file 30GB
I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?
Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>
|||that mean can i shrinkfile(abc_log, 1M)??
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>
|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> that mean can i shrinkfile(abc_log, 1M)??
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
it[vbcol=seagreen]
> the
10MB[vbcol=seagreen]
data
>
|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>
|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?
Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>
|||that mean can i shrinkfile(abc_log, 1M)??
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>
|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> that mean can i shrinkfile(abc_log, 1M)??
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
it[vbcol=seagreen]
> the
10MB[vbcol=seagreen]
data
>
|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>
|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
>
log file 30GB
I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||that mean can i shrinkfile(abc_log, 1M)''
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> >
> > Before Shrinking the log file, what actions should be taken?
> >
> > do i need checkpoint to make sure all transaction are comminted to data
> > file?
> >
> >
>|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> that mean can i shrinkfile(abc_log, 1M)''
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > Understand that SQL Server will not allow you to shrink the log file if
it
> > still requires any data in there, so it's safe to shrink the file. It's
> the
> > truncate process you should be careful about. See
> > http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> > shrinking log files.
> >
> > --
> > Peter Yeoh
> > http://www.yohz.com
> > Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
> >
> >
> > "Inamori" <test@.test.com> wrote in message
> > news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to
10MB
> > >
> > > Before Shrinking the log file, what actions should be taken?
> > >
> > > do i need checkpoint to make sure all transaction are comminted to
data
> > > file?
> > >
> > >
> >
> >
>|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> >
> > Before Shrinking the log file, what actions should be taken?
> >
> > do i need checkpoint to make sure all transaction are comminted to data
> > file?
> >
> >
>
Before Shrinking the log file, what actions should be taken?
do i need checkpoint to make sure all transaction are comminted to data
file?Understand that SQL Server will not allow you to shrink the log file if it
still requires any data in there, so it's safe to shrink the file. It's the
truncate process you should be careful about. See
http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
shrinking log files.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||that mean can i shrinkfile(abc_log, 1M)''
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Understand that SQL Server will not allow you to shrink the log file if it
> still requires any data in there, so it's safe to shrink the file. It's
the
> truncate process you should be careful about. See
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> shrinking log files.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Inamori" <test@.test.com> wrote in message
> news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> >
> > Before Shrinking the log file, what actions should be taken?
> >
> > do i need checkpoint to make sure all transaction are comminted to data
> > file?
> >
> >
>|||Sure, if everything in that log file has been written to disk and there are
no active trxs at the end of the log file. SQL Server will ensure you do
not harm yourself performing a shrinkfile. But note Tibor's article: you
will probably fragment your log file badly if you constantly shrink it.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Inamori" <test@.test.com> wrote in message
news:ObuaSuzrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> that mean can i shrinkfile(abc_log, 1M)''
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ONfC#hzrEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > Understand that SQL Server will not allow you to shrink the log file if
it
> > still requires any data in there, so it's safe to shrink the file. It's
> the
> > truncate process you should be careful about. See
> > http://www.karaszi.com/sqlserver/info_dont_shrink.asp for some info on
> > shrinking log files.
> >
> > --
> > Peter Yeoh
> > http://www.yohz.com
> > Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
> >
> >
> > "Inamori" <test@.test.com> wrote in message
> > news:%23$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to
10MB
> > >
> > > Before Shrinking the log file, what actions should be taken?
> > >
> > > do i need checkpoint to make sure all transaction are comminted to
data
> > > file?
> > >
> > >
> >
> >
>|||Are you interested in backups at all? Did you consider backing up the
database and seeing what happens after that?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Inamori" <test@.test.com> wrote in message
news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> Before Shrinking the log file, what actions should be taken?
> do i need checkpoint to make sure all transaction are comminted to data
> file?
>|||yes
i understand as follows
1. shrink only shrink something are inactive
2. No harm to data file
3. testing in testing environment
4. I will backup data file and log file first and shrink afterwards
5. even i want to shrink to 1M, i udnerstand actually it cannot if the log
data is active.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePAtSZ0rEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Are you interested in backups at all? Did you consider backing up the
> database and seeing what happens after that?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Inamori" <test@.test.com> wrote in message
> news:#$jvZczrEHA.644@.tk2msftngp13.phx.gbl...
> > I would like to shrink the log file dbcc shrinkfile (abc_log,10) to 10MB
> >
> > Before Shrinking the log file, what actions should be taken?
> >
> > do i need checkpoint to make sure all transaction are comminted to data
> > file?
> >
> >
>
Wednesday, March 7, 2012
log backup not getting rid of 'free' space, why?
Hi everyone, I was hoping someone could help me.
I am having problems with my transaction log not shrinking after a log
backup has taken place. The log backups are usually of size 400 Mb at
7:00am and generate further 6 MB (approx.) log backups each half hour.
The log file is usually of size 300 MB after each log backup has
occured. For some reason, the log backup for 7:00am today was at 26.8
GB and the log file has not shrunk - remaining at 28.3 BG.
I have set up a database backup to take place at 2am and log backups
from 7am to 11pm every half hour:
-- Job 1: Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.BAK'' '
EXEC (@.str)
and
-- Job 2: Transactional backup scheduled every hour between 7AM and
11PM inclusive
DECLARE @.str varchar(200)
SET @.str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.TRN'' '
EXEC (@.str)
In addition, the Transaction log space within SQL identifies:
Total: 27669.55 MB
Used: 166.15 MB
Free: 27503.4 MB
I have also run the DBCC OPENTRAN command to identify any open
transactions that might be preventing the log file from shrinking:
DBCC OPENTRAN
Transaction information for database 'ZestLive'.
Replicated Transaction Information:
Oldest distributed LSN : (54590:259717:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Shouldn't a log backup shrink the log file down and get rid of all that
free space i.e. 27503.4 MB?
I havent changed anything interms of the backup schedule. Why is this
happening now?
Many thanks!
cheers
peter
Log backups do NOT shrink the file. They only allow the space to be reused
if the transactions are committed. You need to commit that old transaction
so you can free up the space in the log for more transactions. Then you can
use DBCC SHRINKFILE to shrink it later.
Andrew J. Kelly SQL MVP
"peter" <peter@.nospam.com> wrote in message
news:O57Buj6OGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, I was hoping someone could help me.
> I am having problems with my transaction log not shrinking after a log
> backup has taken place. The log backups are usually of size 400 Mb at
> 7:00am and generate further 6 MB (approx.) log backups each half hour.
> The log file is usually of size 300 MB after each log backup has
> occured. For some reason, the log backup for 7:00am today was at 26.8
> GB and the log file has not shrunk - remaining at 28.3 BG.
>
> I have set up a database backup to take place at 2am and log backups
> from 7am to 11pm every half hour:
>
> -- Job 1: Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> ----
>
> and
>
> -- Job 2: Transactional backup scheduled every hour between 7AM and
> 11PM inclusive
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> ----
>
> In addition, the Transaction log space within SQL identifies:
>
> --
> Total: 27669.55 MB
> Used: 166.15 MB
> Free: 27503.4 MB
> --
>
> I have also run the DBCC OPENTRAN command to identify any open
> transactions that might be preventing the log file from shrinking:
>
> --
> DBCC OPENTRAN
>
> Transaction information for database 'ZestLive'.
> Replicated Transaction Information:
> Oldest distributed LSN : (54590:259717:1)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --
>
> Shouldn't a log backup shrink the log file down and get rid of all that
> free space i.e. 27503.4 MB?
>
> I havent changed anything interms of the backup schedule. Why is this
> happening now?
>
> Many thanks!
>
> cheers
> peter
>
>
|||Andrew J. Kelly wrote:
> Log backups do NOT shrink the file. They only allow the space to be reused
> if the transactions are committed. You need to commit that old transaction
> so you can free up the space in the log for more transactions. Then you can
> use DBCC SHRINKFILE to shrink it later.
>
And in addition to Andrews response, you should find out why the logfile
suddenly has grown so much. If it's because of a maintenance job that
runs e.g. once a week, then you should leave the logfile as it is -
otherwise it will just grow again next week and that's waste of
ressources. If the grow is due to a "one time" operation (e.g. deletion
/update of a huge amount of data) then it might be ok to shrink the
logfile since it not very likely that this much space will be needed for
normal operation.
Regards
Steen
|||Poor application side coding is creating such issue... As you said monitor
oldest transaction using DBCC OPENTRAN, use DBCC INPUTBUFFER(OLDESTSPID)
to get the Query which was executed and by whom from which Host address. Try
to log that and rewrite the application code.
To shrink file,
Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
Run => DBCC sqlperf(logspace)
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Steen Persson (DK)" wrote:
> Andrew J. Kelly wrote:
> And in addition to Andrews response, you should find out why the logfile
> suddenly has grown so much. If it's because of a maintenance job that
> runs e.g. once a week, then you should leave the logfile as it is -
> otherwise it will just grow again next week and that's waste of
> ressources. If the grow is due to a "one time" operation (e.g. deletion
> /update of a huge amount of data) then it might be ok to shrink the
> logfile since it not very likely that this much space will be needed for
> normal operation.
> Regards
> Steen
>
I am having problems with my transaction log not shrinking after a log
backup has taken place. The log backups are usually of size 400 Mb at
7:00am and generate further 6 MB (approx.) log backups each half hour.
The log file is usually of size 300 MB after each log backup has
occured. For some reason, the log backup for 7:00am today was at 26.8
GB and the log file has not shrunk - remaining at 28.3 BG.
I have set up a database backup to take place at 2am and log backups
from 7am to 11pm every half hour:
-- Job 1: Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.BAK'' '
EXEC (@.str)
and
-- Job 2: Transactional backup scheduled every hour between 7AM and
11PM inclusive
DECLARE @.str varchar(200)
SET @.str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.TRN'' '
EXEC (@.str)
In addition, the Transaction log space within SQL identifies:
Total: 27669.55 MB
Used: 166.15 MB
Free: 27503.4 MB
I have also run the DBCC OPENTRAN command to identify any open
transactions that might be preventing the log file from shrinking:
DBCC OPENTRAN
Transaction information for database 'ZestLive'.
Replicated Transaction Information:
Oldest distributed LSN : (54590:259717:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Shouldn't a log backup shrink the log file down and get rid of all that
free space i.e. 27503.4 MB?
I havent changed anything interms of the backup schedule. Why is this
happening now?
Many thanks!
cheers
peter
Log backups do NOT shrink the file. They only allow the space to be reused
if the transactions are committed. You need to commit that old transaction
so you can free up the space in the log for more transactions. Then you can
use DBCC SHRINKFILE to shrink it later.
Andrew J. Kelly SQL MVP
"peter" <peter@.nospam.com> wrote in message
news:O57Buj6OGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, I was hoping someone could help me.
> I am having problems with my transaction log not shrinking after a log
> backup has taken place. The log backups are usually of size 400 Mb at
> 7:00am and generate further 6 MB (approx.) log backups each half hour.
> The log file is usually of size 300 MB after each log backup has
> occured. For some reason, the log backup for 7:00am today was at 26.8
> GB and the log file has not shrunk - remaining at 28.3 BG.
>
> I have set up a database backup to take place at 2am and log backups
> from 7am to 11pm every half hour:
>
> -- Job 1: Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> ----
>
> and
>
> -- Job 2: Transactional backup scheduled every hour between 7AM and
> 11PM inclusive
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> ----
>
> In addition, the Transaction log space within SQL identifies:
>
> --
> Total: 27669.55 MB
> Used: 166.15 MB
> Free: 27503.4 MB
> --
>
> I have also run the DBCC OPENTRAN command to identify any open
> transactions that might be preventing the log file from shrinking:
>
> --
> DBCC OPENTRAN
>
> Transaction information for database 'ZestLive'.
> Replicated Transaction Information:
> Oldest distributed LSN : (54590:259717:1)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --
>
> Shouldn't a log backup shrink the log file down and get rid of all that
> free space i.e. 27503.4 MB?
>
> I havent changed anything interms of the backup schedule. Why is this
> happening now?
>
> Many thanks!
>
> cheers
> peter
>
>
|||Andrew J. Kelly wrote:
> Log backups do NOT shrink the file. They only allow the space to be reused
> if the transactions are committed. You need to commit that old transaction
> so you can free up the space in the log for more transactions. Then you can
> use DBCC SHRINKFILE to shrink it later.
>
And in addition to Andrews response, you should find out why the logfile
suddenly has grown so much. If it's because of a maintenance job that
runs e.g. once a week, then you should leave the logfile as it is -
otherwise it will just grow again next week and that's waste of
ressources. If the grow is due to a "one time" operation (e.g. deletion
/update of a huge amount of data) then it might be ok to shrink the
logfile since it not very likely that this much space will be needed for
normal operation.
Regards
Steen
|||Poor application side coding is creating such issue... As you said monitor
oldest transaction using DBCC OPENTRAN, use DBCC INPUTBUFFER(OLDESTSPID)
to get the Query which was executed and by whom from which Host address. Try
to log that and rewrite the application code.
To shrink file,
Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
Run => DBCC sqlperf(logspace)
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Steen Persson (DK)" wrote:
> Andrew J. Kelly wrote:
> And in addition to Andrews response, you should find out why the logfile
> suddenly has grown so much. If it's because of a maintenance job that
> runs e.g. once a week, then you should leave the logfile as it is -
> otherwise it will just grow again next week and that's waste of
> ressources. If the grow is due to a "one time" operation (e.g. deletion
> /update of a huge amount of data) then it might be ok to shrink the
> logfile since it not very likely that this much space will be needed for
> normal operation.
> Regards
> Steen
>
log backup not getting rid of 'free' space, why?
Hi everyone, I was hoping someone could help me.
I am having problems with my transaction log not shrinking after a log
backup has taken place. The log backups are usually of size 400 Mb at
7:00am and generate further 6 MB (approx.) log backups each half hour.
The log file is usually of size 300 MB after each log backup has
occured. For some reason, the log backup for 7:00am today was at 26.8
GB and the log file has not shrunk - remaining at 28.3 BG.
I have set up a database backup to take place at 2am and log backups
from 7am to 11pm every half hour:
-- Job 1: Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.BAK'' '
EXEC (@.str)
----
and
-- Job 2: Transactional backup scheduled every hour between 7AM and
11PM inclusive
DECLARE @.str varchar(200)
SET @.str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.TRN'' '
EXEC (@.str)
----
In addition, the Transaction log space within SQL identifies:
Total: 27669.55 MB
Used: 166.15 MB
Free: 27503.4 MB
--
I have also run the DBCC OPENTRAN command to identify any open
transactions that might be preventing the log file from shrinking:
DBCC OPENTRAN
Transaction information for database 'ZestLive'.
Replicated Transaction Information:
Oldest distributed LSN : (54590:259717:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
Shouldn't a log backup shrink the log file down and get rid of all that
free space i.e. 27503.4 MB?
I havent changed anything interms of the backup schedule. Why is this
happening now?
Many thanks!
cheers
peterLog backups do NOT shrink the file. They only allow the space to be reused
if the transactions are committed. You need to commit that old transaction
so you can free up the space in the log for more transactions. Then you can
use DBCC SHRINKFILE to shrink it later.
Andrew J. Kelly SQL MVP
"peter" <peter@.nospam.com> wrote in message
news:O57Buj6OGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, I was hoping someone could help me.
> I am having problems with my transaction log not shrinking after a log
> backup has taken place. The log backups are usually of size 400 Mb at
> 7:00am and generate further 6 MB (approx.) log backups each half hour.
> The log file is usually of size 300 MB after each log backup has
> occured. For some reason, the log backup for 7:00am today was at 26.8
> GB and the log file has not shrunk - remaining at 28.3 BG.
>
> I have set up a database backup to take place at 2am and log backups
> from 7am to 11pm every half hour:
>
> -- Job 1: Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> ----
>
> and
>
> -- Job 2: Transactional backup scheduled every hour between 7AM and
> 11PM inclusive
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> ----
>
> In addition, the Transaction log space within SQL identifies:
>
> --
> Total: 27669.55 MB
> Used: 166.15 MB
> Free: 27503.4 MB
> --
>
> I have also run the DBCC OPENTRAN command to identify any open
> transactions that might be preventing the log file from shrinking:
>
> --
> DBCC OPENTRAN
>
> Transaction information for database 'ZestLive'.
> Replicated Transaction Information:
> Oldest distributed LSN : (54590:259717:1)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --
>
> Shouldn't a log backup shrink the log file down and get rid of all that
> free space i.e. 27503.4 MB?
>
> I havent changed anything interms of the backup schedule. Why is this
> happening now?
>
> Many thanks!
>
> cheers
> peter
>
>|||Andrew J. Kelly wrote:
> Log backups do NOT shrink the file. They only allow the space to be reuse
d
> if the transactions are committed. You need to commit that old transactio
n
> so you can free up the space in the log for more transactions. Then you ca
n
> use DBCC SHRINKFILE to shrink it later.
>
And in addition to Andrews response, you should find out why the logfile
suddenly has grown so much. If it's because of a maintenance job that
runs e.g. once a week, then you should leave the logfile as it is -
otherwise it will just grow again next week and that's waste of
ressources. If the grow is due to a "one time" operation (e.g. deletion
/update of a huge amount of data) then it might be ok to shrink the
logfile since it not very likely that this much space will be needed for
normal operation.
Regards
Steen|||Poor application side coding is creating such issue... As you said monitor
oldest transaction using DBCC OPENTRAN, use DBCC INPUTBUFFER(OLDESTSPID)
to get the Query which was executed and by whom from which Host address. Try
to log that and rewrite the application code.
To shrink file,
Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
Run => DBCC sqlperf(logspace)
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Steen Persson (DK)" wrote:
> Andrew J. Kelly wrote:
> And in addition to Andrews response, you should find out why the logfile
> suddenly has grown so much. If it's because of a maintenance job that
> runs e.g. once a week, then you should leave the logfile as it is -
> otherwise it will just grow again next week and that's waste of
> ressources. If the grow is due to a "one time" operation (e.g. deletion
> /update of a huge amount of data) then it might be ok to shrink the
> logfile since it not very likely that this much space will be needed for
> normal operation.
> Regards
> Steen
>
I am having problems with my transaction log not shrinking after a log
backup has taken place. The log backups are usually of size 400 Mb at
7:00am and generate further 6 MB (approx.) log backups each half hour.
The log file is usually of size 300 MB after each log backup has
occured. For some reason, the log backup for 7:00am today was at 26.8
GB and the log file has not shrunk - remaining at 28.3 BG.
I have set up a database backup to take place at 2am and log backups
from 7am to 11pm every half hour:
-- Job 1: Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.BAK'' '
EXEC (@.str)
----
and
-- Job 2: Transactional backup scheduled every hour between 7AM and
11PM inclusive
DECLARE @.str varchar(200)
SET @.str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.TRN'' '
EXEC (@.str)
----
In addition, the Transaction log space within SQL identifies:
Total: 27669.55 MB
Used: 166.15 MB
Free: 27503.4 MB
--
I have also run the DBCC OPENTRAN command to identify any open
transactions that might be preventing the log file from shrinking:
DBCC OPENTRAN
Transaction information for database 'ZestLive'.
Replicated Transaction Information:
Oldest distributed LSN : (54590:259717:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
Shouldn't a log backup shrink the log file down and get rid of all that
free space i.e. 27503.4 MB?
I havent changed anything interms of the backup schedule. Why is this
happening now?
Many thanks!
cheers
peterLog backups do NOT shrink the file. They only allow the space to be reused
if the transactions are committed. You need to commit that old transaction
so you can free up the space in the log for more transactions. Then you can
use DBCC SHRINKFILE to shrink it later.
Andrew J. Kelly SQL MVP
"peter" <peter@.nospam.com> wrote in message
news:O57Buj6OGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, I was hoping someone could help me.
> I am having problems with my transaction log not shrinking after a log
> backup has taken place. The log backups are usually of size 400 Mb at
> 7:00am and generate further 6 MB (approx.) log backups each half hour.
> The log file is usually of size 300 MB after each log backup has
> occured. For some reason, the log backup for 7:00am today was at 26.8
> GB and the log file has not shrunk - remaining at 28.3 BG.
>
> I have set up a database backup to take place at 2am and log backups
> from 7am to 11pm every half hour:
>
> -- Job 1: Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> ----
>
> and
>
> -- Job 2: Transactional backup scheduled every hour between 7AM and
> 11PM inclusive
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> ----
>
> In addition, the Transaction log space within SQL identifies:
>
> --
> Total: 27669.55 MB
> Used: 166.15 MB
> Free: 27503.4 MB
> --
>
> I have also run the DBCC OPENTRAN command to identify any open
> transactions that might be preventing the log file from shrinking:
>
> --
> DBCC OPENTRAN
>
> Transaction information for database 'ZestLive'.
> Replicated Transaction Information:
> Oldest distributed LSN : (54590:259717:1)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --
>
> Shouldn't a log backup shrink the log file down and get rid of all that
> free space i.e. 27503.4 MB?
>
> I havent changed anything interms of the backup schedule. Why is this
> happening now?
>
> Many thanks!
>
> cheers
> peter
>
>|||Andrew J. Kelly wrote:
> Log backups do NOT shrink the file. They only allow the space to be reuse
d
> if the transactions are committed. You need to commit that old transactio
n
> so you can free up the space in the log for more transactions. Then you ca
n
> use DBCC SHRINKFILE to shrink it later.
>
And in addition to Andrews response, you should find out why the logfile
suddenly has grown so much. If it's because of a maintenance job that
runs e.g. once a week, then you should leave the logfile as it is -
otherwise it will just grow again next week and that's waste of
ressources. If the grow is due to a "one time" operation (e.g. deletion
/update of a huge amount of data) then it might be ok to shrink the
logfile since it not very likely that this much space will be needed for
normal operation.
Regards
Steen|||Poor application side coding is creating such issue... As you said monitor
oldest transaction using DBCC OPENTRAN, use DBCC INPUTBUFFER(OLDESTSPID)
to get the Query which was executed and by whom from which Host address. Try
to log that and rewrite the application code.
To shrink file,
Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
Run => DBCC sqlperf(logspace)
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Steen Persson (DK)" wrote:
> Andrew J. Kelly wrote:
> And in addition to Andrews response, you should find out why the logfile
> suddenly has grown so much. If it's because of a maintenance job that
> runs e.g. once a week, then you should leave the logfile as it is -
> otherwise it will just grow again next week and that's waste of
> ressources. If the grow is due to a "one time" operation (e.g. deletion
> /update of a huge amount of data) then it might be ok to shrink the
> logfile since it not very likely that this much space will be needed for
> normal operation.
> Regards
> Steen
>
log backup not getting rid of 'free' space, why?
Hi everyone, I was hoping someone could help me.
I am having problems with my transaction log not shrinking after a log
backup has taken place. The log backups are usually of size 400 Mb at
7:00am and generate further 6 MB (approx.) log backups each half hour.
The log file is usually of size 300 MB after each log backup has
occured. For some reason, the log backup for 7:00am today was at 26.8
GB and the log file has not shrunk - remaining at 28.3 BG.
I have set up a database backup to take place at 2am and log backups
from 7am to 11pm every half hour:
-- Job 1: Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str = 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.BAK'' '
EXEC (@.str)
----
and
-- Job 2: Transactional backup scheduled every hour between 7AM and
11PM inclusive
DECLARE @.str varchar(200)
SET @.str = 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.TRN'' '
EXEC (@.str)
----
In addition, the Transaction log space within SQL identifies:
--
Total: 27669.55 MB
Used: 166.15 MB
Free: 27503.4 MB
--
I have also run the DBCC OPENTRAN command to identify any open
transactions that might be preventing the log file from shrinking:
--
DBCC OPENTRAN
Transaction information for database 'ZestLive'.
Replicated Transaction Information:
Oldest distributed LSN : (54590:259717:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
Shouldn't a log backup shrink the log file down and get rid of all that
free space i.e. 27503.4 MB?
I havent changed anything interms of the backup schedule. Why is this
happening now?
Many thanks!
cheers
peterLog backups do NOT shrink the file. They only allow the space to be reused
if the transactions are committed. You need to commit that old transaction
so you can free up the space in the log for more transactions. Then you can
use DBCC SHRINKFILE to shrink it later.
--
Andrew J. Kelly SQL MVP
"peter" <peter@.nospam.com> wrote in message
news:O57Buj6OGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, I was hoping someone could help me.
> I am having problems with my transaction log not shrinking after a log
> backup has taken place. The log backups are usually of size 400 Mb at
> 7:00am and generate further 6 MB (approx.) log backups each half hour.
> The log file is usually of size 300 MB after each log backup has
> occured. For some reason, the log backup for 7:00am today was at 26.8
> GB and the log file has not shrunk - remaining at 28.3 BG.
>
> I have set up a database backup to take place at 2am and log backups
> from 7am to 11pm every half hour:
>
> -- Job 1: Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> ----
>
> and
>
> -- Job 2: Transactional backup scheduled every hour between 7AM and
> 11PM inclusive
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> ----
>
> In addition, the Transaction log space within SQL identifies:
>
> --
> Total: 27669.55 MB
> Used: 166.15 MB
> Free: 27503.4 MB
> --
>
> I have also run the DBCC OPENTRAN command to identify any open
> transactions that might be preventing the log file from shrinking:
>
> --
> DBCC OPENTRAN
>
> Transaction information for database 'ZestLive'.
> Replicated Transaction Information:
> Oldest distributed LSN : (54590:259717:1)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --
>
> Shouldn't a log backup shrink the log file down and get rid of all that
> free space i.e. 27503.4 MB?
>
> I havent changed anything interms of the backup schedule. Why is this
> happening now?
>
> Many thanks!
>
> cheers
> peter
>
>|||Andrew J. Kelly wrote:
> Log backups do NOT shrink the file. They only allow the space to be reused
> if the transactions are committed. You need to commit that old transaction
> so you can free up the space in the log for more transactions. Then you can
> use DBCC SHRINKFILE to shrink it later.
>
And in addition to Andrews response, you should find out why the logfile
suddenly has grown so much. If it's because of a maintenance job that
runs e.g. once a week, then you should leave the logfile as it is -
otherwise it will just grow again next week and that's waste of
ressources. If the grow is due to a "one time" operation (e.g. deletion
/update of a huge amount of data) then it might be ok to shrink the
logfile since it not very likely that this much space will be needed for
normal operation.
Regards
Steen|||Poor application side coding is creating such issue... As you said monitor
oldest transaction using DBCC OPENTRAN, use DBCC INPUTBUFFER(OLDESTSPID)
to get the Query which was executed and by whom from which Host address. Try
to log that and rewrite the application code.
To shrink file,
Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
Run => DBCC sqlperf(logspace)
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Steen Persson (DK)" wrote:
> Andrew J. Kelly wrote:
> > Log backups do NOT shrink the file. They only allow the space to be reused
> > if the transactions are committed. You need to commit that old transaction
> > so you can free up the space in the log for more transactions. Then you can
> > use DBCC SHRINKFILE to shrink it later.
> >
> And in addition to Andrews response, you should find out why the logfile
> suddenly has grown so much. If it's because of a maintenance job that
> runs e.g. once a week, then you should leave the logfile as it is -
> otherwise it will just grow again next week and that's waste of
> ressources. If the grow is due to a "one time" operation (e.g. deletion
> /update of a huge amount of data) then it might be ok to shrink the
> logfile since it not very likely that this much space will be needed for
> normal operation.
> Regards
> Steen
>
I am having problems with my transaction log not shrinking after a log
backup has taken place. The log backups are usually of size 400 Mb at
7:00am and generate further 6 MB (approx.) log backups each half hour.
The log file is usually of size 300 MB after each log backup has
occured. For some reason, the log backup for 7:00am today was at 26.8
GB and the log file has not shrunk - remaining at 28.3 BG.
I have set up a database backup to take place at 2am and log backups
from 7am to 11pm every half hour:
-- Job 1: Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str = 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.BAK'' '
EXEC (@.str)
----
and
-- Job 2: Transactional backup scheduled every hour between 7AM and
11PM inclusive
DECLARE @.str varchar(200)
SET @.str = 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.TRN'' '
EXEC (@.str)
----
In addition, the Transaction log space within SQL identifies:
--
Total: 27669.55 MB
Used: 166.15 MB
Free: 27503.4 MB
--
I have also run the DBCC OPENTRAN command to identify any open
transactions that might be preventing the log file from shrinking:
--
DBCC OPENTRAN
Transaction information for database 'ZestLive'.
Replicated Transaction Information:
Oldest distributed LSN : (54590:259717:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
Shouldn't a log backup shrink the log file down and get rid of all that
free space i.e. 27503.4 MB?
I havent changed anything interms of the backup schedule. Why is this
happening now?
Many thanks!
cheers
peterLog backups do NOT shrink the file. They only allow the space to be reused
if the transactions are committed. You need to commit that old transaction
so you can free up the space in the log for more transactions. Then you can
use DBCC SHRINKFILE to shrink it later.
--
Andrew J. Kelly SQL MVP
"peter" <peter@.nospam.com> wrote in message
news:O57Buj6OGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, I was hoping someone could help me.
> I am having problems with my transaction log not shrinking after a log
> backup has taken place. The log backups are usually of size 400 Mb at
> 7:00am and generate further 6 MB (approx.) log backups each half hour.
> The log file is usually of size 300 MB after each log backup has
> occured. For some reason, the log backup for 7:00am today was at 26.8
> GB and the log file has not shrunk - remaining at 28.3 BG.
>
> I have set up a database backup to take place at 2am and log backups
> from 7am to 11pm every half hour:
>
> -- Job 1: Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> ----
>
> and
>
> -- Job 2: Transactional backup scheduled every hour between 7AM and
> 11PM inclusive
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + '_'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
> 1, 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> ----
>
> In addition, the Transaction log space within SQL identifies:
>
> --
> Total: 27669.55 MB
> Used: 166.15 MB
> Free: 27503.4 MB
> --
>
> I have also run the DBCC OPENTRAN command to identify any open
> transactions that might be preventing the log file from shrinking:
>
> --
> DBCC OPENTRAN
>
> Transaction information for database 'ZestLive'.
> Replicated Transaction Information:
> Oldest distributed LSN : (54590:259717:1)
> Oldest non-distributed LSN : (0:0:0)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> --
>
> Shouldn't a log backup shrink the log file down and get rid of all that
> free space i.e. 27503.4 MB?
>
> I havent changed anything interms of the backup schedule. Why is this
> happening now?
>
> Many thanks!
>
> cheers
> peter
>
>|||Andrew J. Kelly wrote:
> Log backups do NOT shrink the file. They only allow the space to be reused
> if the transactions are committed. You need to commit that old transaction
> so you can free up the space in the log for more transactions. Then you can
> use DBCC SHRINKFILE to shrink it later.
>
And in addition to Andrews response, you should find out why the logfile
suddenly has grown so much. If it's because of a maintenance job that
runs e.g. once a week, then you should leave the logfile as it is -
otherwise it will just grow again next week and that's waste of
ressources. If the grow is due to a "one time" operation (e.g. deletion
/update of a huge amount of data) then it might be ok to shrink the
logfile since it not very likely that this much space will be needed for
normal operation.
Regards
Steen|||Poor application side coding is creating such issue... As you said monitor
oldest transaction using DBCC OPENTRAN, use DBCC INPUTBUFFER(OLDESTSPID)
to get the Query which was executed and by whom from which Host address. Try
to log that and rewrite the application code.
To shrink file,
Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
Run => DBCC sqlperf(logspace)
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Steen Persson (DK)" wrote:
> Andrew J. Kelly wrote:
> > Log backups do NOT shrink the file. They only allow the space to be reused
> > if the transactions are committed. You need to commit that old transaction
> > so you can free up the space in the log for more transactions. Then you can
> > use DBCC SHRINKFILE to shrink it later.
> >
> And in addition to Andrews response, you should find out why the logfile
> suddenly has grown so much. If it's because of a maintenance job that
> runs e.g. once a week, then you should leave the logfile as it is -
> otherwise it will just grow again next week and that's waste of
> ressources. If the grow is due to a "one time" operation (e.g. deletion
> /update of a huge amount of data) then it might be ok to shrink the
> logfile since it not very likely that this much space will be needed for
> normal operation.
> Regards
> Steen
>
Subscribe to:
Posts (Atom)