Log full

Hi! Please help with the following:
Our disk space is limited. I set the database recovery model to
"FULL", and backup transaction log every hour between business hours.
But the disk is always full because the transactions log growth. Do
you think increase the frequency of transaction log backup or backup
transaction 24 hours instead of only business hours will solve the
problem?
Thanks!!"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0310270725.45bf4877@.posting.google.c om...
> Hi! Please help with the following:
> Our disk space is limited. I set the database recovery model to
> "FULL", and backup transaction log every hour between business hours.
> But the disk is always full because the transactions log growth. Do
> you think increase the frequency of transaction log backup or backup
> transaction 24 hours instead of only business hours will solve the
> problem?

I think you're approaching this from the wrong direction. Your transaction
log should be backed up as often as your business rules dictate and the
diskspace sized appropriately.

You say "between business hours" I assume you mean during? (that logically
makes more sense, but what you wrote could be read as from 5:00 PM to 9:00
AM instead when presumably your log isn't really growing.)

I'd say your best bet is to purchase more disk space.

You also mention the disk is always full. Generally your database files are
far larger than your transaction log files, especially if you are doing
transaction backups on a regular basis. In addition a transaction log back
up does NOT shrink the physical disk file, only removes unused portions. So
it might be that your transaction log is far larger than it should be (say
one day you skipped transaction log backups and it swelled to 8 times its
normal size, now it's normally 8x bigger than it needs to be.)

Hope that helps.

> Thanks!!|||"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0310270725.45bf4877@.posting.google.c om...
> Hi! Please help with the following:
> Our disk space is limited. I set the database recovery model to
> "FULL", and backup transaction log every hour between business hours.
> But the disk is always full because the transactions log growth. Do
> you think increase the frequency of transaction log backup or backup
> transaction 24 hours instead of only business hours will solve the
> problem?

I think you're approaching this from the wrong direction. Your transaction
log should be backed up as often as your business rules dictate and the
diskspace sized appropriately.

You say "between business hours" I assume you mean during? (that logically
makes more sense, but what you wrote could be read as from 5:00 PM to 9:00
AM instead when presumably your log isn't really growing.)

I'd say your best bet is to purchase more disk space.

You also mention the disk is always full. Generally your database files are
far larger than your transaction log files, especially if you are doing
transaction backups on a regular basis. In addition a transaction log back
up does NOT shrink the physical disk file, only removes unused portions. So
it might be that your transaction log is far larger than it should be (say
one day you skipped transaction log backups and it swelled to 8 times its
normal size, now it's normally 8x bigger than it needs to be.)

Hope that helps.

> Thanks!!

Log file not freeing space (FULL recovery model) - even after transaction log backups

Hey all,
I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
David
Hi David
Check to see if there are open transactions with DBCC OPENTRAN
John
"David Conte" wrote:

> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>
|||How have you determined that there is 'no free space in it'? Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegr oups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>
|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> How have you determined that there is 'no free space in it'? Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even if
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegr oups.com...
>
>
>
|||Hi
What does the DBCC command give after you backup the log?
Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
should not shrink it to a size where under normal operations it will expand.
John
"David Conte" wrote:

> Thanks for the info John and Kevin.
> I didn't think that there could be an open transaction.. so tried DBCC
> OPENTRAN.
> Running DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Sounds like this has to do with replication? (excuse my ignorance
> here!)
> We don't have replication setup.
> Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
> log file via Management Studio it gives the free space percentage
> there before attempting the shrink. Just to confirm, I ran the SQL and
> it gives:
> 41297.24 99.79205% full
> Thanks
> David
>
> On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
|||Log still shows minimal free space even after transaction logs.
Hence, running DBCC SHRINKFILE does nothing as there is no free space
to be recovered.
This whole thing smells like a bug.
If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
is "REPLICATION".
Again, this database (or any other databases on the server) has never
had replication setup!
I tried running exec sp_removedbreplication - it completes, but the
log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
gives:
Transaction information for database 'xxxxx'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I think we are going to resort to setting up replication on it, run
sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
David
On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> What does the DBCC command give after you backup the log?
> Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> should not shrink it to a size where under normal operations it will expand.
> John
|||Hi David
I am not sure how that has occurred, but you could try rebuilding the log
file.
John
"David Conte" wrote:

> Log still shows minimal free space even after transaction logs.
> Hence, running DBCC SHRINKFILE does nothing as there is no free space
> to be recovered.
> This whole thing smells like a bug.
> If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
> is "REPLICATION".
> Again, this database (or any other databases on the server) has never
> had replication setup!
> I tried running exec sp_removedbreplication - it completes, but the
> log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
> gives:
> Transaction information for database 'xxxxx'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I think we are going to resort to setting up replication on it, run
> sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
> David
> On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
|||Ok well I found a forum post here on an identical issue.
Sounds like it is indeed an SQL Server bug.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
The workaround (which I've managed to do on a test restore of the live
database which experiences the same issue) is to setup replication on
the database, then remove the publication. After this there was now
99% free space in the logfile and I was able to shrink it from 44 GB
to 1 GB.
DBCC OPENTRAN now says there are no active transactions, and the
log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
So looking good again.
Now to do the process on the live database
Thanks everyone for their time!
Dave
|||Hi David
According to the post the bug may have been fixed in SP2, if you are not
running this version you may want to upgrade to SP2 plus hotfixes.
John
"David Conte" wrote:

> Ok well I found a forum post here on an identical issue.
> Sounds like it is indeed an SQL Server bug.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
> The workaround (which I've managed to do on a test restore of the live
> database which experiences the same issue) is to setup replication on
> the database, then remove the publication. After this there was now
> 99% free space in the logfile and I was able to shrink it from 44 GB
> to 1 GB.
> DBCC OPENTRAN now says there are no active transactions, and the
> log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
> So looking good again.
> Now to do the process on the live database
> Thanks everyone for their time!
> Dave
>

Log file not freeing space (FULL recovery model) - even after transaction log backups

Hey all,
I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
DavidHi David
Check to see if there are open transactions with DBCC OPENTRAN
John
"David Conte" wrote:
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>|||How have you determined that there is 'no free space in it'' Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> How have you determined that there is 'no free space in it'' Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even if
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> > Hey all,
> > I have a site that has their log file at 40 gig (running SQL Server
> > 2005).
> > The database is set to FULL recovery model, and transaction log
> > backups are performed every 3 hours.
> > The log file is HUGE compared to usual and I need to shrink it down.
> > However, it has no free space in it which is very strange considering
> > we are backing up the logs every 3 hours.
> > I have full backups occuring nightly.
> > I also tried switching to SIMPLE recovery model and shrinking the log
> > file.
> > Obviously this doesn't work as there isn't any free space in the log.
> > The site did have some db corruption issues a few weeks ago due to a
> > SAN issue.
> > The hardware has been repaired as well as the corruptions (they were
> > index corruptions so we rebuilt the indexes).
> > Could this have caused the transaction log to have issues to and not
> > free comitted transactions?
> > We have many other sites who have small transaction logs and I am at a
> > loss as to why it is so large.
> > The database is also 40 gig (and thus backups are 80+ gig in size!)
> > Anything I can try?
> > I am concerned about trashing the log by detaching / reattaching the
> > db without the log file as it seems brutal, but is that the only
> > choice?
> > Thanks,
> > David|||Hi
What does the DBCC command give after you backup the log?
Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
should not shrink it to a size where under normal operations it will expand.
John
"David Conte" wrote:
> Thanks for the info John and Kevin.
> I didn't think that there could be an open transaction.. so tried DBCC
> OPENTRAN.
> Running DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Sounds like this has to do with replication? (excuse my ignorance
> here!)
> We don't have replication setup.
> Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
> log file via Management Studio it gives the free space percentage
> there before attempting the shrink. Just to confirm, I ran the SQL and
> it gives:
> 41297.24 99.79205% full
> Thanks
> David
>
> On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> > How have you determined that there is 'no free space in it'' Did you
> > execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> > reduce in physical size? There is some very complicated stuff about the
> > internals of the log file that can prevent it from shrinking (much) even if
> > it has virtually no information in it. Search the web for sql server log
> > file shrink and you will find a number of helpful scripts to get over this
> > situation.
> >
> > --
> > Kevin G. Boles
> > TheSQLGuru
> > Indicium Resources, Inc.
> >
> > "David Conte" <davco...@.gmail.com> wrote in message
> >
> > news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> >
> > > Hey all,
> >
> > > I have a site that has their log file at 40 gig (running SQL Server
> > > 2005).
> > > The database is set to FULL recovery model, and transaction log
> > > backups are performed every 3 hours.
> >
> > > The log file is HUGE compared to usual and I need to shrink it down.
> > > However, it has no free space in it which is very strange considering
> > > we are backing up the logs every 3 hours.
> > > I have full backups occuring nightly.
> > > I also tried switching to SIMPLE recovery model and shrinking the log
> > > file.
> > > Obviously this doesn't work as there isn't any free space in the log.
> >
> > > The site did have some db corruption issues a few weeks ago due to a
> > > SAN issue.
> > > The hardware has been repaired as well as the corruptions (they were
> > > index corruptions so we rebuilt the indexes).
> > > Could this have caused the transaction log to have issues to and not
> > > free comitted transactions?
> >
> > > We have many other sites who have small transaction logs and I am at a
> > > loss as to why it is so large.
> > > The database is also 40 gig (and thus backups are 80+ gig in size!)
> >
> > > Anything I can try?
> > > I am concerned about trashing the log by detaching / reattaching the
> > > db without the log file as it seems brutal, but is that the only
> > > choice?
> >
> > > Thanks,
> > > David
>
>|||Log still shows minimal free space even after transaction logs.
Hence, running DBCC SHRINKFILE does nothing as there is no free space
to be recovered.
This whole thing smells like a bug.
If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
is "REPLICATION".
Again, this database (or any other databases on the server) has never
had replication setup!
I tried running exec sp_removedbreplication - it completes, but the
log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
gives:
Transaction information for database 'xxxxx'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I think we are going to resort to setting up replication on it, run
sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
David
On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> What does the DBCC command give after you backup the log?
> Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> should not shrink it to a size where under normal operations it will expand.
> John|||Hi David
I am not sure how that has occurred, but you could try rebuilding the log
file.
John
"David Conte" wrote:
> Log still shows minimal free space even after transaction logs.
> Hence, running DBCC SHRINKFILE does nothing as there is no free space
> to be recovered.
> This whole thing smells like a bug.
> If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
> is "REPLICATION".
> Again, this database (or any other databases on the server) has never
> had replication setup!
> I tried running exec sp_removedbreplication - it completes, but the
> log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
> gives:
> Transaction information for database 'xxxxx'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I think we are going to resort to setting up replication on it, run
> sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
> David
> On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > What does the DBCC command give after you backup the log?
> >
> > Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> > should not shrink it to a size where under normal operations it will expand.
> >
> > John
>|||Ok well I found a forum post here on an identical issue.
Sounds like it is indeed an SQL Server bug.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
The workaround (which I've managed to do on a test restore of the live
database which experiences the same issue) is to setup replication on
the database, then remove the publication. After this there was now
99% free space in the logfile and I was able to shrink it from 44 GB
to 1 GB.
DBCC OPENTRAN now says there are no active transactions, and the
log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
So looking good again.
Now to do the process on the live database :)
Thanks everyone for their time!
Dave|||Hi David
According to the post the bug may have been fixed in SP2, if you are not
running this version you may want to upgrade to SP2 plus hotfixes.
John
"David Conte" wrote:
> Ok well I found a forum post here on an identical issue.
> Sounds like it is indeed an SQL Server bug.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
> The workaround (which I've managed to do on a test restore of the live
> database which experiences the same issue) is to setup replication on
> the database, then remove the publication. After this there was now
> 99% free space in the logfile and I was able to shrink it from 44 GB
> to 1 GB.
> DBCC OPENTRAN now says there are no active transactions, and the
> log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
> So looking good again.
> Now to do the process on the live database :)
> Thanks everyone for their time!
> Dave
>

Log file not freeing space (FULL recovery model) - even after transaction log backups

Hey all,
I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
DavidHow have you determined that there is 'no free space in it'' Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> How have you determined that there is 'no free space in it'' Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even i
f
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
>
>
>
>
>
>
>

Log File Management

I am working with a client on a production OLTP database using Full
Recovery model. I normally work in datawarehouse environments with
Simple Recovery turned on. They are performing nightly Full Backups and
hourly log backups Monday through Friday. Sunday night is a normal full
backup followed by a log backup and shrinking the log file. Everyweek
the log grows from 200MB on Monday Morning to 12GB on Friday. I have
already recommended not shrinking the log files on Sunday nights as it
only introduces overhead each day during peak hours while the database
expands the log files it previously shrank. My question is why does
this file continue to grow throughout the week? I would expect that
after each log file backup, the log segments that have been backed up
should become available to be reused. I don't see that they are issuing
a checkpoint, and I'm wondering if that is the issue. Can anyone shed
light on this. The information I have found in BOL and online would
indicate that my understanding is correct and the log files should be
being reused after the backup. I just want to be sure before I go
searching for issues with open transactions, etc... Thanks for any and
all help...
Kevinkevin.karlin@.gmail.com wrote:
> I am working with a client on a production OLTP database using Full
> Recovery model. I normally work in datawarehouse environments with
> Simple Recovery turned on. They are performing nightly Full Backups and
> hourly log backups Monday through Friday. Sunday night is a normal full
> backup followed by a log backup and shrinking the log file. Everyweek
> the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> already recommended not shrinking the log files on Sunday nights as it
> only introduces overhead each day during peak hours while the database
> expands the log files it previously shrank. My question is why does
> this file continue to grow throughout the week? I would expect that
> after each log file backup, the log segments that have been backed up
> should become available to be reused. I don't see that they are issuing
> a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> light on this. The information I have found in BOL and online would
> indicate that my understanding is correct and the log files should be
> being reused after the backup. I just want to be sure before I go
> searching for issues with open transactions, etc... Thanks for any and
> all help...
> Kevin
>
Are they doing index rebuilds or some sort of importing during the week?
The log backups will flush out any committed transactions. Reindexing
(or index defragging) generates a LOT of transactional activity.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> > I am working with a client on a production OLTP database using Full
> > Recovery model. I normally work in datawarehouse environments with
> > Simple Recovery turned on. They are performing nightly Full Backups and
> > hourly log backups Monday through Friday. Sunday night is a normal full
> > backup followed by a log backup and shrinking the log file. Everyweek
> > the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> > already recommended not shrinking the log files on Sunday nights as it
> > only introduces overhead each day during peak hours while the database
> > expands the log files it previously shrank. My question is why does
> > this file continue to grow throughout the week? I would expect that
> > after each log file backup, the log segments that have been backed up
> > should become available to be reused. I don't see that they are issuing
> > a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> > light on this. The information I have found in BOL and online would
> > indicate that my understanding is correct and the log files should be
> > being reused after the backup. I just want to be sure before I go
> > searching for issues with open transactions, etc... Thanks for any and
> > all help...
> >
> > Kevin
> >
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> > I am working with a client on a production OLTP database using Full
> > Recovery model. I normally work in datawarehouse environments with
> > Simple Recovery turned on. They are performing nightly Full Backups and
> > hourly log backups Monday through Friday. Sunday night is a normal full
> > backup followed by a log backup and shrinking the log file. Everyweek
> > the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> > already recommended not shrinking the log files on Sunday nights as it
> > only introduces overhead each day during peak hours while the database
> > expands the log files it previously shrank. My question is why does
> > this file continue to grow throughout the week? I would expect that
> > after each log file backup, the log segments that have been backed up
> > should become available to be reused. I don't see that they are issuing
> > a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> > light on this. The information I have found in BOL and online would
> > indicate that my understanding is correct and the log files should be
> > being reused after the backup. I just want to be sure before I go
> > searching for issues with open transactions, etc... Thanks for any and
> > all help...
> >
> > Kevin
> >
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> > I am working with a client on a production OLTP database using Full
> > Recovery model. I normally work in datawarehouse environments with
> > Simple Recovery turned on. They are performing nightly Full Backups and
> > hourly log backups Monday through Friday. Sunday night is a normal full
> > backup followed by a log backup and shrinking the log file. Everyweek
> > the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> > already recommended not shrinking the log files on Sunday nights as it
> > only introduces overhead each day during peak hours while the database
> > expands the log files it previously shrank. My question is why does
> > this file continue to grow throughout the week? I would expect that
> > after each log file backup, the log segments that have been backed up
> > should become available to be reused. I don't see that they are issuing
> > a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> > light on this. The information I have found in BOL and online would
> > indicate that my understanding is correct and the log files should be
> > being reused after the backup. I just want to be sure before I go
> > searching for issues with open transactions, etc... Thanks for any and
> > all help...
> >
> > Kevin
> >
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||kevin.karlin@.gmail.com wrote:
> The only thing that doesn't appear to be happening that I normally
> implement on my datawarehouse boxes is issuing a checkpoint. I know
> that has to be done when you're in Simple Recovery mode, but I don't
> find reference to it being required when in Full Recovery mode. Could
> that be the problem?
>
Where are you getting the notion that you have to issue checkpoints?
Those should happen automatically in Simple mode. In the other modes,
backing up the transaction log performs the truncation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It seems I wasn't entirely clear in my second post. I normally work in
a DW environment not an OLTP environment. We don't have a need for
anything but Simple Recovery. The box that is having the problem is an
OLTP database using Full Recovery. I know that in Simple mode the
server will issue checkpoints automatically. However, my experience has
been that issuing a checkpoint right before a log truncation ensured
you got the most space released. This goes way back to version 6.5 as I
recall, so it may have become unnecessary at some point, but it is
still a habit. As far as the other recovery modes go - that was my
question, I couldn't find any documentation that said a checkpoint was
necessary in Full Recovery mode, but I wanted to be sure that that
wasn't the issue. At this point it seems that my understanding of what
should be happening is correct, now I need to find out what is
preventing the log backups from freeing the space.
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> >
> > The only thing that doesn't appear to be happening that I normally
> > implement on my datawarehouse boxes is issuing a checkpoint. I know
> > that has to be done when you're in Simple Recovery mode, but I don't
> > find reference to it being required when in Full Recovery mode. Could
> > that be the problem?
> >
> Where are you getting the notion that you have to issue checkpoints?
> Those should happen automatically in Simple mode. In the other modes,
> backing up the transaction log performs the truncation.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Log File Management

I am working with a client on a production OLTP database using Full
Recovery model. I normally work in datawarehouse environments with
Simple Recovery turned on. They are performing nightly Full Backups and
hourly log backups Monday through Friday. Sunday night is a normal full
backup followed by a log backup and shrinking the log file. Everyweek
the log grows from 200MB on Monday Morning to 12GB on Friday. I have
already recommended not shrinking the log files on Sunday nights as it
only introduces overhead each day during peak hours while the database
expands the log files it previously shrank. My question is why does
this file continue to grow throughout the week? I would expect that
after each log file backup, the log segments that have been backed up
should become available to be reused. I don't see that they are issuing
a checkpoint, and I'm wondering if that is the issue. Can anyone shed
light on this. The information I have found in BOL and online would
indicate that my understanding is correct and the log files should be
being reused after the backup. I just want to be sure before I go
searching for issues with open transactions, etc... Thanks for any and
all help...
Kevinkevin.karlin@.gmail.com wrote:
> I am working with a client on a production OLTP database using Full
> Recovery model. I normally work in datawarehouse environments with
> Simple Recovery turned on. They are performing nightly Full Backups and
> hourly log backups Monday through Friday. Sunday night is a normal full
> backup followed by a log backup and shrinking the log file. Everyweek
> the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> already recommended not shrinking the log files on Sunday nights as it
> only introduces overhead each day during peak hours while the database
> expands the log files it previously shrank. My question is why does
> this file continue to grow throughout the week? I would expect that
> after each log file backup, the log segments that have been backed up
> should become available to be reused. I don't see that they are issuing
> a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> light on this. The information I have found in BOL and online would
> indicate that my understanding is correct and the log files should be
> being reused after the backup. I just want to be sure before I go
> searching for issues with open transactions, etc... Thanks for any and
> all help...
> Kevin
>
Are they doing index rebuilds or some sort of importing during the week?
The log backups will flush out any committed transactions. Reindexing
(or index defragging) generates a LOT of transactional activity.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||kevin.karlin@.gmail.com wrote:
> The only thing that doesn't appear to be happening that I normally
> implement on my datawarehouse boxes is issuing a checkpoint. I know
> that has to be done when you're in Simple Recovery mode, but I don't
> find reference to it being required when in Full Recovery mode. Could
> that be the problem?
>
Where are you getting the notion that you have to issue checkpoints?
Those should happen automatically in Simple mode. In the other modes,
backing up the transaction log performs the truncation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It seems I wasn't entirely clear in my second post. I normally work in
a DW environment not an OLTP environment. We don't have a need for
anything but Simple Recovery. The box that is having the problem is an
OLTP database using Full Recovery. I know that in Simple mode the
server will issue checkpoints automatically. However, my experience has
been that issuing a checkpoint right before a log truncation ensured
you got the most space released. This goes way back to version 6.5 as I
recall, so it may have become unnecessary at some point, but it is
still a habit. As far as the other recovery modes go - that was my
question, I couldn't find any documentation that said a checkpoint was
necessary in Full Recovery mode, but I wanted to be sure that that
wasn't the issue. At this point it seems that my understanding of what
should be happening is correct, now I need to find out what is
preventing the log backups from freeing the space.
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Where are you getting the notion that you have to issue checkpoints?
> Those should happen automatically in Simple mode. In the other modes,
> backing up the transaction log performs the truncation.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Log file is full

I am using Simple Recover model for my database

Initial size of log file is 1 MB and auto growth is set to by 10%, unrestricted growth.

auto shrink property is set TRUE

i m using SSIS package to copy some data from one table to another ( in same database)

my package fails saying "The log file for database 'WebInterfaceData' is full. Back up the transaction log for the database to free up some log space."

i dont see ne reason y it is fail as i have set up autogrowth to incease by 10%

FYI. there nothing to deal with SSIS as when I try to run simple SQL statement in Management studio it also fails complaining about same error after package execution

can ne one help me in this regard?

thanx

Anas

How large is the transaction log? Run the query below to find out:

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *
FROM sys.database_files;

It sounds like you might have some long running transaction that is not letting the log get checkpointed, so that it can be truncated.

Does the problem go away (for a while at least) if you stop and start SQL Server?

|||

have u configured maxsize ?

select *from sysfiles

sp_helpfile

post the result

Madhu

|||

Obviously check the space on the disk that the logfile is on (I would assume that it is not full).

The other thing to watch out for is a disk quota of some description on the service account being used for SQL Server. This looks to SQL Server the same as a disk full error.

You might like to look in the logs for a System Error 112 (ERROR_DISK_FULL) which will be a symptom of either of these problems.

|||

Madhu K Nair wrote:

have u configured maxsize ?

select *from sysfiles

sp_helpfile

post the result

Madhu

the result of query is that and max size is set to -1 (means no max size)

1 1 47312 -1 10 1081346 0 WebInterfaceData_Data D:\SQL\MSSQL\data\WebInterfaceData_Data.MDF

2 0 2560 -1 10 1081410 0 WebInterfaceData_Log D:\SQL\MSSQL\data\WebInterfaceData_Log.LDF

Plus there is no issue like disk space

thanx,

Anas

Log file grew big ?

We have a table in SQL Server with 3 columns, and 1 of the columns is a
varchar(2000) column.
The database has a "simple" recovery model.
We insert data in the database every millisecond.
Every day, we delete data from the table that is older than 1 w old.
We then changed the column to be varchar(100), because the data is more
compact now.
Since we do this, I notice the log file grew from less than 100 meg to 2
gig. The data file is about 500 meg.
Why is the log file grew so much once I changed 1 of the column to be
varchar(100) ? Thank you very much.
Here is the stored procedure to delete data every day.
CREATE PROCEDURE DeleteDataInBatch
AS
declare @.LastCount smallint
set ROWCOUNT 5000
set @.LastCount = 1
while (@.LastCount > 0)
begin
begin tran
delete from ...
set @.LastCount = @.@.ROWCOUNT
commit tran
end
set ROWCOUNT 0
GO
Here is the stored procedure to insert data.
CREATE PROCEDURE InsertData
@.sContract varchar(8),
@.sData varchar(2000)
AS
insert into ...
GODid you use Enterprise manager to do the change? If so it usually creates a
new table behind the scenes, copies all the data over to it and then drops
the original table. All of this is fully logged and will require lots of
space.
Andrew J. Kelly SQL MVP
"fniles" <fniles@.pfmail.com> wrote in message
news:uhAArB1RFHA.3944@.TK2MSFTNGP10.phx.gbl...
> We have a table in SQL Server with 3 columns, and 1 of the columns is a
> varchar(2000) column.
> The database has a "simple" recovery model.
> We insert data in the database every millisecond.
> Every day, we delete data from the table that is older than 1 w old.
> We then changed the column to be varchar(100), because the data is more
> compact now.
> Since we do this, I notice the log file grew from less than 100 meg to 2
> gig. The data file is about 500 meg.
> Why is the log file grew so much once I changed 1 of the column to be
> varchar(100) ? Thank you very much.
> Here is the stored procedure to delete data every day.
> CREATE PROCEDURE DeleteDataInBatch
> AS
> declare @.LastCount smallint
> set ROWCOUNT 5000
> set @.LastCount = 1
> while (@.LastCount > 0)
> begin
> begin tran
> delete from ...
> set @.LastCount = @.@.ROWCOUNT
> commit tran
> end
> set ROWCOUNT 0
> GO
> Here is the stored procedure to insert data.
> CREATE PROCEDURE InsertData
> @.sContract varchar(8),
> @.sData varchar(2000)
> AS
> insert into ...
> GO
>|||> Did you use Enterprise manager to do the change?
Yes, I changed the column from varchar(2000) to varchar(100) in EM.
So, you think the log file grew big during the process of changing the
column from varchar(2000) to varchar(100) in EM ?
I though I checked the log file size after I did that, but I might be wrong.
With a log file 2 gig in size, will it slow down any querying, inserting or
deleting in the database ?
Do I need and can I shrink this log file size, or shall I leave it alone ?
Thank you very much.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OPBHJb1RFHA.164@.TK2MSFTNGP12.phx.gbl...
> Did you use Enterprise manager to do the change? If so it usually creates
> a new table behind the scenes, copies all the data over to it and then
> drops the original table. All of this is fully logged and will require
> lots of space.
> --
> Andrew J. Kelly SQL MVP
>
> "fniles" wrote in message news:uhAArB1RFHA.3944@.TK2MSFTNGP10.phx.gbl...
>|||I usually set my database log files to about 20 to 30 % of the total databas
e
space.
Also depends on how transaction intensive your database is.
I always restrict my log file size to a maximum value.
I do not thing you woul gain anything by having that big a log file size. I
woud have shrunk it after backing up the database and doing a dump of the
transaction log.
Nishant
"fniles" wrote:

> Yes, I changed the column from varchar(2000) to varchar(100) in EM.
> So, you think the log file grew big during the process of changing the
> column from varchar(2000) to varchar(100) in EM ?
> I though I checked the log file size after I did that, but I might be wron
g.
> With a log file 2 gig in size, will it slow down any querying, inserting o
r
> deleting in the database ?
> Do I need and can I shrink this log file size, or shall I leave it alone ?
> Thank you very much.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OPBHJb1RFHA.164@.TK2MSFTNGP12.phx.gbl...
>
>|||Thank you for your reply.
I do not want the log file to be that big.
We insert data to the database every miliseconds, currently the database is
about 500 meg in size and has about 15 million records in this table.
Is it too late to restrict the log file size now that it already grew to 2
gig ?
If I restrict the log file size, will it slow down inserting and deleting ?
How do I dump the transaction log ?
If I shrink the transaction log, will it slow down inserting and deleting
later ?
Thank you very much.
"NB" <NB@.discussions.microsoft.com> wrote in message
news:705DD5E1-3F87-4D71-86DD-AAFA78DE424A@.microsoft.com...
>I usually set my database log files to about 20 to 30 % of the total
>database
> space.
> Also depends on how transaction intensive your database is.
> I always restrict my log file size to a maximum value.
> I do not thing you woul gain anything by having that big a log file size.
> I
> woud have shrunk it after backing up the database and doing a dump of the
> transaction log.
>
> Nishant
> "fniles" wrote:
>|||There's no penalty to have a "too big" file. Having a small file does come w
ith a penalty, however:
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/
"NB" <NB@.discussions.microsoft.com> wrote in message
news:705DD5E1-3F87-4D71-86DD-AAFA78DE424A@.microsoft.com...
>I usually set my database log files to about 20 to 30 % of the total databa
se
> space.
> Also depends on how transaction intensive your database is.
> I always restrict my log file size to a maximum value.
> I do not thing you woul gain anything by having that big a log file size.
I
> woud have shrunk it after backing up the database and doing a dump of the
> transaction log.
>
> Nishant
> "fniles" wrote:
>

Log backup does not truncate the log file

Hi,
My db uses full recovery model. I used maintenance wizard to perform:
1) Index rebuild every night
2) Full backup once a week
3) Log backup every night
4) Integrity check every night
The log file is 1GB now and because of regular index rebuild I expected this
growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
row 240 which is 2.
My problem is that the log backup does not free the space to OS. I checked
shrink file GUI in EM and it says the minimum log file can be 23MB! So why
the log backup does not free the space?
Thanks in advance,
LeilaLeila wrote:
> Hi,
> My db uses full recovery model. I used maintenance wizard to perform:
> 1) Index rebuild every night
> 2) Full backup once a week
> 3) Log backup every night
> 4) Integrity check every night
> The log file is 1GB now and because of regular index rebuild I expected this
> growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
> row 240 which is 2.
> My problem is that the log backup does not free the space to OS. I checked
> shrink file GUI in EM and it says the minimum log file can be 23MB! So why
> the log backup does not free the space?
> Thanks in advance,
> Leila
>
To clear unused virtual files you can try:
DBCC SHRINKFILE(<database name>_log,<size in MB>)
for more details (and if that does not work) see:
http://support.microsoft.com/kb/272318/|||I know this command, but based on explanation in BOL, the BACKUP LOG must
truncate the log file automatically. Is it true or I have misunderstood
this?
Thanks!
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
> To clear unused virtual files you can try:
> DBCC SHRINKFILE(<database name>_log,<size in MB>)
> for more details (and if that does not work) see:
> http://support.microsoft.com/kb/272318/|||Leila wrote:
> I know this command, but based on explanation in BOL, the BACKUP LOG must
> truncate the log file automatically. Is it true or I have misunderstood
> this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>
The backup only marks unused VLFs as deleted but does not remove them.
The shrink does.|||You mean the shrink does not break the log chain?
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Leila wrote:
>> I know this command, but based on explanation in BOL, the BACKUP LOG must
>> truncate the log file automatically. Is it true or I have misunderstood
>> this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>>
> The backup only marks unused VLFs as deleted but does not remove them. The
> shrink does.|||Leila wrote:
> You mean the shrink does not break the log chain?
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> Leila wrote:
>> I know this command, but based on explanation in BOL, the BACKUP LOG must
>> truncate the log file automatically. Is it true or I have misunderstood
>> this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>> The backup only marks unused VLFs as deleted but does not remove them. The
>> shrink does.
>
Quoting from the aforementioned KB:
"Shrinking the log in SQL Server 2000 is no longer a deferred operation."
The shrink only removes unused VLFs that where invalidated during the
backup. You can only break the log chain by invalidating VLFs WITHOUT
backing them up and then shrinking. For example (from the KB):
BACKUP LOG pubs WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(pubs_log,2)
If you use TRUNCATE_ONLY you break the log chain and have to do a full
backup. Otherwise it should be safe (as far as I understand).|||Leila
TRUNCATE is a logical operation, not a physical one. The fact that DBCC
LOGINFO indicates all 0's means that the log has been truncated, and the
VLFs are now reusable. To reduce the physical size of the operating system
file, you have to DBCC SHRINKFILE.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
You misunderstood.
Truncate != shrink.
And generally you do NOT want to shrink the log file if it's just going to
grow again. This can lead to performance issues (as it expands each time)
and disk level fragmentation.
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>|||Thanks every body for clarifications :-)
BTW, I cannot browse your blog Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ueaCxRVJHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Leila
> TRUNCATE is a logical operation, not a physical one. The fact that DBCC
> LOGINFO indicates all 0's means that the log has been truncated, and the
> VLFs are now reusable. To reduce the physical size of the operating system
> file, you have to DBCC SHRINKFILE.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>>I know this command, but based on explanation in BOL, the BACKUP LOG must
>>truncate the log file automatically. Is it true or I have misunderstood
>>this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>>
>|||What do you mean?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OuDzdfZJHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Thanks every body for clarifications :-)
> BTW, I cannot browse your blog Kalen!

