We have two database tables
Audit which have both grown fairly large over the last couple of years. So in an effort to reduce the size of the database, I have to write a script to clean out all the rows older than 6 months from these two tables.
This is what I came up with:
ALTER DATABASE StudioWebTest SET RECOVERY SIMPLE; DELETE FROM [Audit] WHERE AuditDate < DATEADD(M, -6, GETDATE()); DELETE FROM [ErrorLog] WHERE ErrorDate < DATEADD(M, -6, GETDATE()); ALTER DATABASE StudioWebTest SET RECOVERY FULL;
On my development machine (SQL Server 2008 R2) with or without changing the recovery mode the database size grows 2-3 times in size. However if I follow this up immediately with a shrink command it cuts the size of database back to half the original size (prior to the record deletion).
However if I hold off on doing the shrink for a few days, the shrink is not nearly as effective, in that while the database size is reduced, it is still almost twice the size of what it was (before deleting the records). Not knowing too much about how SQL Server uses the space it has allocated I assume that this has something to do with it using the extra space that has been freed.
This would all be okay except for one thing. When we run this on our production test environment which uses SQL Server 2005, the shrink command does not reduce the size of the database back to half its original size.
As an alternative I have also tried using
TRUNCATE in place of
DELETE but this doesn't appear to make much difference. The database still grows massively after the statements complete and I still have to shrink it for the same result. We haven't tried this on the production test machine yet, but as the shrink command doesn't seem to be cutting it, it seems doubtful that this will yield any improvement.
Anyway, I was just wondering if someone could explain why the database grows by so much despite changing the error recovery setting? How can this be prevented? Or possibly suggest an alternative means for reducing the size of the database if this is not the best way to go about this.
I was just doing some more tests using
TRUNCATE it doesn't seem to be increasing the size now (maybe I imagined it). I do still need to shrink the database to see a reduction in overall size though. I might try this on the production test server. At the end of the day I think my managers will be happy as long as the size goes down to some degree.