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?

DeeKayy90
  • 103
  • 3
  • 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 Oct 23 '15 at 02: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 Oct 23 '15 at 03:00
  • 1
    What are you considering a reasonable size? What is the frequency that you are performing the transaction log backups? – Aaron Oct 23 '15 at 03:42
  • You would make things worse by shrinking it. What possibly you can gain from 256MB. – Shanky Oct 23 '15 at 05:32
  • I understand why you'd want the log to be less than 52GB on an 8GB database, but 256 MB seems small enough. Is there any requirement to make it smaller? – Greg Oct 23 '15 at 06:19
  • In all honesty, there is no real 'reason' other than (I don't know a word that will fit here) - possibility? It just seems that a log file that has reserved space that it's not using even close to 1% of, is a waste of space. Perhaps I am missing the point of the log file. In any case, I am simply curious as to why it refuses to go below 256 MB? And now that it's been brought to my attention, why below 256 MB is not good for a log file. I thank you all for your time in this matter. – DeeKayy90 Oct 23 '15 at 06:56
  • 1
    Please read this in full. – Aaron Bertrand Oct 23 '15 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 Oct 23 '15 at 14:17
  • Thank you Aaron for that link, I will have a thorough read through it. And Sunzi, thanks for that, it completely slipped my mind that that could be a possibility! – DeeKayy90 Oct 24 '15 at 00:10
  • @Sunzi Your comment seems to be the answer that I'm looking for. If you want to post it as an answer, I will accept that :) Cheers. If you have any links that refer to this that would be great, but not necessary - just as some light reading material, hehe. – DeeKayy90 Nov 03 '15 at 09:50
  • @DeeKayy90 I've posted an answer and while I searched for links, I've found out, that my comment was not correct, it is not the inital size that is the Limit, but the virtual log file size (which is set by the database engine, so it may be the same as the initial size but does not need to. BTW: Is there a possibility to edit my old comment to correct the Information? I don't have an edit link, my new comment has one. – Sunzi Nov 03 '15 at 10:43

1 Answers1

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.

Sunzi
  • 221
  • 1
  • 4