Sometimes you need to clean and shrink SQL database, since once database grows even if all records get deleted, the database file would still take the space as before clean up. To achieve this we can manually delete records and call SHRINKFILE command. However, it’s important to delete records in small batches in transaction not to clog up the database server.
First of all, let’s delete records in small batches of 500 (number can be adjusted). As an example we will use ASP.NET Session State database which contains millions of expired session records. The script below could be also set as a recurring SQL job to always keep database clean.
Once the database is cleaned up, we need to shrink the file to free up some space.