FREE SPACE 0.00 MB??

Question

FREE SPACE 0.00 MB??

The_SQL_DBA

SSChampion

Points: 10494
More actions
November 13, 2008 at 6:28 am

#215172

Hi All,
I have a 14Gb DB, with both data and log files set to Autogrowth. There is 20 GB of space available on both the disks these files are on , but the properties tab shows that DB size = 14232.38MB and space available = 0.00MB
What does this mean? I increased the space allocated on both data and log file by 100Mb each, still the space available is shown as 0.00MB .
The DB is in full recovery mode and set to Auto update statistics. The logs are backed up every hour. I hear the customers complaining that things are slow too. I found the reason for it as fragmentation on some key tables. I would reindex them later today, but not able to figure why the 0.00 MB space available message...any ideas will be appreciated.
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try doing an update usage. On SQL 2000 the space use and space available metadata was sometimes inaccurate. Updateusage will fix that.

DBCC UPDATEUSAGE(< Database name > )

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 2

Can I do it during the normal workload or is it to be done during off peak hours?

Thanks for your help..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736445

November 13, 2008 at 8:30 am

#898136

It should be fine during normal workloads.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 4

Thanks Steve, I ran it a while ago. The available space is now shown at 784 MB.

Gail, thank to you too..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

You really shouldn't rely on auto grow to manage your database. The auto grow process is very expensive and can cause response time issues as the system waits for the database files to grow.

When you try to rebuild an index, if there is not enough space available in the database file you are not going to get a clean rebuild of the index. Also, the index rebuild may have to wait on an auto grow before it can complete.

My suggestion is to leave enough space available in the data file to rebuild your largest index. If you don't know what that is, and the database is small enough (e.g. not 100's of GB), then use 20% as your guide line and adjust from that as needed. For a 14GB database, I would leave at least 3GB of free space. That puts us at 17GB - and just because I like round number I would round that up to 20GB.

So, schedule some time during off-peak hours and extend your database to 20GB. Then, start monitoring the database and maintain the free space in the database by scheduling the time you grow the database.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs