| Author |
Topic |
|
fazle_rabbee
Yak Posting Veteran
63 Posts |
 Posted - 2009-01-13 : 01:41:23
|
| I am now working with a size of 68 gb database, replicated with 8 subscriber. I do wanna see every tbale history (total row, total size of the table etc. )I want to decrease the table size if it is large. most of the large tables are indexed. What should I do to find out the large table history and decrease the large table.fazle rabbee |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
fazle_rabbee
Yak Posting Veteran
63 Posts |
Posted - 2009-01-13 : 01:59:11
|
| Dear Sir,This query is for whole database. I wanna know the query for a single table. And I want decrease the table size without deleting a single row of a table. This is emergency. Please help me.fazle rabbee |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2009-01-13 : 02:26:05
|
decrease the size of the table without deleting any rows. that's tricky!
elsasoft.org |
|
|
|
fazle_rabbee
Yak Posting Veteran
63 Posts |
Posted - 2009-01-13 : 03:57:01
|
| I wanna decrease the unused space of a table. How can it be possible?fazle rabbee |
|
|
|
fazle_rabbee
Yak Posting Veteran
63 Posts |
Posted - 2009-01-13 : 04:00:04
|
| is it possible to dcrease index size?fazle rabbee |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-01-13 : 10:03:29
|
quote:
Originally posted by fazle_rabbee
Dear Sir,This query is for whole database. I wanna know the query for a single table. And I want decrease the table size without deleting a single row of a table. This is emergency. Please help me.fazle rabbee
You cannot decrease the table size without deleting data or dropping columns, nor the index size for that matter. What is the issue that you're facing? How can one table be an emergency? I'm a little suspicious about what your motives are.Terry-- Procrastinate now! |
|
|
|
fazle_rabbee
Yak Posting Veteran
63 Posts |
Posted - 2009-01-14 : 00:13:27
|
| The issue is that The database that I am handling is 68 gb and only 25 gb space is left. There are tables, for example named, 'ImportinDetail' and 'SalesDetail' which have the data size of 10 to 12 gb. these table are indexed. thats why the size is large. If it is possible to decrease the data size of the table then a good portion of space can be gained.fazle rabbee |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-01-14 : 12:39:36
|
| Is the 25Gb the space remaining on the disk or free within the database? If it's the space free in thee database but you have plenty of available disk, is the database set to auto-grow? Anyway, you have 33% free, which sounds more than enough to me for the time being (depends on YOUR environment). And, again as stated, you cannot decrease the size of the table without deleting data.Terry-- Procrastinate now! |
|
|
|
fazle_rabbee
Yak Posting Veteran
63 Posts |
Posted - 2009-01-17 : 03:55:02
|
| Thanks for the advice. I have another database wich is showing 50 gb in size, But the actual size is 10 gb. What should I do right now? Please help me.fazle rabbee |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-17 : 10:43:10
|
quote:
Originally posted by jezemine
decrease the size of the table without deleting any rows. that's tricky!
quote:
Originally posted by tosscrosby
You cannot decrease the table size without deleting data or dropping columns, nor the index size for that matter
You can change the size of a table by using smaller datatypes, if appropriate.If you only need full dates, and are not interested in time, you can alter DATETIME to SMALLDATETIME of your dates is older than the year of 2076.That will cut the size that column in half, a 4 byte savings per record. For a 50 million record table, that is about 191 mb saved (less spaced used).Same applies to other datatypes. If you store Month number for example, and use INT, you can alter that to TINYINT instead.That will drop the column size by 75%, from 4 bytes to 1 byte per record. For a 50 million record table, that is about 143 mb saved (less spaced used).
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
About decreasing table size