CODEWITHSUNDEEP
sqlsql-servert-sqlsql-delete
ArielD
ArielD

Reputation: 1

SQL Server - Table cleanup not working

I am working on a large database (32 Gb) containing application and services logs and traces going back a few years that I want to trim down to only a month's worth of data.

We already have a stored proc and job that can be run to do so, with variable retention, but when I ran it for the past day it has not worked. I've also tried a straight delete statement

delete from [INFRAICC 2.0].[css].[DatabaseTrace] where time < DATEADD(day, -30, GETDATE())

but I am getting the same results:

The transaction log fills up, but the database itself does not get any smaller. I suspect possibly an access issue (though it's writing the log fine) but I cannot find any way to confirm this, or if there is any other possible reason.

Upvotes: 0

Views: 249

Answers (1)

Russell Fox
Russell Fox

Reputation: 5435

SQL Server will reserve space on the drive and will not release that when data is deleted unless you tell it to. It just keeps it reserved with the expectation that you'll need it again. Read up on DBCC SHRINKDATABASE and review the recommendations - you don't want to over-shrink it because there's a performance hit when it has to go and reserve more space, so you don't want it doing that all the time.

Upvotes: 1

Related Questions