Single Table Size is increasing....

Question

Single Table Size is increasing....

Chowdary's

SSCrazy

Points: 2978
More actions
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...

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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)

Chowdary's SSCrazy Points: 2978 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

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)