3

I have done a delete operation on a table with many records, so my log file grew from 15 GB to 58 GB. At some point I canceled the execution of delete query because my hdd was running out of space. I have seen here how to delete this big amount of data. My question is how could I reduce the size of log file back to 15 GB?

Ionut
  • 724
  • 2
  • 9
  • 25
  • What is the recovery model of your database? – Stu Mar 30 '21 at 10:59
  • Use [DBCC SHRINKFILE](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15#shrinking-a-log-file). – Dan Guzman Mar 30 '21 at 11:00
  • @Stu, the recovery model is Full. – Ionut Mar 30 '21 at 11:08
  • Are you backing up the transaction log? In full recovery the VLF pages are not reused until the log is backed up. If you back up the log it will very likely shrink. This all depends on any current transactions, it is shrunk only to the point of the current active VLF. – Stu Mar 30 '21 at 13:38

1 Answers1

3

If you use a full recovery model, then you are essentially preserving all the history of your database in your logs.

Most likely you want to switch to simple recovery model, followed by DBCC SHRINKFILE as others have suggested.

See here: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15

jurez
  • 4,436
  • 2
  • 12
  • 20
  • So if I switch to simple recovery model and then DBCC SHRINKFILE, the size of log file should go back to 15 GB? I have to figure it out how to switch to simple recovery model. LE: I have switched to simple recovery model – Ionut Mar 30 '21 at 11:17