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.

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

Log file is not truncated inspite of regular log backup

I am taking regular log backup (every 30 mins). But still
the log file size of one database is 1110MB even when no
users are connected.
While using dbcc shrinkfile, i am getting the following
msg:
Cannot shrink log file 2 (Gsap_Log) because all logical
log files are in use.
DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
Execution of the SP sp_repldone gives "The database is
not published."
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Can anyone help me in truncating my log file?
Regards,
MangaiCheck out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mangai" <mangaiyarkarasi_@.hotmail.com> wrote in message
news:007001c3af1e$a1c81570$a001280a@.phx.gbl...
> I am taking regular log backup (every 30 mins). But still
> the log file size of one database is 1110MB even when no
> users are connected.
> While using dbcc shrinkfile, i am getting the following
> msg:
> Cannot shrink log file 2 (Gsap_Log) because all logical
> log files are in use.
> DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> Execution of the SP sp_repldone gives "The database is
> not published."
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
>
> Can anyone help me in truncating my log file?
> Regards,
> Mangai
>|||Got the solution.
Just created a publication on the server for the database
and executed sp_repldone with null values. The log file
was truncated and I had shrinked the log file to 10 MB.
>--Original Message--
>I am taking regular log backup (every 30 mins). But
still
>the log file size of one database is 1110MB even when no
>users are connected.
>While using dbcc shrinkfile, i am getting the following
>msg:
>Cannot shrink log file 2 (Gsap_Log) because all logical
>log files are in use.
>DBCC OPENTRAN gives:
>Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
>Execution of the SP sp_repldone gives "The database is
>not published."
>EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
>@.numtrans = 0, @.time = 0, @.reset = 1
>
>Can anyone help me in truncating my log file?
>Regards,
>Mangai
>.
>

Log backup chain

