1

Is there any recommended way of dealing with growing log files. My log files are 41 gig and primary data files are 77 gig. I only discovered that one of my purge tasks that deletes old data has been failing for over 2 months.

How can I reduce the size of my log files back to normal?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Immortal
  • 1,133
  • 1
  • 15
  • 35
  • https://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log ? – stuartd Aug 07 '17 at 13:24
  • DBCC SHRINKFILE(db_name, 200); this should shrink the log file – sumeet kumar Aug 07 '17 at 13:27
  • 2
    What would you consider "normal", though? If the log file is that size, then it's either because you're not taking log backups with any frequency, or there was at least one query that actually needed that much log space -- possibly even your very own purge task. SQL Server will not grow the log file without reason. In particular, be wary of shrinking the log file only to have it grow again because you don't understand where the growth is coming from. – Jeroen Mostert Aug 07 '17 at 13:31
  • 1
    Log files are there for a reason. Be sure that your backup strategy is in place, and that you have taken a full backup, or a transaction log backup before shrinking! Check this out: https://www.brentozar.com/blitz/transaction-log-larger-than-data-file/ – Søren Kongstad Aug 07 '17 at 13:31

2 Answers2

1

This is because a valid checkpoint has not been reached. Your backup strategy depends on several factors but the reason for a large log file that you cannot shrink is that the database must be in a consistent state.

It has not been told when it can clean up the log file, so consider your large log file as potential changes that have yet to be fully committed to disk.

Log File: Factors that delay log transaction - Docs.Microsoft.com

You can also check out Manage the Size of the Transaction Log File from the Docs as well.

clifton_h
  • 1,298
  • 8
  • 10
-1

I have shrinked the log files and it worked. Please see the code I used below:

/* Use the database */
USE [GiantLogs]
GO
/* Check the name and size of the transaction log file*/
/* The log is fileid=2, and usage says "log only" */
/* Bonus: make sure you do NOT have more than one log file, that does not help performance */
exec sp_helpfile;
GO
/* Shrink the log file */
/* The file size is stated in megabytes*/
DBCC SHRINKFILE (GiantLogs_log, 2048);
GO
/* Check if it worked. It won't always do what you want if the database is active */
/* You may need to wait for a log backup or more activity to get the log to shrink */
exec sp_helpfile;
GO
Immortal
  • 1,133
  • 1
  • 15
  • 35