Database Partitioning - Partition switching

Hi,

I need replies immediately...

This is with regard to switching of partitions in a sliding window scenario.

I wrote a stored procedure which does the partition switching in a sliding window scenario. During one switching, if the stored procedure is half executed, if I re-execute the same procedure it throws errors like

'Filegroup is in a different partition switching failed'

So how to rollback all the transactions in the stored procedure that does switching of partitions? If it is not possible, then how to identify whether a file group is in its original partition before the stored procedure is part executed? How to bring it back to its original partition before executing the stored procedure again switching?

Can any one help me immediately...

Thanks

Sekharpc

Moving thread to appropriate forum.|||

hi sek,

you have to enclose your sp scripts in a begin transaction.... end transaction clause..

create sp swtichpartion

(

)

as

begin transaction

partition switch... code1

partition switch code 2.

partition switch code 3.

commit transaction

in this way all switch task must commit as a unit. a failure in one job

fails the entire operation.

for a more comprehensive solution you can also incorporate the 2005

error handling feature try.. catch block for more information

you can consult my blogs look for the topic

The SQL SERVER 2005 Try Catch Block look for sub topic

"try catch with transaction"

or BOL

regards,

joey

database partition

Hello,
My question is about database partition. I know how to define a filegroup,
Partition Function, Partition scheme and do a partion on brand new table.
But my question is how can I partition a existing table without droping it!!
Thanks for help
Hooman"Microsoft" <Hooman@.ebatts.com> wrote in message
news:uP7HJXatGHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hello,
> My question is about database partition. I know how to define a
> filegroup,
> Partition Function, Partition scheme and do a partion on brand new table.
> But my question is how can I partition a existing table without droping
> it!!
>
You move it to the partions scheme, just like you would move it to a
different filegroup.
Something Like
create unique clustered index pk_MyTable
on MyTable(id) with drop_existing
on MyPartitionScheme(id)
David

Database partition

