DB Has 0 MB space available - and very slow performance

[JohnBates]

Hi,

Sorry to cross-post, but I'm in a bind....

I have 2 SQL Server 2000 (SP3a) databases -

# 1 is 25 GB size and properties show 0 MB space avail
Any query/update to tables in # 1 is very slow


# 2 is 28GB and has 8700 MB space available.

Both are set to * Auto grow file *by 10 percent

Disk drive has 8 GB free.

I'm not sure why DB # 1 is not automatically growing.

My questions is: ** How would 0 MB space avail affect performance in DB # 1 ? ** What is SQLl Server having to do for DB # 1 that it doesn't have to do for DB # 2?

Thanks, John

 

[JBaileys]

John,

This is quite unusual. I would suggest running dbcc (database consistency check) to see if there are any issues with your db. It may be that SQL Server is reporting bad information to you - this may also have to be reviewed. I would also do a backup as soon as possible - it sounds like you have a "flakey" situation. Do you have another server that you can out the dbs on?

Syntax from BOL:
-- Check the current database
DBCC CHECKDB

 

[JohnBates]

JBaileys, DBCC CHECKDB ran for about 10 mins (it is a very large database 25GB)... then said "0 inconsistencies, 0 errors" or something to that effect.

I was able to increase the allocation for both the data file and the T-log file.... it reflects the changes....

but the DB Properties still says '0 MB space avail'

Isn't that weird? How can you make it recompute that value?

Thanks for the tip. John

 

[JayKusch]

Disconnect your session to Enterprise Manager and then reconnect. Does this fix the issue?

Thanks

J. Kusch

 

[SQLBill]

Is the disk partitioned in any way?

Increasing a 25 GB database by 10% would mean there would have to be 2.5 GB of space available, which you appear to have.

How did you get the 25GB value? Is that Data only or is it the total database size including indexes, etc.

Run DBCC UPDATEUSAGE ('dbname')
Then run:
USE dbname
EXEC sp_spaceused

The DBCC will update the sysindexes values. The sp will give you the amount of space used by the whole database and portions of the database.

-SQLBill

 

[JohnBates]

... here's an update on the situation.

I later found that someone had changed this database to 'Auto Shrink'. That caused SQL Server to spend alot of resources shrinking and then expanding the db as we were copying large tables to/from this server.

I think that account for everything because after switching it back to NO auto shrink (which is the way I originally configured it) it is back to normal.

1 of a thousand reasons not to give Enterprise Manager to the programmers.

Thanks for everyone's input. John

 

[SQLBill]

One other thing to consider. DB#2 had 8700 MB of free space available. That roughly converts to 8 GB and that's the amount of free disk space.

Possibly, DB#2 autogrew and took up the 8 GB of space. It really didn't need that amount but it was required to 'grab' it because of the 10% autogrow. Then when #1 needed to autogrow there wasn't any disk space left (#2 had it 'locked' up for its own use).

Autoshrink will let the databases give up excess empty space, but the real solution would be to change your autogrow from percentage to MB. Figure out what size your common transactions are and set the databases to grow enough to cover that. You'll have to play with it to get it to the right amount where it grows enough.

-SQLBill