1

While deleting a large number of records, I get this error:

The transaction log for database 'databasename' is full

I found this answer very helpful, it recommends:

  1. Right-click your database in SQL Server Manager, and check the Options page.
  2. Switch Recovery Model from Full to Simple
  3. Right-click the database again. Select Tasks Shrink, Files Shrink the log file to a proper size (I generally stick to 20-25% of the size of the data files)
  4. Switch back to Full Recovery Model
  5. Take a full database backup straight away

Question: in step 3, when I go to shrink > files and choose log from the file type dropdown menu, it tells me that 99% of the allocated space is free.

Out of ~4500MB of allocated space, there is ~4400MB free (the data file size is ~3000MB).

Does that mean I'm good to go, and there is no need to shrink?

I don't understand this. Why would that be the case, given the warning I received initially?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Martin Hansen Lennox
  • 2,837
  • 2
  • 23
  • 64

1 Answers1

2

I'm not one for hyperbole, but there are literally billions of articles written about SQL Server transaction logs.

Reader's digest version: if you delete 1,000,000 rows at a time, the logs are going to get large because it is writing those 1,000,000 deletes in case it has to roll back the transaction. The space needed to hold those records does not get released until the transaction commits. If your logs are not big enough to hold 1,000,000 deletes, the log will get filled, throw that error you saw, and rollback the whole transaction. Then all that space will most likely get released. Now you have a big log with lots of free space.

You probably hit a limit on your log file at 4.5gb and it wont get any bigger. To avoid filling your logs in the future, chunk down your transactions to smaller amounts, like deleting 1,000 records at a time. A shrink operation will reduce the physical size of the file, like from 4.5gb down to 1gb.

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

dfundako
  • 8,022
  • 3
  • 18
  • 34
  • I've never had to deal with them before so the RD version is very helpful to me! Thank you. So the 'full log' warning is a result of attempting a single very large transaction (as opposed to something that accumulates over time / numerious transactions)? – Martin Hansen Lennox Oct 24 '18 at 22:13
  • Yep. Try running the deletes in a loop or with a delete top 10000 and commit between each transaction. During a big delete, run DBCC SQLPERF(LOGSPACE) and watch the log fill up. – dfundako Oct 24 '18 at 22:18
  • Got it. Ta for taking the time to explain, I'd have lost many hours otherwise. – Martin Hansen Lennox Oct 24 '18 at 22:31