Auto shrink not working as expected

I'll be the first to admin, I am not a sql expert. I have a nightly job
that backs up the tran logs on my db's and the box is checked to autoshrink
the tran log when it exceeds 100 mb. There are no errors and the shrink is
executed as shown in the log reports but the size doesn't appear to change.
What is really stumping me is if I do it manually, I have to do a tran log
backup, shrink the db. This results in a few mb shrinkage. If I then go
back and do the same thing again (This is consistent on four major DB's on
this server) tran log backup followed by a shrink db it then shrinks the
tranlog as expected. Several of these db's aren't activily being updated at
the backup/shrink time so records aren't being inserted (At least not that I
am aware of) at the time.
Is there something I am not doing or an idea someone may have to do this?
The backup and shrink are being handled via the setup by Enterprise Manager.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.An obvious question is why do you think you need to shrink the files every
day? If they grow every day then all you're doing is slowing down your
database every day because it has to grow the file. Do you tear down your
garage every time you back your car out and build it again when you come
home? This is about the same logic as shrinking the database and log files
daily. You should only shrink the files when you know they aren't going to
grow again.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Roger is 100% correct but these may be of interest to you as well:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||The transaction log backup is nightly, the attempted shrinking is weekly. I
don't believe a tran log needs to be 10 gig in size, considering the data
file is only 2 gig in size. I would have thought the nightly tran log back
up would force the tran log to re-use from the truncation point forward but
that doesn't seem to be occuring. The goal is to keep the tran log the max
log that could be used in a day, which is almost 2 gig in size.
Am I doing some thing wrong? Probably, but what I don't know.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
> An obvious question is why do you think you need to shrink the files every
> day? If they grow every day then all you're doing is slowing down your
> database every day because it has to grow the file. Do you tear down your
> garage every time you back your car out and build it again when you come
> home? This is about the same logic as shrinking the database and log
> files daily. You should only shrink the files when you know they aren't
> going to grow again.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>|||Paul
You don't need to shrink the log , because it is meaningless as it will be
grown againg and again. One option is set up the log file with an appropiate
size that it does not need to grow frequently .
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The transaction log backup is nightly, the attempted shrinking is weekly.
> I don't believe a tran log needs to be 10 gig in size, considering the
> data file is only 2 gig in size. I would have thought the nightly tran
> log back up would force the tran log to re-use from the truncation point
> forward but that doesn't seem to be occuring. The goal is to keep the
> tran log the max log that could be used in a day, which is almost 2 gig in
> size.
> Am I doing some thing wrong? Probably, but what I don't know.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
>|||I don't think people understand my predicament.
I have four specific db's on my sql server 2000 server. I run these in Full
Recovery mode with nightly tran log and weekly full back ups. The log file
in some instances is more than 5 times the size of the db. I find it hard
to believe that this would be considered normal since a nightly job would
never have more info than the db itself.
If you can provide details as to why this is normal, please do.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238Ke8jGwGHA.4444@.TK2MSFTNGP05.phx.gbl...
> Paul
> You don't need to shrink the log , because it is meaningless as it will
> be grown againg and again. One option is set up the log file with an
> appropiate size that it does not need to grow frequently .
>
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
>|||Paul Bergson wrote:
> I don't think people understand my predicament.
> I have four specific db's on my sql server 2000 server. I run these in Fu
ll
> Recovery mode with nightly tran log and weekly full back ups. The log fil
e
> in some instances is more than 5 times the size of the db. I find it hard
> to believe that this would be considered normal since a nightly job would
> never have more info than the db itself.
> If you can provide details as to why this is normal, please do.
>
Are you doing something like rebuilding indexes at night? Large imports?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Imports can be large
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uKZQv8GwGHA.4296@.TK2MSFTNGP06.phx.gbl...
> Paul Bergson wrote:
> Are you doing something like rebuilding indexes at night? Large imports?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul Bergson wrote:
> Imports can be large
>
Ok, that could explain the large transaction log file. Say you're
importing 100,000 new rows of data, all as one transaction. The
transaction log has to be able to hold that entire transaction, in case
it has to roll back.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This may turn out to be a mute point. I'm on the phone with the vendor and
they are some how using the Log Files to store log history. It sounds like
it is unrelated to the actual logs that are need for roll back. I don't get
it. I need to get more info if this is the case. It sounds to me like they
have a configuration option which could allow me to control history kept
within this.
Maybe this is normal use, seems odd to me though.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O57rkXHwGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Paul Bergson wrote:
> Ok, that could explain the large transaction log file. Say you're
> importing 100,000 new rows of data, all as one transaction. The
> transaction log has to be able to hold that entire transaction, in case it
> has to roll back.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com