Space Available for database is 0

By mchauhan ·

My sql database is showing space available for one of the database is 0.
I have got SQL Server 2005 Ent. Edition(32bit). File growth for .mdf file is set to Autogrowth 10% growth Unrestricted.

I have got lots of physical space on hard drive, why then it is showing 0.00 space available for database. What do i need to do to free up space.

When i ran sp_spaceused it gave me following results

database_name database_size unallocated space
XXXX 1817.31 MB 0.00 MB

reserved data index_size unused
1860760 KB 1763496 KB 13240 KB 84024 KB

I would really appreciate if anyone could tell me what is going on over here and would this slow down the data access?


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

The 0 is unallocated space

by Tony Hopkinson In reply to Space Available for datab ...

If the file is growing as necessary the you don't have a problem. The only performance penalty from having zero space is SQL server will have to make a call to the OS to allocate more disk space to the mdf.
That can get quite expensive if say you start with the default 1M and then add 30M of data from queries.

upadteusage = 'true' will force the stats to be recalced, they can get messed up.

You could try increasing the size of the mdf, 0 unallocated could easily be an indicator of a successfully optimised database, which has hit equilibrium in terms of size.

To free up space, get rid of some stuff. Another possible saver in terms of space is high fill factors in indexes on static tables.

Collapse -

Few more Questions?

by mchauhan In reply to The 0 is unallocated spac ...

Thank you very much Tony for you simple but very effective response.

I have checked the logs and i am not getting any errors but the data access has gone very slow.

Meanwhile i was wondering if there is lots of physical disk space available then why is SQL not using it and how can i make it use that space.

How do i solve the problem of "high fill factors in indexes on static tables"?


Related Discussions

Related Forums