July 29, 2014 at 10:56 pm
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...
July 30, 2014 at 12:29 am
If you have Enterprise edition, you can enable page compression.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2014 at 1:07 am
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.
😎
July 30, 2014 at 4:56 am
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...
July 30, 2014 at 4:59 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2014 at 5:19 am
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?
😎
July 30, 2014 at 5:37 am
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...
July 30, 2014 at 5:44 am
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...
July 30, 2014 at 5:50 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2014 at 7:27 am
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...
July 30, 2014 at 7:30 am
There is no data mismatch.
The only downside is increased CPU usage (the data doesn't (de)compresses itself), but the advantages usually outweigh this.
The biggest advantage aside from disk savings are the reduced IO and more data that can fit into memory.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2014 at 7:53 am
As an aside, I'd avoid shrinking the data file if at all possible. Shrinking has negative consequences that we try to avoid (like fragmentation).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2014 at 8:12 am
Koen's excellent recommendations on compression are really the only way to reduce the size of data stored other than deleting the data.
Your other options are to try to tweak the data types, using date instead of datetime2 if you don't need the time aspect which is 3bytes for 6-8. Same thing goes for tinyint instead of bigint or int or smallint. But that's really only likely to get you very marginal size decreases.
You can also look to the normalization of the data. Storing four or five columns that should instead be in a lookup table or a many-to-many join can also reduce data size. Again, you may only see marginal decreases.
The two best things to do are to ensure that you're only storing the data you need, and make sure you have a big enough hard drive to store it. If you don't need the data, delete it. If your drive is too small, get a bigger one.
"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
July 30, 2014 at 8:19 am
Grant Fritchey (7/30/2014)
Koen's excellent recommendations on compression are really the only way to reduce the size of data stored other than deleting the data.Your other options are to try to tweak the data types, using date instead of datetime2 if you don't need the time aspect which is 3bytes for 6-8. Same thing goes for tinyint instead of bigint or int or smallint. But that's really only likely to get you very marginal size decreases.
You can also look to the normalization of the data. Storing four or five columns that should instead be in a lookup table or a many-to-many join can also reduce data size. Again, you may only see marginal decreases.
The two best things to do are to ensure that you're only storing the data you need, and make sure you have a big enough hard drive to store it. If you don't need the data, delete it. If your drive is too small, get a bigger one.
I'm going to throw one more datatype thing out there.
The use of GUIDs as your Clustered Index will COST you plenty of space. Changing the clustered index to something like a bigint will save loads of space. You can keep the PK on the GUID if that is your design though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2014 at 8:31 am
SQLRNNR (7/30/2014)
Grant Fritchey (7/30/2014)
I'm going to throw one more datatype thing out there.
The use of GUIDs as your Clustered Index will COST you plenty of space. Changing the clustered index to something like a bigint will save loads of space. You can keep the PK on the GUID if that is your design though.
Absolutely! Plus, not mentioned in your "I want the table to be smaller" is the unstated issue around indexes too. Setting the clustered index to the appropriate column(s) as Jason suggests has a secondary effect. All the nonclustered indexes will be smaller since they will have smaller lookup keys, again saving space.
"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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply