Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to Reduce the Table Size... Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 10:56 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:56 AM
Points: 168, Visits: 538
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...
Post #1597572
Posted Wednesday, July 30, 2014 12:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1597586
Posted Wednesday, July 30, 2014 1:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,533, Visits: 7,101
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.
Post #1597596
Posted Wednesday, July 30, 2014 4:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:56 AM
Points: 168, Visits: 538
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...
Post #1597642
Posted Wednesday, July 30, 2014 4:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1597643
Posted Wednesday, July 30, 2014 5:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,533, Visits: 7,101
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?



Post #1597652
Posted Wednesday, July 30, 2014 5:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:56 AM
Points: 168, Visits: 538
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...


  Post Attachments 
table_script.txt (13 views, 1.62 KB)
Post #1597662
Posted Wednesday, July 30, 2014 5:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:56 AM
Points: 168, Visits: 538
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...
Post #1597665
Posted Wednesday, July 30, 2014 5:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1597667
Posted Wednesday, July 30, 2014 7:27 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:56 AM
Points: 168, Visits: 538
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...
Post #1597719
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse