Post reply

Partioning

  • October 27, 2008 at 10:53 pm

    #127207

    Hi,

    As many of us use Partioning which solves lot of our purpose but I figured out that though its good but it does has limitations boundries may be I am wrong , Just looking for some one who came across a scenario like me did that person solved or wasn't able to ??? wondering

    Issue is

    I have a table which is partioned

    Basically am loading some data from a flat file for every state , and I use that data in some SSIS Package do some crunching on that data by doing some Select,Insert,Update

    For Eg:- I load the data into tTempState table for State 'TX' , will use that data as I mentioned into my SSIS package , and will do select query on that table in my ssis package and will also update the data in that table accrodingly .

    The main issue comes know :-

    I have 4 jobs running on Each server for state (TX,CA,GA,IL) which is doing above process

    If 1 job is running at a time it works fine but for eg

    TX starts and at the same time , When GA job is running so while TX is altering the table to delete the old data which it had in dumptable(tTempState) and proceed further to add new data GA gets locked until TX is done with the below process..and vice versa

    And this has become a big pain becuase as its getting locked the jobs are running behind the schedule .

    So if you guys have came across with any of this situation please do share with me and for other's

    I hope I do get answers.....

    ALTER TABLE tTempState SWITCH

    PARTITION 10 TO tTempState _temp

    Truncate Table dbo.tTempState_temp

    Select from tTempState with(readuncommitted)

    where .................

    update tTempState

    where .............

  • October 28, 2008 at 2:37 am

    #890442

    Please don't cross post. It just wastes people's time and fragments replies.

    No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic592622-145-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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