0

I use the maintenance plan of SQL Server, but it does not work well.

Transaction log truncation flow in the maintenance plan:

① Database consistency check task
↓
② Database compression task
↓
③ Database backup task
(Transaction log backup)
↓
④ Truncate transaction log area (execute T-SQL)

If run the maintenance plan manually in the manual server, it will successfully terminate the transaction log area and reduce the size.

The size is not reduced while the transaction log area is truncated, even though the schedule is successfully terminated.

Is there any problem with the execution of the maintenance plan?

USE [XXX system _SQL] 
GO

DBCC SHRINKFILE (XXX system _SQL_log, TRUNCATEONLY) 
GO
2

If you need to shrink your log file often, it means that the size it has after a shrink is not sufficient for your everyday activity, so it grows again and again.

This means that you do unnecessary work when shrink it, and, that is worse, every time your log needs to expand, it wastes time zeroing out every new piece of file it adds (and your users WAIT for the completion of this zeroing out).

You should stop this double waste of time and eliminate log shrinking from your maintenance plan. If you are not satisfied with the size of your log, shrink it once and then make your log backups more frequently.

  • Agreed. Or, another way to put it: The size that your log files grows to is part of the cost you pay for each database. If it grow to, say 10 GB, then you need to get budget for those 10GB for that log file, since that is a part of the cost of this application/database. – Tibor Karaszi Jan 15 at 10:57
  • Thank U for ur answer @sepupic @Tibor Karaszi. I try to shrink it once and then make log backups more frequently. so your means if shrink log file, the database have performance issues? – tomi Jan 17 at 1:00
  • Shrink it as a one-time operation is no problem. What you don't want to do is regular shrinks, so it later has to grow again. That would hurt performance. – Tibor Karaszi Jan 17 at 9:45
  • After the shrink log is executed manually, the logfile will become smaller. Automatic execution of logfile is not change. Would you know caues? – tomi Jan 18 at 2:38
  • Are you sure that your "automatic shrink" uses the same command? Try to launch it and capture the actual command using Profiler – sepupic Jan 18 at 7:03

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.