What is best criteria to partition table?
Table size or number of records?
How many partition we can do?
If I have 20 GB size of table how many tables of how much size should i
create?
Regards
Amish ShahThe best criterion is to partition when you need to provide a solution to a
PROBLEM and the best solution is PARTITIONING.
So the question is.... do you have a PROBLEM that needs solving?
What is your PROBLEM?
Is PARTITIONING the best SOLUTION for your PROBLEM?
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"amish" <shahamishm@.gmail.com> wrote in message
news:1129544966.925091.296410@.g43g2000cwa.googlegroups.com...
> What is best criteria to partition table?
> Table size or number of records?
> How many partition we can do?
> If I have 20 GB size of table how many tables of how much size should i
> create?
>
> Regards
> Amish Shah
>|||Suppose I have table of 20GB how much partition should i create? What
should be size of each partition?
Or
If I have table of 2.5 billion rows how much partition should be
created? How much rows should be in each partition?
Regards|||Amish,
I think what Nik was pointing out is that you are asking for a solution
without describing a problem. Size alone is not enough criteria to determine
proper partitioning. How can your data be segregated? The answer to
partitioning is not <x> rows = partition. The view your build will need to
determine which table(s) from the partition to query. If you do not
partition on a meaningful field, then you will hurt your performance rather
than improve it.
Provide more specifics and I am sure someone here can help you out. Since
you mention the number of rows, I will assume you are looking for horizontal
partitioning, so in the meantime read up on horizontal partitioning in BOL.
John
"amish" wrote:
> Suppose I have table of 20GB how much partition should i create? What
> should be size of each partition?
> Or
> If I have table of 2.5 billion rows how much partition should be
> created? How much rows should be in each partition?
> Regards
>|||Hi John.
Correct. Solutions are for problems. You've not described yours at all. Will
you really have 20 billion rows? If so how big are they, 1 single ID column
and an integer would still mean you may not need partitioning as all the
data could fit onto a normal (or several) medium sized disks.
Paritioning is not the only solution for large or high use databases. You
could split the data into 2 tables each with the same key. One table has a
few columns but is very frequently accessed. In this case the packing
density of rows will be high and a lot of data accesses willl get pages from
buffers alreay in memory. The other table could contain infrequently used
data.
I've seen many people come unstuck because they've embraced something which
sounds "high-tech" or "is the current buzzword", developed their solution
and it doesn't provide what they wanted because they didn't define the
problem properly in the first place.
Please explain your current problem and what you're trying to achieve (with
and without partitioning) and we'll all be able to better help you arrive at
the best solution.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AA57FE51-EFEA-4D74-8499-4F58535D92EA@.microsoft.com...
> Amish,
> I think what Nik was pointing out is that you are asking for a solution
> without describing a problem. Size alone is not enough criteria to
> determine
> proper partitioning. How can your data be segregated? The answer to
> partitioning is not <x> rows = partition. The view your build will need
> to
> determine which table(s) from the partition to query. If you do not
> partition on a meaningful field, then you will hurt your performance
> rather
> than improve it.
> Provide more specifics and I am sure someone here can help you out. Since
> you mention the number of rows, I will assume you are looking for
> horizontal
> partitioning, so in the meantime read up on horizontal partitioning in
> BOL.
> John
> "amish" wrote:
>> Suppose I have table of 20GB how much partition should i create? What
>> should be size of each partition?
>> Or
>> If I have table of 2.5 billion rows how much partition should be
>> created? How much rows should be in each partition?
>> Regards
>>|||Hi
Thanks for the reply
Actually I have joined a new company.
Here we have some tables of 20 gb to 50 gb in size.
So I just want to know should I partition this tables?
I think if I will partition it, reindex and other management activity
will be easier for me.
I want to partition this tables so I just want to find on which
criteria and how many partition should I create for this tables.
Regards|||Amish,
Again, the answer is not based on size. When you partition you are NOT
doing it to reduce the administrative workload. in fact, you probably
increase it. You are doing it for performance reasons. And the data has to
be partitioned based on criteria.
So for example your company has 5 sales regions and they are comprised of 10
states each. you could partition the data into 5 tables, each representing
the sales data for a specific refion. This might make sense since in most
cases the queries are going to be for a specific region. You see what we
mean when we say there has to be some logical criteria for the parition.
You cannot just say. 2billion / 10 = 200 million rows per table. All you
have done with this partition is forced the creation of a view that will
ALWAYS have to join ALL the tables. No benefit there at all. And how do you
decide which table to insert into? It needs to be based on some business
logic, not just pulled out of thin air. Stop looking at the size of the
tables and start looking at the logic for partitioning.
John Scragg
"amish" wrote:
> Hi
> Thanks for the reply
> Actually I have joined a new company.
> Here we have some tables of 20 gb to 50 gb in size.
> So I just want to know should I partition this tables?
> I think if I will partition it, reindex and other management activity
> will be easier for me.
> I want to partition this tables so I just want to find on which
> criteria and how many partition should I create for this tables.
> Regards
>

Database partition

What is best criteria to partition table?
Table size or number of records?
How many partition we can do?
If I have 20 GB size of table how many tables of how much size should i
create?
Regards
Amish Shah
The best criterion is to partition when you need to provide a solution to a
PROBLEM and the best solution is PARTITIONING.
So the question is.... do you have a PROBLEM that needs solving?
What is your PROBLEM?
Is PARTITIONING the best SOLUTION for your PROBLEM?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"amish" <shahamishm@.gmail.com> wrote in message
news:1129544966.925091.296410@.g43g2000cwa.googlegr oups.com...
> What is best criteria to partition table?
> Table size or number of records?
> How many partition we can do?
> If I have 20 GB size of table how many tables of how much size should i
> create?
>
> Regards
> Amish Shah
>
|||Suppose I have table of 20GB how much partition should i create? What
should be size of each partition?
Or
If I have table of 2.5 billion rows how much partition should be
created? How much rows should be in each partition?
Regards
|||Amish,
I think what Nik was pointing out is that you are asking for a solution
without describing a problem. Size alone is not enough criteria to determine
proper partitioning. How can your data be segregated? The answer to
partitioning is not <x> rows = partition. The view your build will need to
determine which table(s) from the partition to query. If you do not
partition on a meaningful field, then you will hurt your performance rather
than improve it.
Provide more specifics and I am sure someone here can help you out. Since
you mention the number of rows, I will assume you are looking for horizontal
partitioning, so in the meantime read up on horizontal partitioning in BOL.
John
"amish" wrote:

