0

I Recently upgraded from sql server 2008 to 2019 and have noticed SQL would start to freeze up and crash a few times a day. I narrowed it down to a shrink logs job I added to sql Agent. I have been using this shrink log job for years in sql server 2008 and never experienced this issue. This is The Job that runs every 30 minutes:


GO  

-- Truncate the log by changing the database recovery model to SIMPLE. 

ALTER DATABASE MyDB  

SET RECOVERY SIMPLE;  

GO  

-- Shrink the truncated log file to 1 MB.  

DBCC SHRINKFILE (Places_log, 1);  

GO  

-- Reset the database recovery model.  

ALTER DATABASE MyDB 

SET RECOVERY FULL; 

GO

Is there a better way I can do this? Honestly I do not need the Log files at all but ive been told there was no safe way to disable log files completely and the only way to remove them is to automate shrinking them as they come in.

Thom A
  • 88,727
  • 11
  • 45
  • 75
kivi12k
  • 13
  • 5
  • If you are in full recovery mode just back up your log file like you're supposed to. – Stu Apr 14 '23 at 16:05
  • 2
    Why are you shrinking the logs in the first place? Are you *not* doing regular transaction log backups? IF you're not, why are you not just putting the database in simple recovery mode and leaving it in that? This smells like an [XY Problem](//xyproblem.info). – Thom A Apr 14 '23 at 16:06

1 Answers1

0

Simply leave the database in SIMPLE recovery.

ALTER DATABASE MyDB SET RECOVERY SIMPLE;

The log will be truncated after each checkpoint, and the space re-used. There is no need to ever shrink the log file, unless you have a one-time transaction that made it grow beyond it's normal high-water mark.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67