Thanks for a great article Gail. I have a question relating to this area for my first forum post, hopefully it is not too system specific. Please bear with me while I try to explain the situation.
I have seen issues at 2 client sites running SQL 2000 where several tables in the db are showing large areas of unused space (we are talking 1 - 3Gb per table here) but will not release this space when shrinking the db. I recently read an article I cannot find now that claimed this could happen when all the indexes on a table were heaps.
The tables in question are part of an OLTP application and these tables indeed do not have a clustered index. The article claimed creating a clustered index and then dropping it would reorganise the data such that a subsequent shrinkfile would be able to release the space. I tried this and it worked!
My questions are:
Have you, on anyone on the forum, seen this issue?
Is it SQL 2000 specific?
If the table(s) in question are of the type that have order number and possibly order line values, is leaving a clustered index on the table likely to cause issues?
Will leaving a clustered index on the table solve the excess space problem noted above?
Again apologies if this is far too complex a query for the forum but I thought it worth asking!