February 19, 2008 at 10:25 am
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
thanks
SQL_EXPAT
February 19, 2008 at 2:51 pm
not that this helps but I'm glad someone else has encountered this - I did lots of testing and found that I needed the database in exclusive ( single user ) to be able to alter the partition function - not done any serious work with aprtitions since. I was going to use alter database to go single user, make the change and then back to multi user - should be very quick.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply