SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Pros and Cons to implement partions in SQL 2k tabl
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
149 Posts

Posted - 07/21/2013 :  21:00:21  Show Profile  Reply with Quote
Good evening
In a PROduction System the largest tables have for about 500M of rows and more
The problem arises when we need to debug the oldest data from 1999 to 2006

this 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 keys

According 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

India
52325 Posts

Posted - 07/22/2013 :  01:38:45  Show Profile  Reply with Quote
partitioning and putting in separate FG would help if you want to do backup separately for the old partition data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

alejo46
Posting Yak Master

Colombia
149 Posts

Posted - 07/22/2013 :  22:11:49  Show Profile  Reply with Quote
thank you very much, one more question, meanwhile i get documented with partitioning, this process takes less time than debugging
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/23/2013 :  01:05:56  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

alejo46
Posting Yak Master

Colombia
149 Posts

Posted - 07/23/2013 :  12:57:46  Show Profile  Reply with Quote
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|unused
SALDOS_PREPAGO_DIARIOS_ALTAMIRA_SALDOS|2147483647 |863440848 KB|541344904 KB|233418456 KB|88677488 KB
this has a column called:
FEC_SALDO|smalldatetime|no|4| | |no|(n/a)|(n/a)|
and the index:
index_name|index_description|index_keys
IX_FEC_SALDO_SALDOS_PREP_DIA_ALTAMIRA_SALDOS|nonclustered located on INDICES|FEC_SALDO
id like to start partioning ths table by month

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/24/2013 :  02:09:31  Show Profile  Reply with Quote
which column are you partitioning on? is the column the clustered indexed one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

alejo46
Posting Yak Master

Colombia
149 Posts

Posted - 07/24/2013 :  15:15:24  Show Profile  Reply with Quote
Good afternon
replying your question the column is calld: FEC_SALDO and its a nonclustered

index_name|index_description|index_keys
IX_FEC_SALDO_SALDOS_PREP_DIA_ALTAMIRA_SALDOS|nonclustered located on INDICES|FEC_SALDO
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/25/2013 :  00:47:59  Show Profile  Reply with Quote
how is distribution of data based on the values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

alejo46
Posting Yak Master

Colombia
149 Posts

Posted - 07/25/2013 :  12:07:39  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/25/2013 :  17:01:54  Show Profile  Reply with Quote
SQL Server 2000 does not support partitioned tables.



CODO ERGO SUM
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/26/2013 :  02:09:21  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

alejo46
Posting Yak Master

Colombia
149 Posts

Posted - 07/26/2013 :  03:56:20  Show Profile  Reply with Quote
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_SALDOS
name|rows|reserved|data|index_size|unused
SALDOS_PREPAGO_DIARIOS_ALTAMIRA_SALDOS|2147483647 |863440848 KB|541334136 KB|233416904 KB|88689808 KB

sp_utilfileinfo
DATA5|1|1077417

But if SQL Server 2K does not support partitioned tables theres is nothing we can do ?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000