3

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?

|improve this question
  • Simple recovery model – BlueChippy Oct 21 '14 at 4:58
  • Full database backup will not reduce log file size, it just create copy of tail log from log file but don't reduce log file size. If you want reduce size you must execute DBCC SHRINKDATABASE (YouDatabaseName, TRUNCATEONLY); – aasim.abdullah Oct 21 '14 at 5:00
  • As per the question: I don;t expect the file size to reduce, but I WAS expecting the free space to increase? – BlueChippy Oct 21 '14 at 5:13
  • In order to free up space from transaction log, you need to perform a transaction log backup. Or, if you don't need it, switch the database to simple recovery, as it was already suggested. – Roger Wolf Oct 21 '14 at 6:15
  • Read rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log – Remus Rusanu Oct 21 '14 at 6:43
1

Free Space

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.

File Size

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).

|improve this answer
1

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.

|improve this answer
  • This may be more semantics but log file growth is not dependent necessarily on recovery model because a database in simple can still see log growth. The recovery model is more telling SQL Server how you want it to manage log file space. – user507 Oct 21 '14 at 22:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.