> Suppose I have table of 20GB how much partition should i create? What
> should be size of each partition?
> Or
> If I have table of 2.5 billion rows how much partition should be
> created? How much rows should be in each partition?
> Regards
>
|||Hi John.
Correct. Solutions are for problems. You've not described yours at all. Will
you really have 20 billion rows? If so how big are they, 1 single ID column
and an integer would still mean you may not need partitioning as all the
data could fit onto a normal (or several) medium sized disks.
Paritioning is not the only solution for large or high use databases. You
could split the data into 2 tables each with the same key. One table has a
few columns but is very frequently accessed. In this case the packing
density of rows will be high and a lot of data accesses willl get pages from
buffers alreay in memory. The other table could contain infrequently used
data.
I've seen many people come unstuck because they've embraced something which
sounds "high-tech" or "is the current buzzword", developed their solution
and it doesn't provide what they wanted because they didn't define the
problem properly in the first place.
Please explain your current problem and what you're trying to achieve (with
and without partitioning) and we'll all be able to better help you arrive at
the best solution.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AA57FE51-EFEA-4D74-8499-4F58535D92EA@.microsoft.com...[vbcol=seagreen]
> Amish,
> I think what Nik was pointing out is that you are asking for a solution
> without describing a problem. Size alone is not enough criteria to
> determine
> proper partitioning. How can your data be segregated? The answer to
> partitioning is not <x> rows = partition. The view your build will need
> to
> determine which table(s) from the partition to query. If you do not
> partition on a meaningful field, then you will hurt your performance
> rather
> than improve it.
> Provide more specifics and I am sure someone here can help you out. Since
> you mention the number of rows, I will assume you are looking for
> horizontal
> partitioning, so in the meantime read up on horizontal partitioning in
> BOL.
> John
> "amish" wrote:
|||Hi
Thanks for the reply
Actually I have joined a new company.
Here we have some tables of 20 gb to 50 gb in size.
So I just want to know should I partition this tables?
I think if I will partition it, reindex and other management activity
will be easier for me.
I want to partition this tables so I just want to find on which
criteria and how many partition should I create for this tables.
Regards
|||Amish,
Again, the answer is not based on size. When you partition you are NOT
doing it to reduce the administrative workload. in fact, you probably
increase it. You are doing it for performance reasons. And the data has to
be partitioned based on criteria.
So for example your company has 5 sales regions and they are comprised of 10
states each. you could partition the data into 5 tables, each representing
the sales data for a specific refion. This might make sense since in most
cases the queries are going to be for a specific region. You see what we
mean when we say there has to be some logical criteria for the parition.
You cannot just say. 2billion / 10 = 200 million rows per table. All you
have done with this partition is forced the creation of a view that will
ALWAYS have to join ALL the tables. No benefit there at all. And how do you
decide which table to insert into? It needs to be based on some business
logic, not just pulled out of thin air. Stop looking at the size of the
tables and start looking at the logic for partitioning.
John Scragg
"amish" wrote:

> Hi
> Thanks for the reply
> Actually I have joined a new company.
> Here we have some tables of 20 gb to 50 gb in size.
> So I just want to know should I partition this tables?
> I think if I will partition it, reindex and other management activity
> will be easier for me.
> I want to partition this tables so I just want to find on which
> criteria and how many partition should I create for this tables.
> Regards
>

database partition

Hello,
My question is about database partition. I know how to define a filegroup,
Partition Function, Partition scheme and do a partion on brand new table.
But my question is how can I partition a existing table without droping it!!
Thanks for help
Hooman"Microsoft" <Hooman@.ebatts.com> wrote in message
news:uP7HJXatGHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hello,
> My question is about database partition. I know how to define a
> filegroup,
> Partition Function, Partition scheme and do a partion on brand new table.
> But my question is how can I partition a existing table without droping
> it!!
>
You move it to the partions scheme, just like you would move it to a
different filegroup.
Something Like
create unique clustered index pk_MyTable
on MyTable(id) with drop_existing
on MyPartitionScheme(id)
David

Database partition

