0

In a sql server database with a full model backup with tran logs backed up every 15 minutes, but ,still, the log file is huge. The backups (once a day) of the db and the tran log (every 15 minutes) are regular the log space is almost empty ... then is it good to shrink it a little?

logspacee logspace2 vlf logspace

  • Hi Gabriele, It depends, have you tried shrinking the file and has it grown back again? What maintenance tasks have you got running? Was there a time when you did not have any logbackups running? What is the size of your biggest Index? – Stijn Wynants Nov 6 '18 at 10:30
  • ye, i've tried DBCC SHRINKFILE (db_log, 8000) WITH NO_INFOMSGS with no result, the log is still 10 GB, the backups are once a day full db backup, and the tr log is backed up every 15 minutes – Gabriele D'Onufrio Nov 6 '18 at 10:39
1

Transactional log files keeps log records for operations on database. It is expanding when necessary by claiming new pages from OS. However, Log file doesn't shrink automatically. It will stay 10GB if you don't shrink it manually.

When log backups performed, the data on log file is marked as reusable and you can see log usage from

  • SSMS: Double Click on Database-> Tasks-> Shrink->File and choose Log File.
  • DBCC SQLPERF(logspace) command.

If your log usage is below 50 percent and log file size is big such as 10GB you may consider shrinking it. However, Shrinking is always a bit tricky in my opinion. You can claim back the space but what is the point of taking pages back if OS is going to grant it again? For instance, If batch insert is performed last night and your log file is 10GB suddenly, you can shrink it back. On the other hand, if this operation runs every night, there is no reason to shrink log file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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