How to Reduce the Table Size...

Question

How to Reduce the Table Size...

Viewing 4 posts - 16 through 18 (of 18 total)

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

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

I believe that one form of "compression" is being overlooked and it's available in all editions of SQL Server... if the Clustered Index has no temporal-like key to match thee order of inserts of the key columns of the Clustered Index are updated on a regular basis, there will be a huge number of page splits. In such cases, simply rebuilding or reorging the Clustered Index (and the non-clustered indexes, as well. It's amazing how much empty space they can contain on systems that have no regular, proper defragmentation jobs going on.

So my first question before any other consideration would be...

Do you have properly written index defragmentation and stats update jobs running? If not, set one up, run it, and then look again.

As for the 20GB size of the table, that's actually pretty small, nowadays. I wouldn't worry about the actual size of the table unless, as I said, it has a huge amount of free space in the table caused by page or extent splits.

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Chowdary's (7/30/2014)
Hi Eirikur Eiriksson,
Pls find the below
1. What is the output of this query?
A:Enterprise Edition (64-bit),3
2. Can you post the CREATE TABLE script for the table?
A.PFA
3. Is this a production server :Yes
Regards
Chowdary....

Few of more questions,

4. How many rows are in the table

5. What is the output of this query? SELECT SUM(DATALENGTH([MEMBERPHOTO])) AS PHOTO_SIZE FROM [dbo].[tblSalesMemberMaster]

6. How is the [MEMBERPHOTO] used?

7. Where does the [MemberNo] value come from?

8. Is the [MemberNo] ever increasing?

Reason for asking is that the [MEMBERPHOTO] (Image) will not compress and Image is also a depreciated data type. An option here might me to move the column into another filegroup, table or filestream, certainly cannot recommend keeping it in the primary filegroup.

😎

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

Hi All,

When am trying to do shrink by using the DBCC command (DBCC SHRINKFILE(DB NAME,50)) am getting the following error

" could not adjust the space allocation for file ibhmLive"

Can anyone help me out....

Regards
Chowdary...

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 4

Sounds like you're trying to make it smaller than you have data in it. You can't do that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning