November 16, 2002 at 10:33 am
We're trying to rearrange a rather large database and do some cleanup. Some things we very much anticipated doing in parallel are not working. Is there anything I could have done to make these work?
Example #1: Two filegroups, one file each, doing a DBCC SHRINKFILE on each at the same time, one blocks the other. Why?
Example #2: Doing a CREATE INDEX which converted a table to having a clustered index. The table and all indexes for it are stored in three filegroups (call them X, Y, Z). Why can we not do a SHRINKFILE on some other file Q that's not in any of the filegroups being affected with CREATE INDEX. Actually in this case an amazing amount of stuff won't work -- I can't even run anything useful in Enterprise Manager, it's all blocked (and there are almost 500,000 locks presnet in SYSLOCKS with no other activity - though it's hanging pretty constant now at about 3 hours into a planned 8 hour run; doesn't SQL ever escalate to file or filegroup locks?)
This is SQL Server 2000 standard on a quad W2K system. I know I won't get parallel create-index performance, but I really expected to be doing more than one at a time in whol diferent filegroups, shrinking some, creating indexes, etc. all at the same time, but it's really holding things up.
Should I have started out different? Was there some approach or hint or transaction setting that might have caused less interaction? Or is there some funnel somewhere in a system table that gets locked for all these things and won't let others through?
November 16, 2002 at 11:33 am
OK, I answered one of my own questions, and it's kind of obvious. The two SHRINKFILE's are blocking because it appears the SHRINKFILE needs to update something for a table(s) that is in the file being shrunk, OR for the index being shrunk. I had assumed there was some less physical pointed to in the indexes, but I guess idnexes point to something physical that has to be updated everytime a data page is moved.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply