Re: SPS2003 SQL Database sizes?
From: Martin (martin_at_hotmail.com)
Date: 01/03/05
- Next message: Martin: "SBS 2003, SPS and AD"
- Previous message: Hollis D. Paul: "Re: Publishing Previous sites"
- In reply to: Sara Windsor: "Re: SPS2003 SQL Database sizes?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 4 Jan 2005 11:37:14 +1300
Hi sara,
whether the sql server log (ldf) file grows and to what extent is mainly
determined by the recovery mode of the sql server.
If you don't want the log file to grow (or at least not grow very much) then
set the recovery model to simple.
This of course has the downside of if you ever need to recovery your
database from backup the you can only do so from your last FULL backup -
which may have been some time ago (so back up reguarly if you intend to use
this, and make sure that in the worst case you will never loses more data
from your database than you can afford --- which will depend on a lot of
factors and who you talk to :)...).
alternatively, you can set you recovery mode to FULL and back up the
transaction log say very hour or so. This means that your sql server log
will grow but will be purged every hour -- however note that this process
does not return the freed space within the ldf file to the operating system
(it merely creates free space within the ldf file). if you want to do this
then you will have to run DBCC shrinkdatabase / shrinkfile with the
appropriate options to free the space.
if you are going to keep transaction log backups then you will have to make
sure that you keep ALL of the backups safe (and in order) after your last
full back up, so that you can "roll forward" from your last full backup if
you ever need to.
The way this sql server log works takes a lot of reading before you totally
understand it (at least it did with me..)
If I were you I'd look up RECOVER MODELS and DBCC shinkfile / DBCC
shrinkdatabase in sql server books on line.
HTH.
cheers
martin.
"Sara Windsor" <SaraWindsor@discussions.microsoft.com> wrote in message
news:52556D8D-629C-4360-9810-921FE0B6E04E@microsoft.com...
> Thanks! Makes sense.
> If one is using SharePoint's builtin backup/recovery tool, does anyone
know
> if there are any SharePoint recommendations for these settings within SQL?
> Since I'm not really sure how much history I *should* keep given my setup
> (medium server farm per Microsoft's Solution Accelerator for Intranets),
I'm
> not sure how to proceed - but it certainly doesn't seem wise to just let
the
> logfile sizes grow indefinitely.
> TIA.
>
> "Bil Simser [MVP]" wrote:
>
> > Sara,
> >
> > The LDF files are log files and used for restoring the transactions in
> > SQL. If you're doing file level backup on SharePoint or using some other
> > tool for restoring SharePoint then you probably don't need them. If
> > you're using them for restore purposes, it's a SQL setting to determine
> > how often they roll over or how big they grow. By default (I think) they
> > just keep growing until you manually remove them. You'll need to
> > determine how much history to keep that works for your setup and
> > configure it in SQL Server.
> >
> > --
> > Bil Simser
> > http://weblogs.asp.net
> >
> >
> >
> > "Sara Windsor" <SaraWindsor@discussions.microsoft.com> wrote in message
> > news:SaraWindsor@discussions.microsoft.com:
> > > As my number of SPS2003 clients increases, of course the size of the
> > > databases (especially _SITE) is growing in leaps & bounds. I've
noticed
> > > that
> > > the SQL database LDF files (which I think are the logfiles?) are now
much
> > >
> > > larger than the MDF files (which I think are the primary database
files?).
> > >
> > > I'm running successful backups via SPS's builting backup/restore
tool...
> > > and
> > > SQL logs the successful backups... but the LDF files remain huge.
Assuming
> > >
> > > that the LDF files are the logfiles - do they ever get purged during
> > > backups?
> > > or do they just continue to grow with the database?
> > > TIA.
> >
> >
- Next message: Martin: "SBS 2003, SPS and AD"
- Previous message: Hollis D. Paul: "Re: Publishing Previous sites"
- In reply to: Sara Windsor: "Re: SPS2003 SQL Database sizes?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
