Post reply

Disk Usage by Top Tables

  • March 10, 2014 at 12:40 am

    #304917

    Hi All

    I have a query to resolve/understand how it all works. I have a table with 363 287 records, with Reserved(KB) 228 652 048, Data(KB) 228 592 480, Indexes(KB) 47 400 and Unused(KB) 12 168. Why so much space allocated to such a small number of records? As a test in UAT i had to delete some close records using the below script after I restored the db

    delete from Request_Items where requestid in (select requestid from requests where reqClosed=1)

    after the delete then I ran the DBCC SHRINKDATABASE (mydb,10) the data file shrank from 256GB to 17GB. Why so much masive space to one table and how do i tackle the issue. Does

    Thanks

    It's better to fail while trying, rather than fail without trying!!!

  • March 10, 2014 at 12:57 am

    #1695416

    Maybe you have a lot of columns? Hard to guess without the table DDL.

    How to tackle the issue? Do you have row/page compression on the table (Enterprise edition if I'm not mistaken)?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 10, 2014 at 1:17 am

    #1695421

    Thanks for your response. No row or page compression

    It's better to fail while trying, rather than fail without trying!!!

  • March 10, 2014 at 2:14 am

    #1695437

    smthembu (3/10/2014)

    Thanks for your response. No row or page compression

    you have two columns in this table

    1. [Value] [varchar](max) NULL

    2. [File] [varbinary](max) NULL

    looks like a contain which holds the uploaded file if not mistaken. i understand the File column

    but why you have a Value column of Varchar(Max) ?

  • March 10, 2014 at 2:16 am

    #1695438

    twin.devil (3/10/2014)

    smthembu (3/10/2014)

    Thanks for your response. No row or page compression

    you have two columns in this table

    1. [Value] [varchar](max) NULL

    2. [File] [varbinary](max) NULL

    looks like a contain which holds the uploaded file if not mistaken. i understand the File column

    but why you have a Value column of Varchar(Max) ?

    Maybe it is an option to store the file outside the table with the FILESTREAM option.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 10, 2014 at 8:24 am

    #1695545

    smthembu (3/10/2014)

    Hi All

    I have a query to resolve/understand how it all works. I have a table with 363 287 records, with Reserved(KB) 228 652 048, Data(KB) 228 592 480, Indexes(KB) 47 400 and Unused(KB) 12 168. Why so much space allocated to such a small number of records? As a test in UAT i had to delete some close records using the below script after I restored the db

    delete from Request_Items where requestid in (select requestid from requests where reqClosed=1)

    after the delete then I ran the DBCC SHRINKDATABASE (mydb,10) the data file shrank from 256GB to 17GB. Why so much masive space to one table and how do i tackle the issue. Does

    Thanks

    Deletes don't return space to the system. Rebuilding indexes does.

    Also, be advised that DBCC SHRINKDATABASE causes massive framentation of all indexes and that you should at least REORGANIZE them all after such a shrink.

    --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)

  • March 11, 2014 at 12:01 am

    #1695811

    The unfortunate part thats how the found the table created. Thanks for mentioning the filestream option as the app stores the documents on the db and i did advise the vendor to use the filestream should help in storage tons of documents.

    It's better to fail while trying, rather than fail without trying!!!

Viewing 7 posts - 1 through 6 (of 6 total)

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