data deletions

[john.livermore]

Quick question about deleting data from SQL Server.

We have a table that gets quite a bit of activity with an attribute of
type text (inserts that store new text entries of 50-200k apiece).
Older rows aren't needed so we have a process that deletes rows more
than 30 days old (using delete statements).

When these rows are deleted, is the memory consumed by these
automatically recovered? Or is there some process that must be
performed to recover the space? What about the transaction log? Does
that grow with each deletion? When do transaction logs get reset?

Thanks,
John

[LLik]

Unless you have transactions logs turned off what you delete will be
recorded in them until you do a backup of the transaction logs. Almost
any change you make to the database will recorded in the transaction
log. If you are not already having problems with the disk filling up
then you probably already are backing up the transaction log.

While a cache may reside in memory it will automaticly be removed when
other queries need the memory. As for disk space yes it will be
reused, however if you need to free the disk space you can do a shrink
of the database, right click on database or table in enterprise
manager. However backup your transaction log before doing this to
really free up more space.

[Erland Sommarskog]

(jo************ @inginix.com) writes:

We have a table that gets quite a bit of activity with an attribute of
type text (inserts that store new text entries of 50-200k apiece).
Older rows aren't needed so we have a process that deletes rows more
than 30 days old (using delete statements).

When these rows are deleted, is the memory consumed by these
automatically recovered? Or is there some process that must be
performed to recover the space?
It depends a little. SQL Server allocates space for a table in extents
of 8*8192 bytes at a time. Such an extend is automatically deallocated
when there is no longer any data in the extent. Thus, if you deletes
are very well aligned with the extents, the space will get reclaimed.
Whether they are aligned depends on the clustered index. If the
clustered index on a column that gorws with each insert, this is likely
to happen. If the clustered index is such that inserts and deletes
happen all over the place, then no extents will be released. However,
in this case space within the extents is likely to be reused by new rows.

The worst case is if you don't have any clustered index. In this case,
the tables grows in one end, but space may not always be reclaimed.

You can check your table for fragmentation with DBCC SHOWCONTIG.

One way to reclaim space is to periodically run DBCC DBREINDEX on
the table. This defragments the table.
What about the transaction log? Does that grow with each deletion?
When do transaction logs get reset?

If you use simple recovery, the transaction log is truncated every
once in a while (about once a minute). It's never truncated past active
transactions though.

If you run with full or bulk-logged recovery, the transaction is truncated
on demand only. It is not specifically not truncated just because you
do a full backup of the database.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp