The job we have in place to manage our partitions is continuosly blocked by
other processes accessing the partitioned table/s. The job is being blocked
by different SPIDs every second but never gets the chance to ALTER the
partition function. The only way for the ALTER PARTITION FUNCTION to run
through is for us to shutdown all the applications.
Is there a transaction isolation level or way that we can write the ALTER
PARTITION FUNCTION code so that it can get the table/schema LOCK it requires to
alter the PARTITION FUNCTION ? We do not want to stop all the applications
every time we run this. We would rather the applications got blocked for a
few seconds and NOT the ALTER PARTITION FUNCTION statement
thank you
cranfield