SQL 2000 General Property - Space Available

[MrPeds]

Hi,

One of my customers SQL Server 2k databases has a value of 0.00MB for the setting 'Space Available' which you can view by right-clicking on the database > Properties > general tab.

What does this mean? How is it calculated? Is it something that needs to be investigaged, and if so, what do i need to look at?

We are currently investigateing a problem with the database, and wondered what affect (if any) this value of zero would have.

Any help is appreciated.

MrPeds

 

[SQLBill]

It just means that the database needs to expand (autogrow or be expanded manually) with the next transaction. I have had that happen and there's a small performance 'hit' while the database expands. Is your database set to autogrow? If so, by how much? How much does your database normally increase by daily? The autogrow should be set to a size larger than the normal daily increase.

-SQLBill

Posting advice: FAQ481-4875

 

[MrPeds]

The data file is set to auto-grow by 100MB each time (with no manximum size). Also the log file is set to auto-grow as well by 100MB each time.

We were getting some MOM (Ms operations manager) errors in the event log relating to sql server (cant remember the exact message off the topp of my head) and we werent sure if this zero would affect this event log entry or if we have 2 different issues.

Baiscally we have a dts package that imports some data and then runs some stored procs. Last week the process took 3 minutes, now its taking 3 hours or more. Hence we are trying to work out what the customers have changed or what the underlying problem is. The MOM issue could be separate though.

MrPeds

 

[SQLBill]

You probably don't have your autogrow set high enough. Most likely you are getting more than 100MB of data at one time. Also, do you shrink the database? Could auto-shrink be set? If so, then every so often the database will remove the unused space which will leave it at zero.

My database is set to grow by 5000 MB at a time (that usually means it only has to grow once a week). I rarely ever shrink the database, only if I have purged (deleted) lots of data do I shrink the database.

-SQLBill

Posting advice: FAQ481-4875