Decrease the size of Table - 25 GB
-
Dear
I have a Production ULTIMUS DB of 30 GB size running 24/7. Out of 30 GB i have one table(TASKS) of size 25 GB.It has only 124584 records.Purged lot of records,shrinked the DB but the size is not decreasing.I want to reduce the size of the table.What is the best way to do this.I am new to SQL server.
Table Name Rows-CntReserved SpaceData Space Combined Index SpaceUnused Space
TASKS 12458425844736 KB25597544 KB235640 KB11552 KB
Any help is appreciated.
Regards
manoj
-
- put a clustering index on that table ! (as tight a possible)
(or rebuild its clix)
Keep in mind, by setting a clustered index, it will need to rewrite the whole table ! Make sure you have the needed space !
Read BOL !
Play it safe and start with a full backup !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you have a text field, your table won't decrease no matter what you do. The easiest way to get a nice, tight table is to just copy it to another table and then rename.
so...
select * into table2 from table1.
rename table1 to table1OLD.
rename table 2 to table1.
now you're good... then you can drop table1.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
If you have purged a lot of data then you will have a lot of partially filled pages. By creating (as ALZDBA states) or rebuilding the clustered index you will get some of that space back. Examining sys.dm_db_index_physical_stats or DBCC Showcontig will show you the extent to which your table is fragmented. You did not specify if your table is a heap or has a clustered index but either way your indexes need to be rebuilt after a large number of rows are deleted. This will improve the overall performance on the table and will reclaim some of the space used.
-
Also try and establish what datatypes are being used on the table
That few records versus that amount of space....
Possibly a few of the char fields could be converted to varchar when you rebuild the table.
But first things first..... That clustered index.... (oops, first backup, then that clustered index)
-
Hi,
As pduplessis mentioned, you may be able to reduce the table size by modifying the data types of the fields in the table. If you have any decimal fields in the table, you may be able to save space by converting your decimal fields to vardecimal. This option is available in SQL Server 2005 SP2 and later. Before enabling this option, you can get an estimate of how much space can be gained by running the following system stored procedure:
exec sys.sp_estimated_rowsize_reduction_for_vardecimal 'YourTable'Before you can enable vardecimal for the table you must enable this for the database:
exec sp_db_vardecimal_storage_format 'YourDatabase', 'ON'Note that this does NOT automatically enable vardecimal for all tables in the database. You must enable it on a per table basis. To do this run the following command:
exec sp_TableOption 'YourTable', 'Vardecimal Storage Format', 1To check and see what tables have vardecimal enabled, you can run the following query:
Select
b.name + '.' + a.name as ObjectName,
a.type_desc
From
sys.objects a,
sys.schemas b
Where
a.schema_id = b.schema_id
and objectproperty(a.object_id, 'TableHasVarDecimalStorageFormat') = 1
Because turning on this feature places a lock on the database, this option should be done OFFLINE. Also note that only 1 user session can be active in order to turn on vardecimal. You can read more information about it here:
http://msdn.microsoft.com/en-us/library/bb326755.aspx
Bob Pinella
-
Good input Bob.
We to often forget this new feature of sp2.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks ALZDBA - These forums have helped me a great deal in the past, so it's nice to give back once in a while!
Bob Pinella
-
Post the DDL for the table, so we can look and not guess.
Yes, the databatypes can definitely be a factor. If the column is using the UNICODE datatypes then you're using twice the space.
-
Steve I am new to this forum and to ms sql. When you said post the DDL for the table, can that be done by right clicking the table and then script table as Create To?
-
Yes. You'll want to check: tools--options--sql server object explorer--scripting
Make sure you're scripting the index's and keys.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply