Thank you for your thoughts. Always appreciated.
Let me see if I have understood you correctly.
I am concerned with the databases running out of space.
For tables with no cl ix - this is not a problem, as SQL server will reuse
pages. ( A problem for me as most of the tables have cl ix )
For those with a cl ix, it will of course insert the data in the
correct sequence. - Remember that I said I wasn't involved in database
design etc, a lot of our v large tables have a clustered index on a PK
consisting of an Identity column ( not my fault, but my problem ;-) ). This
means that new data will allways be appended at the end of the object at the
next page. ( Right ? ). Will there be a scenario when the database reports
say ) 500Mb free, but one 100byte insert will fail as there is now space
left at the 'end' of the database?
I think I am going to have to do away with cl ix (it is a OLTP system )
and copy in / out the tables to reduce fragmentation. ( At one site, on our
large tables ( 3m+ rows ) Scan density was around 18% and I reduced dbcc
checkdb by one hour by rebuilding the clustered index and resorting the
Have you ever carried out any archiving processes in the past to reclaim
>For tables without cl ix:
>SQL Server will allocate new pages "at the end" of the tables linked list.
>SQL Server will try to get a page "near" the end-page. It can go
>if free pages are found earlier in the page chain (due to earlier
>delete/update). But an "earlier" page is not re-used for insert until it is
>empty. End-result is that you will have potentially a lot pf free space
>unless your "purging" is (kind of) massive. This is a non-issue in 7, btw
>(no linked list).
>For tables with cl ix:
>Rows go where they belong, according to the clustering key. Due to
>page--splits (updates in "the middle"), you can get a fragmented table.
>If SQL Server is performing scans, fragmentation can hurt you. Read ahead
>will be less effective as well.
>Do not trust EM free space reporting. I'd use sp_spaceused (or write my
>possibly based on sp_spaceused). Make sure that you DBCC UPDATEUSAGE if you
>want "correct" free space info.
>Free space reporting is _not_ based on last page. It sums the number of
>pages for each table from the sysindexes table and compares that to the
>total db size.
>Use DBCC SHOWCONTIG is you want to check fragmentation issues.
>MCDBA, MCSE, MCSD, MCT, SQL Server MVP
>Cornerstone Sweden AB
>Please reply to the newsgroup only, not by email.
>> Can any one give me some background info on some thing I am ashamed
>> admit to not knowing?
>> I an developing a archive / purge system in TSQL to remove aged data
>> from a rapidly expanding databases.
>> When I delete rows from tables, will SQL allocate any free extents
>> automatically when a new one is required or will it allocate from the
>> object onwards? ( i.e. do I need to reload / reindex the tables
>> I am aware of issues due to fragmentation of data / indexes and of
>> need to regularly update statistics - is there anything else I should
>> I was not involved in the db design, and clustered indexes are used
>> where I wouldn't. Any issues there?
>> I presume that SEM reports available space from the last object in
>> db. Is this correct?
>> The sites ( about 85 of them ) run close to 24 * 7. Any windows of
>> opportunity are limited to a few hours in the early hours of the morning.
>> any thing other than routine maintenance needs to happen infrequently and
>> speedily. I have starting having some success rebuilding clustered
>> and resorting the data, but the best that this can be done is one
>> (substantial ) table / night.
>> Any information on this would be gratefully received.
>> Many thanks,
>> Dominic Young.