Managing the Storage

  • Hello!

    I need help?

    I have one database with 7 GB size, and my HDD is 9 GB, and I see that soon the database will grow how to find the solution without changing hardware equipments!

    plz advice, much appreciate!

    Thnx!

    :crying:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Use the procedure below to get the table size, used table and free table in KB. This will allow you to identify bloated tables and reclaim disk space by issuing an sp_spacused 'tableName',@updateusage=true

    CREATE PROCEDURE usp_table_space_general

    @major int=1,

    @minor int=0,

    @db sysname,

    @filegroup sysname = '',

    @table_owner sysname = ''

    AS

    declare @total_db_space_kb decimal(28,0),

    @sql varchar(2000)

    BEGIN

    set nocount on

    /*

    *********************************************************************

    * create worktable

    *********************************************************************

    */

    create table #table_info

    (owner sysname NULL,

    tablename sysname NULL,

    partition_number int NULL,

    file_group nchar(128) NULL,

    table_rows int NULL,

    table_reserved decimal(28,0) NULL,

    table_used decimal(28,0) NULL)

    create table #db_space

    (dbspace decimal(28,0) NULL)

    /*

    *********************************************************************

    * gather base table space info

    *********************************************************************

    */

    select @sql = 'use [' + @db + '] SELECT SCHEMA_NAME(a.schema_id),

    a.name,

    b.partition_number,

    FILEGROUP_NAME(c.data_space_id),

    max(b.rows),

    table_reserved = SUM(c.total_pages),

    table_used = SUM(case c.type when 2 then c.used_pages else c.data_pages end)

    FROM sys.tables a,

    sys.partitions b,

    sys.allocation_units c

    WHERE a.object_id = b.object_id and

    b.partition_id = c.container_id and

    a.type in (''U'',''S'') and

    b.index_id in (0,1,255) '

    if @filegroup = ''

    begin

    select @sql = @sql

    end

    else

    begin

    select @sql = @sql + ' and filegroup_name(c.data_space_id) = ''' + @filegroup + ''''

    end

    if @table_owner = ''

    begin

    select @sql = @sql

    end

    else

    begin

    select @sql = @sql + ' and SCHEMA_NAME(a.schema_id) = ''' + @table_owner + ''''

    end

    select @sql = @sql + ' group by SCHEMA_NAME(a.schema_id),a.name,partition_number,filegroup_name(c.data_space_id)'

    insert into #table_info exec (@sql)

    /*

    *********************************************************************

    * get total space (in kb) of database

    *********************************************************************

    */

    insert into #db_space exec ('use [' + @db + '] select total_space = 1024 * (select sum(convert(decimal(35,2),size)) / convert( float, (1048576 / (select low from master.dbo.spt_values where number = 1 and type = ''E''))) from dbo.sysfiles) - (1024 * (select sum(convert(decimal(35,2),size)) / convert( float, (1048576 / (select low from master.dbo.spt_values where number = 1 and type = ''E''))) from dbo.sysfiles where (status & 0x40)=0x40))' )

    select @total_db_space_kb = dbspace from #db_space

    /*

    *********************************************************************

    * present table space info

    *********************************************************************

    */

    select owner = isnull(owner,'Unknown'),

    tablename,

    partition_number,

    file_group = rtrim(case

    when file_group IS NULL then 'LOG'

    else file_group

    end),

    table_rows,

    table_reserved_kb = sum(convert(decimal(35,2),table_reserved * 8)),

    table_used_kb = sum(convert(decimal(35,2),table_used * 8)),

    table_free_kb = sum(convert(decimal(35,2),table_reserved * 8)) - sum(convert(decimal(35,2),table_used * 8)),

    pct_table_used = convert(decimal(35,2),100 * (sum(convert(decimal(35,2),table_used * 8)))/

    (sum(case(table_reserved) when 0 then 1 else convert(decimal(35,2),table_reserved) end * 8))),

    pct_of_database = convert(decimal(35,2),100 * (sum(convert(decimal(35,2),table_reserved * 8)) / @total_db_space_kb))

    from #table_info

    group by owner, tablename, partition_number, file_group,table_rows

    order by 1,2,3

    drop table #table_info

    drop table #db_space

    RETURN(0)

    END

  • What's the solution you want? this makes no sense. If you need the data, you need more disk space. If you can delete data, do that.

  • I agree with you Steve ....hmmm I know that if I delete data I will win more space but these data is important!

    and no possible to change the HDD! Do we have any solution to export data in txt files and if I need them backing up on DB!

    also for me is stupid situation but I'm asking to solve it! And I don't know how can I do!?

    ok if we dont have idea I will accept as impossible ( and I will sugest to add more HDD)!

    Tommy I will try you script but little bit confused! :ermm:

    thnx very much Steve!

    Dugi

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Do you have only one HDD? If you have more HDD you could try creating more filegroups.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply