CODEWITHSUNDEEP
sql-serverlogfile
Carmax
Carmax

Reputation: 2917

Why does my SQL Server logfile have 99% space available after giving me a full warning?

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?

Upvotes: 1

Views: 3335

Answers (1)

dfundako
dfundako

Reputation: 8314

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

Upvotes: 2

Related Questions