Recently, I was reviewing a space issue on a server, only to find that the culprit was a 35GB LDF.
It was the OpenManage Essentials database. I’ve been trying to qualify it as a method of detecting hardware issues on ESXi hosts and various Dell hardware (Powervault SAN, etc).
I reviewed the recovery mode and was surprised to find it was already set to Simple.
I ran a backup of the database via SQL Mgmt Studio to see how the log file would respond- it was unchanged.
As I’m not a SQL guru, I did a bit googling on the issue and came across various articles, including this one which outlines things pretty well.
For my scenario, I’m really not concerned with missing transactions in restoring from backup- latest would be fine, so Simple recovery really is a good fit. For Simple recovery, all I really needed to do is run shrinkfile on the log database:
Make sure to select Log for File type.
Clicking OK reduced the size from 35GB to 1GB in a minute or so.
This is obviously not a one-size-fits-all solution for large ldf’s. As mentioned in the referenced link above, If you’re using full recovery mode or can’t break your transaction chain, your approach is different.
