Log

Please correct my concept, as I am thinking when log backup it auto shrink
the file to its mininum point like when I take the log backup and see the
size it's still on the same size but yes when I shrink the log then it's
goes into minimum size so now the
point is like I do take only the log backup every day after every 15 mins
but the log is not shrinking... can any one update me like what I have to do
for this ?
You should turn off autoshrink. Let the log file (and data files) stay at
the size it needs to do it's work plus some free space. Shrinking is
generally a bad thing not a good one.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:er5TNnXVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Please correct my concept, as I am thinking when log backup it auto shrink
> the file to its mininum point like when I take the log backup and see the
> size it's still on the same size but yes when I shrink the log then it's
> goes into minimum size so now the
> point is like I do take only the log backup every day after every 15 mins
> but the log is not shrinking... can any one update me like what I have to
> do
> for this ?
>
|||I have a backup strategy to take the full backup daily and log every after
15 mins. that's why I am asking like if log backup then the size should also
decrease.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eqfxpIYVFHA.3424@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You should turn off autoshrink. Let the log file (and data files) stay at
> the size it needs to do it's work plus some free space. Shrinking is
> generally a bad thing not a good one.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:er5TNnXVFHA.3152@.TK2MSFTNGP12.phx.gbl...
shrink[vbcol=seagreen]
the[vbcol=seagreen]
mins[vbcol=seagreen]
to
>
|||Andrew, should I disable to autoshrink so when the log will shrink? never ?
"Joh" <joh@.mailcity.com> wrote in message
news:erYxm0YVFHA.2700@.TK2MSFTNGP12.phx.gbl...
> I have a backup strategy to take the full backup daily and log every after
> 15 mins. that's why I am asking like if log backup then the size should
also[vbcol=seagreen]
> decrease.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eqfxpIYVFHA.3424@.TK2MSFTNGP09.phx.gbl...
at[vbcol=seagreen]
> shrink
> the
it's
> mins
> to
>
|||If you turn it off it will never shrink. That is the desired effect. As
long as you issue regular log backups it should not continue to grow past a
size that it needs to operate on a regular basis. That is as long as you
don't have any very long running open transactions that will prevent the log
from being truncated during the backup.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:ujF9p6YVFHA.2540@.tk2msftngp13.phx.gbl...
> Andrew, should I disable to autoshrink so when the log will shrink? never
> ?
> "Joh" <joh@.mailcity.com> wrote in message
> news:erYxm0YVFHA.2700@.TK2MSFTNGP12.phx.gbl...
> also
> at
> it's
>
|||If you had a long running open transaction it may not be able to shrink
until the transaction is committed.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:erYxm0YVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>I have a backup strategy to take the full backup daily and log every after
> 15 mins. that's why I am asking like if log backup then the size should
> also
> decrease.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eqfxpIYVFHA.3424@.TK2MSFTNGP09.phx.gbl...
> shrink
> the
> mins
> to
>
|||Joh wrote:
> Andrew, should I disable to autoshrink so when the log will shrink?
> never ?
>
When you backup the log, it is truncated. The physical size does not
change, but the backed-up data in the log is removed. You should already
have the size set to avoid an auto-grow operations during normal
operation. There's no reason to shrink the physical log files unless
you're dealing with a runaway transaction that caused the log to grow to
an enormous size.
David Gugick
Imceda Software
www.imceda.com
|||Hi,
To add on to Andrew, just execute DBCC OPENTRAN command to check if there
is any open active transactions. If there is not then try to
shrink the LDF file manually and see if you could shrink. See DBCC
SHRINKFILE in books online. Perform a transaction log backup before the
shrink file command.
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23%232divZVFHA.3152@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> If you had a long running open transaction it may not be able to shrink
> until the transaction is committed.
> --
> Andrew J. Kelly SQL MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:erYxm0YVFHA.2700@.TK2MSFTNGP12.phx.gbl...
after[vbcol=seagreen]
have
>
|||Thanks Hari, like in a day there are 24 Logfiles exists in one folder so in
the job I can mention after the log backup to shrink the log file but the
problem is there is there any query like
If Exists(
DBCC OpenTran
)
Begin
DBCC ShrinkFile(logFileName,EMPTYFILE)
End
what you prefer should I use EMPTYFILE or TRUNCATEONLY or NOTRUNCATE
Thanks and waiting for your reply
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#EESi7cVFHA.2328@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> To add on to Andrew, just execute DBCC OPENTRAN command to check if there
> is any open active transactions. If there is not then try to
> shrink the LDF file manually and see if you could shrink. See DBCC
> SHRINKFILE in books online. Perform a transaction log backup before the
> shrink file command.
> Thanks
> Hari
> SQL Server MVP
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23%232divZVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> after
should[vbcol=seagreen]
stay[vbcol=seagreen]
see[vbcol=seagreen]
15
> have
>