question

TheDBLearningGuy avatar image
0 Votes"
TheDBLearningGuy asked ·

Initial size of the log file is changing back to its original size.

I have a database whose log file initial size is 1MB, I have changed it to 4MB. But when I check the initial size after some time its size shows again to 1 MB. The auto shrink option is set to false for the database. Could any please let me know why it is changing back to 1 MB.

Note: Model database's logfile initial size is at 1MB.

Thanks in advance!

sql-server-general
· 3
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KrishneGowdaKV-7600,

Could you please show me the screenshot of the log file size where you changed? And you can always verify the current size of the database files by running the following command:

 SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, Size*8/1024 SIZE_MB
 FROM MASTER.SYS.MASTER_FILES
 WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'


0 Votes 0 ·

Hi, we haven't get your reply, any update for this?

0 Votes 0 ·

Sorry for the delayed response!

When I change the initial log file size value and check it through GUI, sp_helpdb & DBCC Sqlperf(logspace) - It does show the new value for that moment.

And changes back to old value after few hours [In this case 1 MB]. I did verify that neither any automated job nor someone manually changing it. [I think shrinking of a log file or data file for that matter shouldn't change the initial size].

Here is the output of the query shared:

77660-image.png

GUI snap:
77600-image.png


0 Votes 0 ·
image.png (21.5 KiB)
image.png (66.8 KiB)
TiborKaraszi avatar image
1 Vote"
TiborKaraszi answered ·

Where do you see this "initial" size? If you are using an old SSMS, then it incorrectly say "Initial", then it in fact show you the current size.

Just to be certain, check the file size on disk as well. If you see that the file is smaller then it was, then something did a shrink on that tile. Possibly autoshrink, a scheduled job, or somebody doing this manually.

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TiborKaraszi,

The SSMS used is SSMS v17.9 and yes, the file size on the disk is smaller than the initial value.
The DB is in Simple recovery model & part of daily full backup schedule.
Auto shrink is set to false, no manual or automated shrink to log file.

I'm trying to understand what makes the initial file size to change every time.

Thank you.

0 Votes 0 ·

Something shrinks that file. SQL Server doesn't do that by itself. Your task is to find out who does this* We can't do this, since we don't have access to your system. I'd run a trace and see when the file size changes and take it from there.

Also, don't get hung up on the word "initial". What SSMS is showing you is the current file size.

1 Vote 1 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ·

Hi @KrishneGowdaKV-7600,

Which version of SQL Server do you used? I have been tested in my side, the log file size will not change if I modified the size value. And the size means current log file size, see below:
77967-physical-file.png
77969-logfilesize.png

Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



physical-file.png (3.4 KiB)
logfilesize.png (42.1 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.