Post reply

database size clash

  • January 20, 2010 at 10:39 am

    #137508

    Hello Gurus

    I have database in prod whose size is 21GB approx and unused space is 4GB. So, the actual database size is 16GB Approx. When I looked at Disk Usage by Top Tables in SSMS I see the Total reserved space in KB as 9350832 KB (~ 9.3GB). Again, using the following code, I checked the data size of the Database. which summed to 5.6GB Approx.

    USE AdventureWorks

    GO

    CREATE TABLE #temp (

    table_name sysname ,

    row_count INT,

    reserved_size VARCHAR(50),

    data_size VARCHAR(50),

    index_size VARCHAR(50),

    unused_size VARCHAR(50))

    SET NOCOUNT ON

    INSERT #temp

    EXEC sp_msforeachtable 'sp_spaceused ''?'''

    SELECT a.table_name,

    a.row_count,

    COUNT(*) AS col_count,

    a.data_size

    FROM #temp a

    INNER JOIN information_schema.columns b

    ON a.table_name collate database_default

    = b.table_name collate database_default

    GROUP BY a.table_name, a.row_count, a.data_size

    ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

    DROP TABLE #temp

    May be I am looking at three different things? I am not sure. If I am looking at three different things please advice me on what is the difference.

    Thank you very much

    Ali

  • January 20, 2010 at 11:07 am

    #1106220

    I'm wondering if you are comparing apples to oranges.

    How are you determining the first values (21 GB, 4 GB).

    How are you determining the second value ~ 9 GB.

    I see how you are getting the third set, but does that include the size of the indexes on the tables?

    Does some of the values you are looking at also include the space used by and filled in the transaction log?

  • January 20, 2010 at 11:20 am

    #1106226

    How are you determining the first values (21 GB, 4 GB).

    SSMS right click on database--> Properties

    How are you determining the second value ~ 9 GB.

    Right click on the DB --> Reports --> Standard Report --> Disk Usage by Top Tables

    I am sure the above two methods include the index spaces. By the way, if this information is useful, we have only couple of views and good no.of SP's. But still I dont think this will quantify the difference.

    Thanks

    Ali

  • January 20, 2010 at 1:06 pm

    #1106289

    Can you run the query below and see the sizes then...

    DBCC updateusage(myDB)

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • January 20, 2010 at 2:16 pm

    #1106357

    This query is giving out lot of data.. is there any way that I can consolidate this whole information??

    Thanks

    Ali

  • January 20, 2010 at 9:48 pm

    #1106469

    Use DBCC UPDATEUSAGE with no output and then run sp_SpaceUsed... or, there's a parameter that you can add to sp_SpaceUsed that will do it for you. It's in Books Online.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 20, 2010 at 10:45 pm

    #1106492

    SSMS right click on database--> Properties

    This value reflects both the size of your data file(s) and the size of the log file(s)

    You can see the breakdown between the two using the Disk Usage report.

    Right click on the DB --> Reports --> Standard Report --> Disk Usage by Top Tables

    I am sure the above two methods include the index spaces. By the way, if this information is useful, we have only couple of views and good no.of SP's. But still I dont think this will quantify the difference.

    You are right, both methods include the index spaces. The 5.6 GB sum from the query in your initial post does not include indexes. I think the majority of the space discrepency is from the log space.

    Use DBCC SQLPERF('logspace') to see if the Log Size (MB) for the database covers that gap you see.

  • January 20, 2010 at 10:56 pm

    #1106498

    SQLPirate is correct on the space info. Run the query he suggested. You will likely see the discrepancy there.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 21, 2010 at 8:39 am

    #1106744

    The query answered my question 😀

    Thanks Guys. This website is being very helpful for me. Even though, I am new to sql, I am able to cope up in my job place because of the articles and forums in this website...

    Thanks to you all!!!

    Ali

  • January 21, 2010 at 9:04 am

    #1106773

    You're welcome

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 9 (of 9 total)

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