However, why can I not shrink the file in full recovery mode?
It's working as designed. You just need to understand more about what is going on, so you can decide which is the appropriate action to take.
That, or you're just trying to shrink it at the wrong time (see my note #7 below).
First of all, "log file" is a bit of a misnomer: SQL uses the transaction log as its "working space" for everything it does, so it will always be non-zero in size. In my experience, I normally expect the tran log to be between 10% and 25% the size of the data files, depending on what recovery mode you're using, and the kind of activity on the database. You didn't mention the size of your data file, so I can't judge whether 302MB is big (it frankly doesn't sound very big to me).
Regarding recovery mode, the key question here is: if you have to recover the database (due to an emergency), will you need "point-in-time" recovery? Or is a restore to the latest full backup sufficient?
Putting aside some unusual situations (replication/CDC/mirroring/availability groups), your recovery mode choice is more of a business decision than it is a technical one. In either mode, SQL server uses the transaction log for in-process transactions, the difference is what happens next:
- FULL recovery mode: When a transaction completes, it sticks around in the tran log until you perform a transaction log backup. This tends to make the log larger (depending on how often you run the log backups), but means you can do a "point-in-time" recovery in the event of a disaster: you can restore the latest full backup, then use the tran log backups to "roll forward" to just before the disaster occurred.
- SIMPLE recovery mode: When a transaction completes, the space it used in the tran log is immediately available for re-use by later transactions. This keeps the transaction log file (comparatively) small, but means that your only choice in the event of a disaster is to restore from the latest full backup.
In some companies, you'll use FULL for all production databases, and SIMPLE for all QA/DEV environments. Or perhaps some specific databases, like ones used for reporting and/or data processing don't need full recovery because you can simply re-run the prior day's processing to get back up to date. But like I said, that's a business decision/risk analysis question, not really a technical one.
So, if you don't need point-in-time recovery, then switch it to simple mode, and leave it that way.
If you do need point-in-time recovery, then switch it to full mode, and run regular tran log backups throughout the day, according to your business risk/recovery needs. Hourly is popular, but some people recommend them far more often.
One final point that I alluded to before: once you've selected the correct recovery mode, you might still need to shrink an over-sized log. A few things to keep in mind:
- Again, make sure you've chosen the right recovery model
- If you're running in FULL mode, make sure you're running frequent tran log backups
- Choose a reasonable target size for the log, never try to shrink it to zero. I typically scale it based on the total size of the data files (or at least the used space within the data files)
- Read and understand this important advice about VLF size. This might provide more guidance about deciding on a reasonable log size, or give you a better method for getting your log to that size.
- Never use
SHRINKDATABASE, always use
- In both simple and full mode, there is a chance that the end of the log file is currently being used by SQL for an active transaction. If that's the case, then it won't shrink as much as you want. Just shrink it again a few minutes later, and if the transaction is complete, it should work better the second time.
- With a particularly stubborn tran log in full recovery mode, I've had luck doing
SHRINKFILE, immediately followed by a transaction log backup, immediately followed by another
DBCC SQLPERF(LOGSPACE)return. Lastly, what's the default size set to? I've never tried to set a log file to 0 but perhaps this is reverting to the default