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

TheDBLearningGuy 171

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!

CarrinWu-MSFT 6,851 Reputation points

2021-03-12T06:03:52.28+00:00
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'
CarrinWu-MSFT 6,851 Reputation points

2021-03-15T02:35:32.47+00:00

Hi, we haven't get your reply, any update for this?
TheDBLearningGuy 171 Reputation points

2021-03-15T07:55:53.453+00:00

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:

GUI snap:

2 answers

tibor_karaszi@hotmail.com 4,301 Reputation points

2021-03-11T14:34:02.277+00:00

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.
Please sign in to rate this answer.

1 person found this answer helpful.
TheDBLearningGuy 171 Reputation points

2021-03-15T08:21:42.92+00:00

Hi @tibor_karaszi@hotmail.com ,

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.

tibor_karaszi@hotmail.com 4,301 Reputation points

2021-03-15T13:44:17.673+00:00

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.
Sign in to comment
CarrinWu-MSFT 6,851 Reputation points

2021-03-16T02:01:23.367+00:00

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:

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment