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:
- Changing the size from the Db properties (Outcome is the same as before)
- Using both script and GUI to shrink the Log file (Outcome is the same as before)
- 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:
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.