Deleting Rows from Tables in Database 1

[mgallot]

I need to delete rows from a large 6.5 database because the default segment keeps filling up. At this point we are running out of actual hard drive space. My plan is to select the older records by year, save them, and put in an Access database by year. The old records are infrequently used and simple searches on the records are fine. (They are all uploaded to a DB2 database anyway) My question is, after I delete rows from the SQL Server database will SQL Server re-use that that space in the database? Should I run update_stats at that point? I'm thinking of just keeping 6mths worth of data in the database, (we currently have 2 years) in order to make it more manageable. I know that I have to clear the log when deleting, any other tips?

 

[tlbroadbent]

SQL will reuse the space. Backup the database after the update. Updating stats or rebuilding a clustered index, if one exists, are good ideas.

You may want to delete records in batches rather than all in one shot. That way you can truncate the log after each batch before it gets full. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[dbmsguy]

one other thing you could do is a

dbcc showcontig

you can look it up in in BOL and this will help determine if and index or table rebuild is needed, especially on an ongoing basis if you perform periodic archiving.... Paul

http://www.dbmsguy.com