Re: question about autogrowth and shrinkfile

geekyguy (geeky@xxxxxxx) writes:
The first time I backed it up, a 14-gig backup was created (which took
about 25 minutes), but the shrinkfile failed (with 'all logical files in
use' error). I ran the backup a second time, and then shrinkfile worked.
I've noticed the same thing has happened in the past with large
transaction logs.
A few questions:

Did the first shrinkfile fail because there SQL hadn't updated the
statistics for the tlog or something like that? If so, do I need to
execute an additional command between "backup log" and "shrinkfile", or
am I doing something out of sequence?

Tibor's article discusses a problem where you had to clear the end of
log file, for the shrink to be possible, but as I recall that applied
to SQL 7, so I don't really have an answer. But it's a matter of
curiousity since you should not shrink very often.

Thanks...it does seem that the backup has to occur twice before the log can be shrunk.

Secondly, I'm curious as to what I should set the auto-growth to for a
dB of this size. I've read that 1/8 the dB size is appropriate, but in
this case that would be 750 MB for the dB?

I would set it to 1 GB as a round number, but 750 MB sounds alright by
me. Of course, auto-grow is something that should never occur. That is,
you should increase the space ahead in a maintenance window. Hm, you
said this database was 6 GB today. I don't know what the trends are,
but I would make the database 10 GB or even 15 GB, so I could forget
about its size for a while.

The database does not seem to be growing very quickly...perhaps by a gig a year.


I was able to get the log down to 20 meg with shrinkfile, but what
should I set the auto-growth to for the log?

20 MB log for a database of 6GB? That can't be right! First of all, as
with the data file, auto-grow should not really occur with the log.
Actually, in a way it should happen even less with log files. After all,
it's evitable that the data file grows by time, but the log should
stabilise on some size.

I just got properties on the dB, and it's showing as size: 5270.38MB with 1469.08MB free. I imagine that it must have recently "auto-grown" by 20% before the backup/shrink that was executed 48 hrs ago.

In terms of the transaction log, it's possible that the database has only been lightly loaded for the past 48 hours since the initial backup/shrink of the log (the dB is the backend of an ecommerce website that doesn't get a lot of traffic during the weekend). In the past 24 hours, it did perform one autogrow by 100 meg, and is now showing as 111 meg on the disk...so it may be growing at a rate of about 100-200 meg/day.


Now, what that size would be depends on your database. The only number is
we have is that the log was 14 GB at some point, but it appears that
you had neglected to backup the transaction log. But say that the
database had been running like that for a fortnight.

It was longer than that...several months...

That gives 1 GB
log per day. Supposedly you back the log more than once a day, but
then again some operations take more log than others, so if it was
a fortnight, make the log 1GB. Or 1.5 GB to have some more margins.
For auto-grow I would probably go with 0.5 GB.

So if I become more proactive and really track the growth, as well as maintain regular backups of the transaction log (hourly?), if it's only growing at say 200 meg/day max, I could fix it's size at 1 gig and not worry about it? I will probably leave auto-grow on just to be safe, but I want to make sure I understand the sizing issues.

Thanks for the help, Erland!

.

Relevant Pages

  • Re: Recovery model and log file shrinking
    ... their affect on a database. ... But even this backup of log file ... the log file is still 100mb. ... last two lines again(backup log & shrinkfile), ...
    (microsoft.public.sqlserver.programming)
  • Re: question about autogrowth and shrinkfile
    ... I ran the backup a second time, and then shrinkfile worked. ... auto-grow is something that should never occur. ... said this database was 6 GB today. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL 2008 -- "Backup Log with TRUNCATE ONLY" Deprecated
    ... The checkpoint needs running in the database you are truncating the log in... ... dbcc shrinkfile(2) ... Doing the "backup log database with truncate_only" ...
    (microsoft.public.sqlserver.programming)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Re: RMAN restore of full backup fails completely
    ... You are not using a catalog database. ... You might have autobackup controlfile on, ... Your autobackup has been made *prior* to the backup! ... plus archivelog', when i try a restore on the other server however, the ...
    (comp.databases.oracle.server)
Loading