Post reply

Database space available 0Mb...autogrow was set...manual increase still gives 0Mb?

  • October 24, 2007 at 5:12 am

    #70261

    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

    #744883

    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

    #744885

    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

    #744892

    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

    #744914

    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

    #744916

    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

    #744924

    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

    #744942

    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