| Author |
Topic |
|
alejo46
Posting Yak Master
157 Posts |
 Posted - 2013-07-21 : 21:00:21
|
| Good evening In a PROduction System the largest tables have for about 500M of rows and moreThe problem arises when we need to debug the oldest data from 1999 to 2006this processes entails a time comsuming time process becasue before deletng the rows from a specficic range i.e from 19960101 to 19960131 w need to backup and deleting the specific rows takes so much time even though i use PK keysAccording to your knowledege adn experience id like to know it its worth an pratical to implement particioning to those tables in SQL 2K and the deleting the partition in order to free up space in FG becuase theyre running out of space?Im not a DBA nor a developer but i wonder if this process needs a manteinance window ?Thnks in advanced |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 01:38:45
|
| partitioning and putting in separate FG would help if you want to do backup separately for the old partition data.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2013-07-22 : 22:11:49
|
| thank you very much, one more question, meanwhile i get documented with partitioning, this process takes less time than debugging |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-23 : 01:05:56
|
quote:
Originally posted by alejo46
thank you very much, one more question, meanwhile i get documented with partitioning, this process takes less time than debugging
sorry i didnt understand your question------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2013-07-23 : 12:57:46
|
| sorry, by mystake i pressed the enter key so the message was not completed, but the question is the partitionig process takes so much time ? what factors should be taken before starting this process ? the purpose of implementing partitioning tables in SQL 2K tables is to minimize the debug process so instead of deleting rows for huge tables i would delete the partition and that would be faster in order to free up some space faster, may you can correct me if im wrong ?ie i´ve got this table with the output sp_spaceused:name|rows|reserved|data|index_size|unusedSALDOS_PREPAGO_DIARIOS_ALTAMIRA_SALDOS|2147483647 |863440848 KB|541344904 KB|233418456 KB|88677488 KBthis has a column called:FEC_SALDO|smalldatetime|no|4| | |no|(n/a)|(n/a)|and the index:index_name|index_description|index_keysIX_FEC_SALDO_SALDOS_PREP_DIA_ALTAMIRA_SALDOS|nonclustered located on INDICES|FEC_SALDOid like to start partioning ths table by month |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-24 : 02:09:31
|
| which column are you partitioning on? is the column the clustered indexed one?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2013-07-24 : 15:15:24
|
| Good afternon replying your question the column is calld: FEC_SALDO and its a nonclusteredindex_name|index_description|index_keysIX_FEC_SALDO_SALDOS_PREP_DIA_ALTAMIRA_SALDOS|nonclustered located on INDICES|FEC_SALDO |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-25 : 00:47:59
|
| how is distribution of data based on the values?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2013-07-25 : 12:07:39
|
| excuse me i didnt understand your question, what do you mean exactly when you say how is distribution of data based on the values?it has something to do with the Filegroup for allocating the table and its indexes ?thanks |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2013-07-25 : 17:01:54
|
| SQL Server 2000 does not support partitioned tables.CODO ERGO SUM |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-26 : 02:09:21
|
quote:
Originally posted by alejo46
excuse me i didnt understand your question, what do you mean exactly when you say how is distribution of data based on the values?it has something to do with the Filegroup for allocating the table and its indexes ?thanks
I was asking on amount of data for each of partitions. Are they in same order of magnitude?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2013-07-26 : 03:56:20
|
| Good evening, this table has 2147483647 records it takes up about 864 GB and datafile for the FG is caleda DATA5 which has 1% free i.e:exec sp_spaceused SALDOS_PREPAGO_DIARIOS_ALTAMIRA_SALDOSname|rows|reserved|data|index_size|unusedSALDOS_PREPAGO_DIARIOS_ALTAMIRA_SALDOS|2147483647 |863440848 KB|541334136 KB|233416904 KB|88689808 KBsp_utilfileinfoDATA5|1|1077417But if SQL Server 2K does not support partitioned tables theres is nothing we can do ? |
|
|
|
|
Pros and Cons to implement partions in SQL 2k tabl