SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Reduce the Table Size...


How to Reduce the Table Size...

Author
Message
Chowdary's
Chowdary's
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 566
Hi All,
Am using 2008 Sql server.

In my database one of the table containing 20gb of data.I need to check that table means what are the data updated and is there any unused space.
>How can i reduce the Tbl space
>Am trying to do Shrink my mdf file but i can able to do only 2mb at a time and there is no enough space in my drive.
>Is there any command to reduce the Mdf size..
Kindly give me any resolution..

Regards
Chowdary..

Regards
Chowdary...
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27645 Visits: 13268
If you have Enterprise edition, you can enable page compression.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15310 Visits: 18612
Chowdary's (7/29/2014)
Hi All,
Am using 2008 Sql server.

In my database one of the table containing 20gb of data.I need to check that table means what are the data updated and is there any unused space.
>How can i reduce the Tbl space
>Am trying to do Shrink my mdf file but i can able to do only 2mb at a time and there is no enough space in my drive.
>Is there any command to reduce the Mdf size..
Kindly give me any resolution..

Regards
Chowdary..


The simplest thing for the table size is compression as Koen mentioned, but regardless of how you reduce the table size, that's not going to reduce the datafile size. There are few options but they all depend on the environment, available maintenance windows, server load etc.
Cool
Chowdary's
Chowdary's
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 566
Hi Eirikur Eiriksson thank you for response,

How can i claim my space from that table without doing Compress,
If i compress row level or Page level is there any issues like performance level or any other..
Can you pls give me some suggestions..

Regards
Chowdary....

Regards
Chowdary...
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27645 Visits: 13268
Chowdary's (7/30/2014)
Dear Eirikur Eiriksson,

How can i claim my space from that table without doing Compress, Can you pls give me some suggestions..

Regards
Chowdary....


Why is compression not an option? Because of SQL Server edition?

Another way to reduce size - aside from the obvious: deleting data - is to choose your column data types wisely.
Every byte saved counts.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15310 Visits: 18612
Koen Verbeeck (7/30/2014)
Chowdary's (7/30/2014)
Dear Eirikur Eiriksson,

How can i claim my space from that table without doing Compress, Can you pls give me some suggestions..

Regards
Chowdary....


Why is compression not an option? Because of SQL Server edition?

Another way to reduce size - aside from the obvious: deleting data - is to choose your column data types wisely.
Every byte saved counts.


Quick questions

1. What is the output of this query?
SELECT SERVERPROPERTY('Edition'),SELECT SERVERPROPERTY('EngineEdition')


2. Can you post the CREATE TABLE script for the table?
3. Is this a production server?
Cool
Chowdary's
Chowdary's
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 566
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....

Regards
Chowdary...
Attachments
table_script.txt (19 views, 1.00 KB)
Chowdary's
Chowdary's
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 566
Koen Verbeeck (7/30/2014)

Why is compression not an option? Because of SQL Server edition?

Another way to reduce size - aside from the obvious: deleting data - is to choose your column data types wisely.
Every byte saved counts.

--------------
Hi Koen Thank you for response,
Am not sure abt that Compression that's y am getting tens..
If i do compress the table ,whether that free space can added to the database file or not.If no there is no use of doing that as per y requirement ,If yes we are happy with that.because there is less available space.

Regards
Chowdary....

Regards
Chowdary...
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27645 Visits: 13268
You said your table is about 20GB. Suppose your data file is 50GB.
After page compression it is likely your table has a size of about 12GB. So you gain 8GB.
However, the data file will still be 50GB. If you actually want to gain those 8GB, you'll need to shrink the data file after you compressed the table.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Chowdary's
Chowdary's
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 566
Hi Koen Verbeeck,
Thank you so much for the detailed Reply.
But i have a question ,If i do the compression is there any issue like data mismatch or performance...etc..
is it recommended to do continuously...

Regards
Chowdary...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search