Home Forums SQL Server 2005 Administering Database Size is shwoing Much larger then Actual Data RE: Database Size is shwoing Much larger then Actual Data

  • Users say a lot of things...

    A few questions to help you sort this out:

    1) Do you track your DB growth? Do you have any idea how fast this DB is growing?

    2) What is the Recovery_model? If FULL are you doing transaction log backups? You have a 60GB DB with a 12GB transaction log that seams kind of high.

    To get an idea of what tables/indexes are taking up the most space you could run this:

    with cte as

    (

    SELECT

    t.name as TableName,

    SUM (s.used_page_count) as used_pages_count,

    SUM (CASE

    WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE lob_used_page_count + row_overflow_used_page_count

    END

    ) as pages

    FROM sys.dm_db_partition_stats AS s

    JOIN sys.tables AS t ON s.object_id = t.object_id

    JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id

    GROUP BY t.name

    )

    select

    cte.TableName,

    cast((cte.pages)/1024 as decimal(10,3)) as TableSizeInMB,

    cast(((CASE

    WHEN cte.used_pages_count > cte.pages THEN cte.used_pages_count - cte.pages

    ELSE 0

    END) * 8./1024) as decimal(10,3)) as IndexSizeInMB

    from cte

    order by used_pages_count desc

    That should help you get an idea of where the growth is occurring.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001