Why does the "Initial Size" change?

Question

What causes a change in the "Initial size" parameter of the database or log database? For example, we are trying to collapse a transaction log that is too large. We noticed that the "Initial Size" is much larger than necessary. We can set it lower, but it again returns to a higher value. Does that mean AutoGrowth value?

Answer 1

The Initial Size information actually uses a very misleading heading. It must be "Current File Size (MB)" to be more accurate.

Try the following:

Select a database and check the current file sizes. Compare this to what you see in the GUI, they should be the same

SELECT file_id, name, physical_name, (size * 8 /1024.0) AS SizeMB FROM sys.database_files 

Now increase the file size (here I am using a test database called DBAdmin)

 ALTER DATABASE DBAdmin MODIFY FILE (NAME = dbadmin, SIZE = 1GB) 

Run the first query and you will see that SizeMB now reflects this new value, as well as the "Initial Size" in the GUI.

Now compress this file

 DBCC SHRINKFILE(1, 500) 

And again look at the values ​​from the query and the GUI, you will see that they are back at 500 MB.

This puts the question in the transaction log. Are you just recovering or are you full? If complete, increase the frequency of the log backups to maintain control of the transaction log. If in a simple recovery, then the size of the transaction log, accordingly, do not perform compressive operations on it, since it just needs to grow again if the same criteria are met, and part of the growth will lead to a loss in the performance of your database.