Post reply

Single Table Size is increasing....

  • November 30, 2014 at 9:32 pm

    #292974

    Hi everyone,

    We are having the issue with DB size,

    for every 3 months we are getting the same issue.

    The thing is only one table getting increasing the size.

    On 26th the db size was 3 gb and today it got increased 33.833 GB.

    So how can i check the exact problem and how can i resolve...

    PFA....

    Regards
    Chowdary...

  • November 30, 2014 at 9:52 pm

    #1761589

    Chowdary's (11/30/2014)

    Hi everyone,

    We are having the issue with DB size,

    for every 3 months we are getting the same issue.

    The thing is only one table getting increasing the size.

    On 26th the db size was 3 gb and today it got increased 33.833 GB.

    So how can i check the exact problem and how can i resolve...

    PFA....

    IMHO...

    Step 1 would be to post the CREATE TABLE statement, including ALL of the indexes and any triggers that may be included.

    Step 2 would be to run the following code with the database the table is in as the "current" database in SSMS...

    SELECT *

    FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('PutTableNameHere'),NULL,NULL,'DETAILED')

    ;

    ... and provide that output.

    Step 3 would be to identify how often and what is modified after any row is initially inserted.

    Then we'll go from there.

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

  • November 30, 2014 at 10:30 pm

    #1761594

    Hi Mr.Jeff Moden thank you for ur response..

    1. &2. PFA..

    3.After 26 We inserted 155 rows of data,For that it is taking 30GB..

    If I want to check which column is occupying more space in table..

    Regards
    Chowdary...

  • November 30, 2014 at 10:57 pm

    #1761595

    If you check the results from the script I gave, there are 3,832,619 pages of LOB data present. If we multiply that times the page size of 8,192, we get 31,396,814,848 or roughly 31GB (29.24 GB using typical binary math) which is about the same size as your table. That means that the MEMBERPHOTO column is the culprit because it's the only BLOB column in the table.

    I'd consider pre-processing the MEMBERPHOTO to limit the size of the data there.

    You should also be advised that the IMAGE data-type is deprecated and needs to be changed to a VARBINARY(MAX) data-type.

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

  • November 30, 2014 at 11:04 pm

    #1761596

    P.S. That table has a fair number of columns that you might want to add indexes to. With that thought in mind, I strongly recommend that you do a "vertical" partition on the table where that nasty MemberPhoto column and a duplication of the MemberID column are made to reside in a separate table. Consider adding a "Date_Added" column to that "sister" table, as well, to support possible partitioning efforts in the future to reduce the huge load on backups and possible restores that table will represent in the future.

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

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

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