Posted by Erland Sommarskog on 02/28/06 00:43

Matik (marzec@sauron.xo.pl) writes:
> So I've changed the database model, and it seems to look much better
> than it was before. And I have up-to-time recovery possibility.
>...
> Curently, database size shows 18GB, but the used space shows 8GB.
> For a log file, it shows 8GB but the used space is 34MB.

Used log space of 34 MB sounds suspiciously small to me, when you at
the same time say that you have point-in-time recovery possibility.
That is, you are running with full (or bulk-logged) recovery. Then
again, 34 MB of used log space is alright if you recently backed up
the transaction long.

> With my understanding, it is better not to shrink these files, because
> during running processes, sql server will expand size of the files
> anyway.
> Shirinking files, will cause that by expanding files, sql server will
> need more time, because of generation more io traffic (right?).
>
> But, from the other side, I do not need to keep this files so big!
> (This probably remains big anyway from my old model, where I've also
> have had not optimized "delete old data" cleaning job).

Let's take the data and log files separately.

If you have a data file of 18 GB and only 8 GB used, and you don't
expect any particular growth, you could shrink the file to 10 GB.
One thing to keep in mind here is the size of your biggest table.
There should be enough free space to permit reindexing of this table.
(I believe that when a table is reindexed it is essentially moved to
new fresh pages, but I have not investigated this in detail, and I
may be wrong on this.)

But if you expect the database to continue to grow, then there is little
reason to shrink it.

If you do shrink your data file, you should then reindex all tables
to defragment them, as shrinking often leads to serious fragmentation.

For the log file, you need to find the peak size of the log file. 34 MB
in use may be now, but if you run some heavy maintenance job, you may find
that 2GB of the log is used. (Reindexing for example. But run reindexing
with bulk-logged recovery, that reduces the log growth considerably.

8GB log for the aame amount of data sounds a bit excessive, but it
depends a lot on the operations you do on your data. I guess one way
to find out the peak size, is to keep an eye on the size of the log
backups, particularly after you have performed some large update, such
a reindexing job.



> The question then is: is it better to shring time-to-time, or let say
> now, the database files, or leave it just in this way?
>
> Gratings
>
> Matik
>



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

[Back to original message]