0

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?

Improve this question
11
  • 1
    What was the initial size of the log file when you created the database? Why on earth would you ever want your log file smaller than 256 MB?
    – Aaron Bertrand
    Commented Oct 23, 2015 at 2:50
  • I will assume that having the log file smaller than 256 MB is not a beneficial act? The reason that this question has arose is because our .mdf is at 8.14 GB and our .ldf has blown way out at over 52 GB, and we are trying to maintain a method by which we can keep our .LDF's at a consistent, reasonable size.
    – DeeKayy90
    Commented Oct 23, 2015 at 3:00
  • 1
    What are you considering a reasonable size? What is the frequency that you are performing the transaction log backups?
    – Aaron
    Commented Oct 23, 2015 at 3:42
  • 1
    Please read this in full.
    – Aaron Bertrand
    Commented Oct 23, 2015 at 10:53
  • 1
    Your initial log size is 256 MB according to your posted database properties and as far as I know, you can't shrink it below the initial size. So it can't be smaller than 256MB.
    – Sunzi
    Commented Oct 23, 2015 at 14:17

1 Answer 1

Reset to default
1

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

DBCC LOGINFO

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

Improve this answer
1
  • Thank you Sunzi for the answer, and the linked query and documentation - it is greatly appreciated.
    – DeeKayy90
    Commented Nov 3, 2015 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.