I am using a MSSQL database and deleted old data from my EventSentry database. Even though I deleted a significant amount of data from my database, space used by the database remains high and I am unable to free up disk space. I am also unable to shrink the database transaction log file.

Article ID: 151
Category: Database
Created: 2007-09-13

If the recovery model is set to "Full", then Microsoft SQL Server will record transactions, including removed data, in the transaction log for recovery purposes.

If you do not need the transaction log for recovery purposes, then you can change the database recovery model from "Full" to "Simple". You can do this with the following SQL command:

ALTER DATABASE EventSentry SET RECOVERY SIMPLE

You should be able to shrink the transaction log after making this change. If you would like to keep the recovery model at "High" then you can also execute the following SQL statement to remove all committed transactions from the transaction log:

BACKUP LOG EventSentry WITH TRUNCATE_ONLY

Keep in mind though that the transaction log will most likely fill up again in the future if it is not being backed up.