Database size vs total table and index size

I use SQL Server 2000.

My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.

My question:

How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?

When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.

I am grateful for any help.

Regards,

Jan Nordgreen

Reply

"damezumari"  wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
>
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen

This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that 
-
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of you
r
shrink statement ...

First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.

Your issue could be based on several things like:

You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:

"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."

You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.

Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:

HTH, Jens Suessmeyer.