Hi all, BOL states
Although the transaction log may be truncated manually, it is strongly
recommended that you do not do this, as it breaks the log backup chain. Until
a full database backup is created, the database is not protected from media
failure. Use manual log truncation only in very special circumstances, and
create a full database backup as soon as practical.
Case:
A daily full backup of a db occurs at 7 p.m. and tran log backup every
15min, and a job shrinks the log file (DBCC SHRINKFILE) daily at 6 a.m. What
should happen if I try to restore the database to a point of failure after 6
a.m. (say 11.45 a.m.)? Can I still continue restoring my tran log backups up
11:45 or everthing from 6 a.m onwards is lost?
I thought that truncating the log file manually would break the log backup
chain because inactive part of the log will be released. However, when I
tried to do so all seemed to be fine (no error messages). Surely, I
misunderstood something. Can anyone explain what "it breaks the log backup
chain" actually means?Hi,
If u studied the BOL then u should come to know that as u will do the
back up of the database transaction log automatically truncates and
flushes out the unused space.
When we execute any DML command sql writes it in transaction log and
issues a checkpoint and when we do backup the sql server clears all old
transaction records except the active portion of the transaction log.
So it will not harm u .u can do the point in time recovery.
U are doing transaction log backup every 15 min so suppose u had backup
at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
PM and finishes in few seconds.
So u have the backup till 7 PM and when u will run the truncate command
it will clear the logfile before 7 pm and u have the tlog backup after
7 pm.
So, now u have tlog back every 15 mins.In case u have a crash then u
have to apply the tlog after the restore of last backup back of the
database and then restore all the tlog after 7 PM.
Aslo study virtual logfile and truncate logfile from BOL
If u use the command with Tuncate_only it will shrink the logfile on
disk
No it will not break the chain because as u will do the backup of
database database issue a checkpoint and stores a entry in it self so
when u apply the tlog it will take the LSN after the backup.
Hope it helps u.
from
Killer.|||Thanks doller for your time. But what if dbcc shrinkfile was executed between
tlob backups. e.g
Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
I understand what after tran log backup, inactive part of the log gets
removed and we don't need to run the shrinkfile command. But I am looking at
the server that has this configs (?) . I have tried to restore the data,
wanting to see if it will cause any problems and it did not.
"doller" wrote:
> Hi,
> If u studied the BOL then u should come to know that as u will do the
> back up of the database transaction log automatically truncates and
> flushes out the unused space.
> When we execute any DML command sql writes it in transaction log and
> issues a checkpoint and when we do backup the sql server clears all old
> transaction records except the active portion of the transaction log.
> So it will not harm u .u can do the point in time recovery.
> U are doing transaction log backup every 15 min so suppose u had backup
> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
> PM and finishes in few seconds.
> So u have the backup till 7 PM and when u will run the truncate command
> it will clear the logfile before 7 pm and u have the tlog backup after
> 7 pm.
> So, now u have tlog back every 15 mins.In case u have a crash then u
> have to apply the tlog after the restore of last backup back of the
> database and then restore all the tlog after 7 PM.
> Aslo study virtual logfile and truncate logfile from BOL
> If u use the command with Tuncate_only it will shrink the logfile on
> disk
> No it will not break the chain because as u will do the backup of
> database database issue a checkpoint and stores a entry in it self so
> when u apply the tlog it will take the LSN after the backup.
>
> Hope it helps u.
> from
> Killer.
>|||Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit on
the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
> Thanks doller for your time. But what if dbcc shrinkfile was executed between
> tlob backups. e.g
> Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
> SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
> I understand what after tran log backup, inactive part of the log gets
> removed and we don't need to run the shrinkfile command. But I am looking at
> the server that has this configs (?) . I have tried to restore the data,
> wanting to see if it will cause any problems and it did not.
>
> "doller" wrote:
>> Hi,
>> If u studied the BOL then u should come to know that as u will do the
>> back up of the database transaction log automatically truncates and
>> flushes out the unused space.
>> When we execute any DML command sql writes it in transaction log and
>> issues a checkpoint and when we do backup the sql server clears all old
>> transaction records except the active portion of the transaction log.
>> So it will not harm u .u can do the point in time recovery.
>> U are doing transaction log backup every 15 min so suppose u had backup
>> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
>> PM and finishes in few seconds.
>> So u have the backup till 7 PM and when u will run the truncate command
>> it will clear the logfile before 7 pm and u have the tlog backup after
>> 7 pm.
>> So, now u have tlog back every 15 mins.In case u have a crash then u
>> have to apply the tlog after the restore of last backup back of the
>> database and then restore all the tlog after 7 PM.
>> Aslo study virtual logfile and truncate logfile from BOL
>> If u use the command with Tuncate_only it will shrink the logfile on
>> disk
>> No it will not break the chain because as u will do the backup of
>> database database issue a checkpoint and stores a entry in it self so
>> when u apply the tlog it will take the LSN after the backup.
>>
>> Hope it helps u.
>> from
>> Killer.
>>|||Thanx Tibor, I have visited your site many times before but did see this
article. I follow now. That's why somitimes when you do
"dbcc shrinkfile" and the size of the tlog is not reduced we need to run
"backup log xxx with truncate_only" and than dbcc shrinkfile.
Thanx again.
"Tibor Karaszi" wrote:
> Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
> the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit on
> the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
> news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
> > Thanks doller for your time. But what if dbcc shrinkfile was executed between
> > tlob backups. e.g
> >
> > Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
> > SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
> >
> > I understand what after tran log backup, inactive part of the log gets
> > removed and we don't need to run the shrinkfile command. But I am looking at
> > the server that has this configs (?) . I have tried to restore the data,
> > wanting to see if it will cause any problems and it did not.
> >
> >
> > "doller" wrote:
> >
> >> Hi,
> >> If u studied the BOL then u should come to know that as u will do the
> >> back up of the database transaction log automatically truncates and
> >> flushes out the unused space.
> >> When we execute any DML command sql writes it in transaction log and
> >> issues a checkpoint and when we do backup the sql server clears all old
> >> transaction records except the active portion of the transaction log.
> >> So it will not harm u .u can do the point in time recovery.
> >> U are doing transaction log backup every 15 min so suppose u had backup
> >> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
> >> PM and finishes in few seconds.
> >>
> >> So u have the backup till 7 PM and when u will run the truncate command
> >> it will clear the logfile before 7 pm and u have the tlog backup after
> >> 7 pm.
> >> So, now u have tlog back every 15 mins.In case u have a crash then u
> >> have to apply the tlog after the restore of last backup back of the
> >> database and then restore all the tlog after 7 PM.
> >>
> >> Aslo study virtual logfile and truncate logfile from BOL
> >>
> >> If u use the command with Tuncate_only it will shrink the logfile on
> >> disk
> >> No it will not break the chain because as u will do the backup of
> >> database database issue a checkpoint and stores a entry in it self so
> >> when u apply the tlog it will take the LSN after the backup.
> >>
> >>
> >>
> >> Hope it helps u.
> >> from
> >> Killer.
> >>
> >>
>|||> That's why somitimes when you do
> "dbcc shrinkfile" and the size of the tlog is not reduced we need to run
> "backup log xxx with truncate_only" and than dbcc shrinkfile.
Exactly. It's all in understanding how the virtual log files work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:362F54BB-0F65-404D-BE62-F35BCCB9249A@.microsoft.com...
> Thanx Tibor, I have visited your site many times before but did see this
> article. I follow now. That's why somitimes when you do
> "dbcc shrinkfile" and the size of the tlog is not reduced we need to run
> "backup log xxx with truncate_only" and than dbcc shrinkfile.
> Thanx again.
> "Tibor Karaszi" wrote:
>> Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any
>> of
>> the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit
>> on
>> the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
>> news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
>> > Thanks doller for your time. But what if dbcc shrinkfile was executed between
>> > tlob backups. e.g
>> >
>> > Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
>> > SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
>> >
>> > I understand what after tran log backup, inactive part of the log gets
>> > removed and we don't need to run the shrinkfile command. But I am looking at
>> > the server that has this configs (?) . I have tried to restore the data,
>> > wanting to see if it will cause any problems and it did not.
>> >
>> >
>> > "doller" wrote:
>> >
>> >> Hi,
>> >> If u studied the BOL then u should come to know that as u will do the
>> >> back up of the database transaction log automatically truncates and
>> >> flushes out the unused space.
>> >> When we execute any DML command sql writes it in transaction log and
>> >> issues a checkpoint and when we do backup the sql server clears all old
>> >> transaction records except the active portion of the transaction log.
>> >> So it will not harm u .u can do the point in time recovery.
>> >> U are doing transaction log backup every 15 min so suppose u had backup
>> >> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
>> >> PM and finishes in few seconds.
>> >>
>> >> So u have the backup till 7 PM and when u will run the truncate command
>> >> it will clear the logfile before 7 pm and u have the tlog backup after
>> >> 7 pm.
>> >> So, now u have tlog back every 15 mins.In case u have a crash then u
>> >> have to apply the tlog after the restore of last backup back of the
>> >> database and then restore all the tlog after 7 PM.
>> >>
>> >> Aslo study virtual logfile and truncate logfile from BOL
>> >>
>> >> If u use the command with Tuncate_only it will shrink the logfile on
>> >> disk
>> >> No it will not break the chain because as u will do the backup of
>> >> database database issue a checkpoint and stores a entry in it self so
>> >> when u apply the tlog it will take the LSN after the backup.
>> >>
>> >>
>> >>
>> >> Hope it helps u.
>> >> from
>> >> Killer.
>> >>
>> >>
>>