Log backup does not truncate the log file

Hi,
My db uses full recovery model. I used maintenance wizard to perform:
1) Index rebuild every night
2) Full backup once a week
3) Log backup every night
4) Integrity check every night
The log file is 1GB now and because of regular index rebuild I expected this
growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
row 240 which is 2.
My problem is that the log backup does not free the space to OS. I checked
shrink file GUI in EM and it says the minimum log file can be 23MB! So why
the log backup does not free the space?
Thanks in advance,
Leila
Leila wrote:
> Hi,
> My db uses full recovery model. I used maintenance wizard to perform:
> 1) Index rebuild every night
> 2) Full backup once a week
> 3) Log backup every night
> 4) Integrity check every night
> The log file is 1GB now and because of regular index rebuild I expected this
> growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
> row 240 which is 2.
> My problem is that the log backup does not free the space to OS. I checked
> shrink file GUI in EM and it says the minimum log file can be 23MB! So why
> the log backup does not free the space?
> Thanks in advance,
> Leila
>
To clear unused virtual files you can try:
DBCC SHRINKFILE(<database name>_log,<size in MB>)
for more details (and if that does not work) see:
http://support.microsoft.com/kb/272318/
|||I know this command, but based on explanation in BOL, the BACKUP LOG must
truncate the log file automatically. Is it true or I have misunderstood
this?
Thanks!
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Leila wrote:
> To clear unused virtual files you can try:
> DBCC SHRINKFILE(<database name>_log,<size in MB>)
> for more details (and if that does not work) see:
> http://support.microsoft.com/kb/272318/
|||Leila wrote:
> I know this command, but based on explanation in BOL, the BACKUP LOG must
> truncate the log file automatically. Is it true or I have misunderstood
> this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>
The backup only marks unused VLFs as deleted but does not remove them.
The shrink does.
|||You mean the shrink does not break the log chain?
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Leila wrote:
> The backup only marks unused VLFs as deleted but does not remove them. The
> shrink does.
|||Leila wrote:
> You mean the shrink does not break the log chain?
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
>
Quoting from the aforementioned KB:
"Shrinking the log in SQL Server 2000 is no longer a deferred operation."
The shrink only removes unused VLFs that where invalidated during the
backup. You can only break the log chain by invalidating VLFs WITHOUT
backing them up and then shrinking. For example (from the KB):
BACKUP LOG pubs WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(pubs_log,2)
If you use TRUNCATE_ONLY you break the log chain and have to do a full
backup. Otherwise it should be safe (as far as I understand).
|||Leila
TRUNCATE is a logical operation, not a physical one. The fact that DBCC
LOGINFO indicates all 0's means that the log has been truncated, and the
VLFs are now reusable. To reduce the physical size of the operating system
file, you have to DBCC SHRINKFILE.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
You misunderstood.
Truncate != shrink.
And generally you do NOT want to shrink the log file if it's just going to
grow again. This can lead to performance issues (as it expands each time)
and disk level fragmentation.

> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks every body for clarifications :-)
BTW, I cannot browse your blog Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ueaCxRVJHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Leila
> TRUNCATE is a logical operation, not a physical one. The fact that DBCC
> LOGINFO indicates all 0's means that the log has been truncated, and the
> VLFs are now reusable. To reduce the physical size of the operating system
> file, you have to DBCC SHRINKFILE.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>

Log backup does not truncate the log file

Hi,
My db uses full recovery model. I used maintenance wizard to perform:
1) Index rebuild every night
2) Full backup once a week
3) Log backup every night
4) Integrity check every night
The log file is 1GB now and because of regular index rebuild I expected this
growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
row 240 which is 2.
My problem is that the log backup does not free the space to OS. I checked
shrink file GUI in EM and it says the minimum log file can be 23MB! So why
the log backup does not free the space?
Thanks in advance,
LeilaLeila wrote:
> Hi,
> My db uses full recovery model. I used maintenance wizard to perform:
> 1) Index rebuild every night
> 2) Full backup once a week
> 3) Log backup every night
> 4) Integrity check every night
> The log file is 1GB now and because of regular index rebuild I expected th
is
> growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
> row 240 which is 2.
> My problem is that the log backup does not free the space to OS. I checked
> shrink file GUI in EM and it says the minimum log file can be 23MB! So why
> the log backup does not free the space?
> Thanks in advance,
> Leila
>
To clear unused virtual files you can try:
DBCC SHRINKFILE(<database name>_log,<size in MB> )
for more details (and if that does not work) see:
http://support.microsoft.com/kb/272318/|||I know this command, but based on explanation in BOL, the BACKUP LOG must
truncate the log file automatically. Is it true or I have misunderstood
this?
Thanks!
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Leila wrote:
> To clear unused virtual files you can try:
> DBCC SHRINKFILE(<database name>_log,<size in MB> )
> for more details (and if that does not work) see:
> http://support.microsoft.com/kb/272318/|||Leila wrote:
> I know this command, but based on explanation in BOL, the BACKUP LOG must
> truncate the log file automatically. Is it true or I have misunderstood
> this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>
The backup only marks unused VLFs as deleted but does not remove them.
The shrink does.|||You mean the shrink does not break the log chain?
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Leila wrote:
> The backup only marks unused VLFs as deleted but does not remove them. The
> shrink does.|||Leila wrote:
> You mean the shrink does not break the log chain?
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
>
Quoting from the aforementioned KB:
"Shrinking the log in SQL Server 2000 is no longer a deferred operation."
The shrink only removes unused VLFs that where invalidated during the
backup. You can only break the log chain by invalidating VLFs WITHOUT
backing them up and then shrinking. For example (from the KB):
BACKUP LOG pubs WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(pubs_log,2)
If you use TRUNCATE_ONLY you break the log chain and have to do a full
backup. Otherwise it should be safe (as far as I understand).|||Leila
TRUNCATE is a logical operation, not a physical one. The fact that DBCC
LOGINFO indicates all 0's means that the log has been truncated, and the
VLFs are now reusable. To reduce the physical size of the operating system
file, you have to DBCC SHRINKFILE.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
You misunderstood.
Truncate != shrink.
And generally you do NOT want to shrink the log file if it's just going to
grow again. This can lead to performance issues (as it expands each time)
and disk level fragmentation.

> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>|||Thanks every body for clarifications :-)
BTW, I cannot browse your blog Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ueaCxRVJHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Leila
> TRUNCATE is a logical operation, not a physical one. The fact that DBCC
> LOGINFO indicates all 0's means that the log has been truncated, and the
> VLFs are now reusable. To reduce the physical size of the operating system
> file, you have to DBCC SHRINKFILE.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>