I have a SQL Server database where the log file is massive and keeps growing every day.
I've archived data from the database and then done a full backup. I was expecting the log to then be basically "empty". e.g. It might be the same physical size, but now have lots of free space. However, it's currently only got 0.1% free.
Any ideas why there is still almost no free space in the log?
Got a SQL database and the log file is massive and keeps growing every day.
Log files growth depends on Recovery Model of a database and transaction log backups. if database is not critical and data loss is not an issue in any case then it should be in SIMPLE recovery mode. Which allows SQL Server to reuse log space when a transaction is committed and log growth remains in limits. But if database is critical and point in time recovery can be a requirement then database must be in FULL Recovery mode. Which is an indication for SQL Server that keep all the log in transaction log file and don't reuse, until it is backed up (transaction log backup).
I've archived data from the database and then done a full backup...I was expecting the log to then be basically "empty". e.g.* Full backup or even Transaction Log backup don't left log file empty. Transaction log backup only mark log file space to reusable but still file size remains same.
If archiving means you have removed some data after fetching some data and keeping it in any other format. Then one must keep in mind that deleting data never reduce physical size of data or log files, though space could be available for reuse by database.
BACKUP DATABASE (full backup, differential backup, file/filegroup backup, copy only) does not free up space in any files. If anything, it uses up a bit of space in the log file to record its actions.
BACKUP LOG truncates the log, which generally does free up space.
A Simple recovery model database truncates its log when a checkpoint happens.
None of the backup operations affect the physical size of the files.
The only commands which do that are DBCC SHINKFILE and DBCC SHRINKDB (in SSMS, right-click a database, choose Tasks, Shrink).
