Sql-server – Log file shrink attempts are futile

sql servertransaction-log

I have got a question relating to SQL server log files in a production environment, however what I am trying to do is first run some testing on a smaller Test Database (E10Test) before I move on to the production database. However I am having some issues on shrinking the Log file.

Here are the Database properties of E10Test

Logical Name | File Type | Filegroup | Initial Size (MB) | Autogrowth / Maxsize

E10Test | Rows Data | PRIMARY | 7337 | By 100 MB, Limited to 10248 MB

E10Test_log | Log | Not Applicable | 256 | By 20 MB, Limited to 2048 MB

When running DBCC sqlperf ('logspace')
Here is the output

E10Test | 255.875 | 0.1463651 | 0

Log_reuse_wait = 0
Log_reuse_wait_Desc = NOTHING

Looking at the information above the log is not even 1% used. In theory I should be able to shrink the log file to a smaller size right?

Db is on Full recovery mode and I have done a full backup of Db and Log file backup.

I have tried the following without much luck:

  1. Changing the size from the Db properties (Outcome is the same as before)
  2. Using both script and GUI to shrink the Log file (Outcome is the same as before)
  3. Set Db to simple recovery and repeat the above (Outcome is the same as before)

No matter what I do the log is still on 256Mb and it does not go any lower.
Is this something caused by Microsoft or is there something I have missed out on?

Best Answer

The documentation to dbcc shrinkfile says:

Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used. The size of the virtual log file is chosen dynamically by the Database Engine when log files are created or extended.

So it isn't possible to shrink the logfile to an arbitrary small size. To check your virtual log files, you can use


There you can see the virtual log files and their size. Probably you hit the virtual log file size.