Re: question about autogrowth and shrinkfile
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 29 Mar 2008 08:11:52 -0700
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.
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.
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.
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. 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.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: question about autogrowth and shrinkfile
- From: geekyguy
- Re: question about autogrowth and shrinkfile
- References:
- question about autogrowth and shrinkfile
- From: geekyguy
- question about autogrowth and shrinkfile
- Prev by Date: question about autogrowth and shrinkfile
- Next by Date: How to add a server conn in business Intelligence Development Stud
- Previous by thread: question about autogrowth and shrinkfile
- Next by thread: Re: question about autogrowth and shrinkfile
- Index(es):
Relevant Pages
|
