DB Space Available: 0 MB and Very Slow Performance

[JohnBates]

Hi,

I have 2 SQL Server 2000 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.

Bothe 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 doen't have to do for DB # 2?

Thanks, John

 

[nigelrivett]

Manually increase the size of #1 then set it to grow.
Which service pack do you have?

I would guess that things in #1 are failing and rolling back causing it to take time or maybe it can't checkpoint.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLSister]

You may well be doing a task that takes more than 10% growth to accomplish. Also have you cheked the transaction logs, are they also set to grow?

 

[JohnBates]

thanks nigelrivett and SQLSister !

nigelrivett: "Both are set to * Auto grow file *by 10 percent"

.... This is Service Packs 3a, the latest for 2000

SQLSister: yes, the T-log is also set to grow Auto. by 10 percent.

"You may well be doing a task that takes more than 10% growth to accomplish"... well could be, but wouldn't it just continue to increase the size by 10 percent... are you saying it only gets 1 try at it?

Thanks, John

 

[nigelrivett]

Did you try
>> Manually increase the size of #1

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLSister]

John I can't say architectually that it only gets one try, but I do know I've seen other people have a problem like this that was fixed by increasing the percentage growth because it couldn't grow fast enough to keep up with the task.

Just at a guess I'd say you have multiple problems causing database one to be slower than 2. Could be the code is less efficient, could be the indexes are incorrect or need to be updated. Could have a locking problem on one. Could be dozens of different things. Not really enough information to even make an educated guess.

Certainly the 0 room to grow is a concern. I suspect your best bet in this situation is to get a good book on performance tuning. With databases this size, you will need those skills anyway. Two databases over 25 g and only 8 g free on the drive would be a concern to me, too unless the current size is stable. You will be running out of room altogether soon. Only take two more growth cycles to take up your whole drive and that's assuming the transaction logs are on a separate drive or are not growing as well.