October 24, 2007 at 5:12 am
Hi
SQL 2000 SP4 on Windows 2000 SP4
Primary DB size @200Mb, autogrow set to 100% (recently changed this due to hitting 0Mb space available previosuly). In future I'll manually increase size out of hours.
The autogrow triggered, the DB file increased to 400Mb, however Enterprise Manager still showed the DB size @200Mb and space available 0Mb.
I manually changed the database size to 250Mb, now EM shows Size 250Mb space available 0Mb.
We have another 10 DB's on this system that don't seem to have this problem but maybe they've just not hit there autogrow threshold yet.
Does anyone have any thoughts why it still says 0Mb....?
Regards
Tony
October 24, 2007 at 5:25 am
Does you Databases shows as 'SUSPECT' mode when u view from Entrprise Mgr. If so, this is due to space constraint issue of a server. Remove the old files/junk into a another drive so that the server automatically deducts the disk space and databases will become operational similiar with that of other databases.
October 24, 2007 at 5:33 am
Hi
All databases shown are NOT suspect, there is 7Gb availkable on the drive and no restrictions on the size the DB can be.
Tony
October 24, 2007 at 5:52 am
Then try to attempt Shrink the databases even the system/Default DB. Hope this could solve the issue.
October 24, 2007 at 6:32 am
Sometimes SQL Server gets mixed up with the amount of space reserved for a table but not used - unused space.
This is a script I found on this site which will tell you how much space is allocated to each table within a database:
create table #table_size(name nvarchar(60),
rows int,
reserved_kb varchar(18),
data_kb varchar(18),
index_kb varchar(18),
unused_kb varchar(18) )
insert #table_size exec sp_MsForeachTable 'exec sp_spaceused ''?''--,true'
-- "uncomment" --,true above to run updateusage
select
name,
rows,
convert(int,replace(reserved_kb,' KB','')) as reserved_kb,
convert(int,replace(data_kb,' KB','')) as data_kb,
convert(int,replace(index_kb,' KB','')) as index_kb,
convert(int,replace(unused_kb,' KB','')) as unused_kb
from
#table_size
order by
name
--rows desc
-- reserved_kb desc
drop table #table_size
Run the script and have a look at the reserved_kb column - you will find a table (or maybe more) with a large amount of unused space.
You can then either uncomment the TRUE option in the above script to force SQL Server to recalculate the amount of space or just run exec sp_spaceused tablename, true for the specific table.
Jez
October 24, 2007 at 6:37 am
DO NOT Shrink if you can avoid it. This causes indexing fragmentation.
Or if you must shrink, rebuild your indexes.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 24, 2007 at 6:49 am
Anyother thing could be the stats or usage information need to be refreshed. Try sp_updatestats and DBCC UPDATEUSAGE.
October 24, 2007 at 7:18 am
Hi
Thanks everyone for your comments....the solution came from Antares686 🙂
I tried - sp_updatestats - (problem still remained)
DBCC UPDATEUSAGE (0) - (I then tried this command (0) runs against current DB)) - problem resolved 🙂
Thanks
Tony
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply