Why Can't I shrink log file in full recovery mode [duplicate]

Question

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

Answer 1

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:

1. Again, make sure you've chosen the right recovery model
2. If you're running in FULL mode, make sure you're running frequent tran log backups
3. 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)
4. 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.
5. Never use SHRINKDATABASE, always use SHRINKFILE
6. 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.
7. 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.

Answer 2

The relevant thing to know about SQL Server transaction log files is that they are written to in a circular fashion. This is going to over simplify things, and leave out some details like virtual log files (how it's actually handled internally), but it's a reasonable facsimile of reality.

Imagine you create a database and give it a 100 MB transaction log file. Transactions will be written starting from the beginning of the file. Suppose you then have 50 MB of transaction activity. You now are using the first 50 MB of the 100 MB transaction log. Then you take a transaction log backup. Now you're using nearly no space in the transaction log; practically the whole thing is available for (re)use. But what happens if you try to shrink the log to, say, 20 MB? It'll shrink it down to (roughly) 50 MB.

This is because the active portion of the log happens to be sitting around that 50 MB mark, and SQL Server won't shrink the log past where it's currently active. Think of it kind of like the play head on a cassette tape, and SQL Server won't/can't do anything with tape that's already on the take-up reel.

This is why shrinking the transaction log is frequently a two-step process: you do the first backup and shrink (chop off all the empty tape after the play head), then you generate some transaction activity so that SQL Server is forced to wrap back around to the beginning of the log file (rewind the tape), and finally do another backup and shrink (chop off more empty tape to get to your target size).

Note that there are some other situations that can prevent you from reusing (and thus shrinking) logs. Usually you can find out the reason by looking at the log_reuse_wait_desc column in master.sys.databases. Transactional replication, database mirroring, and AlwaysOn availability groups are common culprits.

Answer 3

Your question says "I have done a log backup" indicating one backup. If you have not already, do a couple of backups then try shrinking the log. It is not unusual for some transactions to not clear with a single backup. I have done 3 or 4 sometimes before they cleared.

Afterwards run DBCC LOGINFO to see what the VLF files look like. You can read Why are virtual log files not always allocated in order? to learn a bit more about VLFs

Answer 4

If you are working with Full Recovery Model, by definition you need Transaction Log Backups. If you don't need them it is useless to have Full Recovery Model.

You are not supossed to do shrinking in your way. Besides it reduces performance.

I suggest taking a transaction log backup and after that decide if you need Full or can go to Simple Recovery Model.

Also you can check active transactions maybe with dm_exec_sessions, and take a look of your fragmentation level on indexes to see if you have some configuration that is not helping with your shrinking.