What is best criteria to partition table?
Table size or number of records?
How many partition we can do?
If I have 20 GB size of table how many tables of how much size should i
create?
Regards
Amish ShahThe best criterion is to partition when you need to provide a solution to a
PROBLEM and the best solution is PARTITIONING.
So the question is.... do you have a PROBLEM that needs solving?
What is your PROBLEM?
Is PARTITIONING the best SOLUTION for your PROBLEM?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"amish" <shahamishm@.gmail.com> wrote in message
news:1129544966.925091.296410@.g43g2000cwa.googlegroups.com...
> What is best criteria to partition table?
> Table size or number of records?
> How many partition we can do?
> If I have 20 GB size of table how many tables of how much size should i
> create?
>
> Regards
> Amish Shah
>|||Suppose I have table of 20GB how much partition should i create? What
should be size of each partition?
Or
If I have table of 2.5 billion rows how much partition should be
created? How much rows should be in each partition?
Regards|||Amish,
I think what Nik was pointing out is that you are asking for a solution
without describing a problem. Size alone is not enough criteria to determin
e
proper partitioning. How can your data be segregated? The answer to
partitioning is not <x> rows = partition. The view your build will need to
determine which table(s) from the partition to query. If you do not
partition on a meaningful field, then you will hurt your performance rather
than improve it.
Provide more specifics and I am sure someone here can help you out. Since
you mention the number of rows, I will assume you are looking for horizontal
partitioning, so in the meantime read up on horizontal partitioning in BOL.
John
"amish" wrote:

> Suppose I have table of 20GB how much partition should i create? What
> should be size of each partition?
> Or
> If I have table of 2.5 billion rows how much partition should be
> created? How much rows should be in each partition?
> Regards
>|||Hi John.
Correct. Solutions are for problems. You've not described yours at all. Will
you really have 20 billion rows? If so how big are they, 1 single ID column
and an integer would still mean you may not need partitioning as all the
data could fit onto a normal (or several) medium sized disks.
Paritioning is not the only solution for large or high use databases. You
could split the data into 2 tables each with the same key. One table has a
few columns but is very frequently accessed. In this case the packing
density of rows will be high and a lot of data accesses willl get pages from
buffers alreay in memory. The other table could contain infrequently used
data.
I've seen many people come unstuck because they've embraced something which
sounds "high-tech" or "is the current buzzword", developed their solution
and it doesn't provide what they wanted because they didn't define the
problem properly in the first place.
Please explain your current problem and what you're trying to achieve (with
and without partitioning) and we'll all be able to better help you arrive at
the best solution.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AA57FE51-EFEA-4D74-8499-4F58535D92EA@.microsoft.com...[vbcol=seagreen]
> Amish,
> I think what Nik was pointing out is that you are asking for a solution
> without describing a problem. Size alone is not enough criteria to
> determine
> proper partitioning. How can your data be segregated? The answer to
> partitioning is not <x> rows = partition. The view your build will need
> to
> determine which table(s) from the partition to query. If you do not
> partition on a meaningful field, then you will hurt your performance
> rather
> than improve it.
> Provide more specifics and I am sure someone here can help you out. Since
> you mention the number of rows, I will assume you are looking for
> horizontal
> partitioning, so in the meantime read up on horizontal partitioning in
> BOL.
> John
> "amish" wrote:
>|||Hi
Thanks for the reply
Actually I have joined a new company.
Here we have some tables of 20 gb to 50 gb in size.
So I just want to know should I partition this tables?
I think if I will partition it, reindex and other management activity
will be easier for me.
I want to partition this tables so I just want to find on which
criteria and how many partition should I create for this tables.
Regards|||Amish,
Again, the answer is not based on size. When you partition you are NOT
doing it to reduce the administrative workload. in fact, you probably
increase it. You are doing it for performance reasons. And the data has to
be partitioned based on criteria.
So for example your company has 5 sales regions and they are comprised of 10
states each. you could partition the data into 5 tables, each representing
the sales data for a specific refion. This might make sense since in most
cases the queries are going to be for a specific region. You see what we
mean when we say there has to be some logical criteria for the parition.
You cannot just say. 2billion / 10 = 200 million rows per table. All you
have done with this partition is forced the creation of a view that will
ALWAYS have to join ALL the tables. No benefit there at all. And how do yo
u
decide which table to insert into? It needs to be based on some business
logic, not just pulled out of thin air. Stop looking at the size of the
tables and start looking at the logic for partitioning.
John Scragg
"amish" wrote:

> Hi
> Thanks for the reply
> Actually I have joined a new company.
> Here we have some tables of 20 gb to 50 gb in size.
> So I just want to know should I partition this tables?
> I think if I will partition it, reindex and other management activity
> will be easier for me.
> I want to partition this tables so I just want to find on which
> criteria and how many partition should I create for this tables.
> Regards
>