No truncating.

Hello eveybody !
I have a small problem with my logs on sql server .
Even after a backup (lod and/or db), server seems to not truncate his
logs ... so they're growing each days, and need to be deleted
manually.
Any ideas ?Hi,
After backup the physical LDF file will not get reduced automatcally. But
the logical space will be reduced by looking into
dbcc sqlperf(logspace)
To shrink the physical ldf file after the log backup use dbcc shrinkfile
command. See the dbcc shrinkfile command in boks online.
Thanks
Hari
MCDBA
"Frater" <None@.legioobscurantis.com> wrote in message
news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> Hello eveybody !
> I have a small problem with my logs on sql server .
> Even after a backup (lod and/or db), server seems to not truncate his
> logs ... so they're growing each days, and need to be deleted
> manually.
> Any ideas ?|||Can't you just set the database to autoshrink with:
sp_dboption database_name, 'autoshrink' TRUE
In article <#xXRVRhdEHA.3988@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> After backup the physical LDF file will not get reduced automatcally. But
> the logical space will be reduced by looking into
> dbcc sqlperf(logspace)
> To shrink the physical ldf file after the log backup use dbcc shrinkfile
> command. See the dbcc shrinkfile command in boks online.
> "Frater" <None@.legioobscurantis.com> wrote in message
> news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> > Hello eveybody !
> >
> > I have a small problem with my logs on sql server .
> >
> > Even after a backup (lod and/or db), server seems to not truncate his
> > logs ... so they're growing each days, and need to be deleted
> > manually.
> >
> > Any ideas ?|||Frater,
Truncating and shrinking are two different things.
Truncating the log (logically) cleans up space which was previously used in
the log so that it can be re-used. The log is a circular file which tries to
re-use the same space over and over. Truncating the log does NOT change the
physical size of the log... If the log is NOT truncated, then space can not
be re-used, so the log will grow to acquire the necessary space. Truncating
the log occurs automatically if the database is in SIMPLE recovery mode. The
log is truncated during a transaction log backup as well..
Shrinking the log can be done AFTER the log has been truncated. Use DBCC
Shrinkdatabase or DBCC Shrinkfile to physical reduce the log file size...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frater" <None@.legioobscurantis.com> wrote in message
news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> Hello eveybody !
> I have a small problem with my logs on sql server .
> Even after a backup (lod and/or db), server seems to not truncate his
> logs ... so they're growing each days, and need to be deleted
> manually.
> Any ideas ?|||Hi Brad
Shrinking the datasbase will shrink ALL the files, not just the log files,
and the autoshrink option will do this every 30 minutes.
It is incredibly resource intensive, as it tries to move all data in the
files to other places in the files, and all kinds of adjustments to indexes
might need to be done as a result.
Autoshrink is definitely NOT recommended for a production system.
The log file must be managed separately, as per the other suggestions in
this thread.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Brad Murray" <brad@.seesigifthere.com> wrote in message
news:MPG.1b73f47b2134b1ff989682@.news...
> Can't you just set the database to autoshrink with:
> sp_dboption database_name, 'autoshrink' TRUE
> In article <#xXRVRhdEHA.3988@.tk2msftngp13.phx.gbl>,
> hari_prasad_k@.hotmail.com says...
> >
> > After backup the physical LDF file will not get reduced automatcally.
But
> > the logical space will be reduced by looking into
> >
> > dbcc sqlperf(logspace)
> >
> > To shrink the physical ldf file after the log backup use dbcc shrinkfile
> > command. See the dbcc shrinkfile command in boks online.
> >
> > "Frater" <None@.legioobscurantis.com> wrote in message
> > news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> > > Hello eveybody !
> > >
> > > I have a small problem with my logs on sql server .
> > >
> > > Even after a backup (lod and/or db), server seems to not truncate his
> > > logs ... so they're growing each days, and need to be deleted
> > > manually.
> > >
> > > Any ideas ?