Partitioning problem

  • Hi all,

    our DBA has left us, and now I am [ as non-DBA 😉 ] facing a little problem with the un-doing of partitioning on a DB. I'm trying to understand the scripts our former DBA has left for us, and I think I know what the problem is, but I don't know how to fix it.

    A DB has been partitioned, and that is working fine. One of the commands that has been used to do the partitioning, is this one:

    create partition function part_afspdlnemer(varchar(18))

    as

    range left for values('390830000034958093', '394480000035028281', '398140000035071500', '401790000035095140',

    '405440000035133312', '409090000035176187', '412750000035216312')

    go

    create partition scheme sch_afspdlnemer

    as partition part_afspdlnemer

    to (FGgee_year2006, FGgee_year2007, FGgee_year2008, FGgee_year2009, FGgee_year2010, FGgee_year2011, FGgee_year2012, [primary]);

    go

    which created the partitioning function and the scheme [this of course wasn't the only statement used, but it's the problem one 😉 ]

    Now, when trying to undo the partitioning, using first the clustered index statement:

    create clustered index clusid on afspdlnemer(ID);

    and then move back to primary file statement:

    drop index clusid on afspdlnemer with (move to [primary]);

    I get the following output:

    Msg 7726, Level 16, State 1, Line 1

    Partition column 'ID' has data type varchar(36) which is different from the partition function 'part_afspdlnemer' parameter data type varchar(18).

    So, I checked several things, and I found that this partitioning function has been created at a time when the column 'ID' on this table had a varchar of (18). And after partitioning the DB, the application manufacturer who uses this DB, has updated the DB when applying one of its application updates. In this update, the varchar setting for the 'ID' column has been adjusted from (18) to (36).

    So, I now need to know if, and how, I can change the partitioning scheme so it matches again with the 'ID' column. Assuming that that will fix the problem I'm now facing when trying to go back to a single file...

    I'm guessing that changing the 'ID' column back to varchar(18) in the DB, undoing the partitioning, and then changing the 'ID' column back to varchar(36) might be possible, but isn't the way to go 🙂

    BTW, of course these are not the only commands used to (un)partition the DB, but it's the one from the scripts our former DBA left that's not working 😉

    All help is greatly appreciated!

    Thanx,

    Tinus

    P.S.: SQL2005 UK Enterprise SP1 running on W2K3 UK Enterprise SP2 active/passive cluster. In case this is relevant.

  • just a ping back http://www.sqlservercentral.com/Forums/Topic510666-1291-1.aspx

    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, I noticed that also, as I stated in my last post in that topic. However, this question is a new (but related) problem, not the same as the other topic

  • Attached you'll find what I tested as a result of your request 😉

    My dev edtn is on SQL2005 SP2 + CU9 ( 9.00.3282 ) so its results may be

    different from your system.

    Test it at your test server and check the results.

    Keep in mind, this operation will need primary-datafile space as well as log file space. So prepare your operation.

    Know the concept of a clustering index (i.e. physical storage row order).

    Apparently the drop of the clustering index with move to primary, disables the partitioning function for the table, but does not "disconnect" it from the table.

    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

  • Many thanx for the reply. I'll ask my co-worker, who already knows more than I do about SQL 😀 if he can get the problem fixed again. We'll let you know of course...

    Regs,

    Tinus

  • Hello all,

    time to kick this topic. Due to a number of reasons, I haven't been able to test ALZDBA's script before today. Just so you know, I've already apologized to him in a PM.

    Anyway, the script does work partially. The output is attached (renamed to txt). If anyone does have suggestions how to move forward from here, I'm very gratefull. Especially since I am the one with the most SQL knowledge at the moment. Which is, quite honestly, no so much. But I'm learning fast.

    BTW, it turns out that there are 3 partitioning scripts that used varchar(18) but are now on varchar(36). And the SQL2005 environment now is a (new build from scratch) W2K3 R2 Enterprise cluster with SQL2005 at SP3 level (as a result of one of the above mentioned reasons).

  • (11 row(s) affected) (94 row(s) affected) (8 row(s) affected) (94 row(s) affected)

    Msg 7726, Level 16, State 1, Line 38 Partition column 'RgValue' has data type varchar(36) which is different from the partition function 'part_afspdlnemer' parameter data type varchar(18).

    This is intended in the script, to repro your situation.

    Msg 7717, Level 16, State 1, Line 7 The partition scheme "sch_afspdlnemer" is currently being used to partition one or more tables.

    This indicates your partition scheme "sch_afspdlnemer" is used by more than one table.

    In the script the only Table_1 uses that partition scheme.

    Hence you should also "unpartition" the other tables that use this partition scheme.

    This is performed in the script by using:

    drop index clusid on dbo.Table_1 with (move to [primary]);

    (Note the WITH MOVE TO PRIMARY ! So all data is actually being moved to the given filegroup !

    Prepare that size !!)

    Msg 2714, Level 16, State 58, Line 1 There is already an object named 'sch_afspdlnemer' in the database. Msg 7726, Level 16, State 1, Line 1 Partition column 'RgValue' has data type varchar(36) which is different from the partition function 'part_afspdlnemer' parameter data type varchar(18).

    This scheme could not be dropped (see previous message !), so it alreaddy exists.

    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

  • Well, it has been a while again, but...

    it worked! 😀

    Based on the scripts provided here by ALZDBA, the application manufacturer managed to build a total script that changed the running partitioning schemes to match the adjusted varchar settings. The scripts have been applied to the production database this weekend and after two working days everything still seems OK.

    So many thanks to ALZDBA for his work!

    And I'm very glad this topic now can be closed 😛

  • Thank you for the feedback.

    HTH

    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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply