Why Can't I shrink log file in full recovery mode I have a log file that is 302MB.

I have done a log backup which has left the log file mostly free (I can see this through the Disk Usage standard report)

If i try to run 

DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 0, TRUNCATEONLY)


or 

DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 0)


the file is still showing as 302MB.

I know I can change the database to simple recovery, then run one of the commands above and set back to full recovery (and then do a full backup to make sure the database is not in Pseudo-simple recovery mode)

However, why can I not shrink the file in full recovery mode?

I know shrink isn't something you should do, but in my real world database, since no one had ever backed up the transaction log it had grown to 30GB and now regular log backups have been put in place to prevent growth of this level
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 SHRINKFILE
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 SHRINKFILE.