TEMPDB File size inconsistent file size

Question

TEMPDB File size inconsistent file size

pdsqsql 431

Hello,
I have Sql Server 2014 and i have added temp Db file as already we have two mdf and 1 ldf files.

I already resized file using following command but it still shows that inconsistent file size:

alter database tempdb modify file (name = tempdev1, size = 1024 MB, filegrowth = 128 MB)
GO

alter database tempdb modify file (name = tempdev2, size = 1024 MB, filegrowth = 128 MB)
GO

alter database tempdb modify file (name = tempdev, size = 1024 MB, filegrowth = 128 MB)
GO

alter database tempdb modify file (name = templog, size = 1024 MB, filegrowth = 128 MB)
go

But one of tool complaining that TEMPDB File size inconsistent file size.
When I checked, all the files it shows following:

Logical file Name Initial Size (MB) Auto Growth
TEMPDEV 6912 128 MB
TEMPDEV1 6784 128 MB
TEMPDEV2 6912 128 MB
TEMPLog 1024 128 MB

I tried few time but still shows inconsistent file size.
Is it I need to Restart the Service so it will reinitialize again the changes?

Thanks for your help!

Accepted answer

5 additional answers

Your answer

Answer 1

pdsqsql 431

Thank you Criszhan.
I can see right now after restarting Service, it shows all same size.

**DBNAME    LOGICAL_FILENAME    SIZE_MB**
tempdb  tempdev                     1024
tempdb  templog                     1024
tempdb  tempdev1                 1024
tempdb  tempdev2                 1024

**name  Initial Size (MB)   Current Size (MB)   Used (MB)**
tempdev         1024.000000 1024.000000         3.375000
templog         1024.000000 1024.000000         21.375000
tempdev1    1024.000000 1024.000000         1.187500
tempdev2    1024.000000 1024.000000          1.375000

Cris Zhan-MSFT 6,661 Reputation points

2021-01-08T02:00:13.563+00:00

Hi @pdsqsql ,

Thank you for your reply.
If the answer is helpful, please click "Accept Answer" and upvote it.

Best Regards,
Cris

Answer 2

Shashank Singh 6,251

I vaguely remember that you might need a SQL Server service restart to reflect this in SSMS GUI. Anyways what does below return

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

pdsqsql 431 Reputation points

2021-01-05T17:37:04.847+00:00

Thanks Shashank.
I am thinking same as need to restart the sql service.

Getting error as some where syntax issue so it should be following right?

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

DBNAME LOGICAL_FILENAME SIZE_MB
tempdb tempdev 1024
tempdb templog 1024
tempdb tempdev1 1024
tempdb tempdev2 1024
Shashank Singh 6,251 Reputation points

2021-01-06T07:49:02.4+00:00

You are right mate, I had it from somewhere but do not know the source of the code sorry about that. Anyways if you see the output, all the files now have same size ( incorrectly called as initial size) so your alter statement is good and worked. Now for SSMS GUI information to reflect it would need restart which you can do after taking approval no hurry though. All is good as sys.master_files is reporting correct size.

Answer 3

Tom Phillips 17,771

That is perfectly normal. Not all files grow at the same time.

What do you think is wrong with the data you posted?

pdsqsql 431 Reputation points

2021-01-05T17:38:05.357+00:00

thanks Tom, Agreed with you as not all files grow same time.

You mean Initial size?
Tom Phillips 17,771 Reputation points

2021-01-05T18:25:18.963+00:00

Changing the size parameter does not shrink the physical file. You need to use SHRINKFILE to reduce the physical size of the file.

https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server
Jeffrey Williams 1,896 Reputation points

2021-01-05T22:33:15.243+00:00

And - if the files have grown out to 6GB for normal operations you might be better off setting the initial size to 6GB instead of 1GB and having the files grow again. You can also set trace flag 1117 for tempdb - which will force the files to grow at the same time (see: https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/)
pdsqsql 431 Reputation points

2021-01-05T22:57:51.65+00:00

Thanks Jeff.
So you are pointing tempdev 1024 to TEMPDEV 6912, that's why you are suggesting to set initial size to 6GB?

Should I wait till Sql/Sql Service Restart so my initial changes because I have ran alter statement 1 month back and didn't have sql service restart so it might be 6 GB growth (from 1024 Initial Size 1024 to 6912 showing) it month be a 1 month time frame?

Thank you!
pdsqsql 431 Reputation points

2021-01-07T14:27:05.417+00:00

Thanks Tom.
Very useful link.
pdsqsql 431 Reputation points

2021-01-07T14:28:57.417+00:00

Thanks Jeff.
Agreed that need to setting the initial size to 6GB instead of 1GB but I have restarted Sql services so I will watch the growth and will make accordingly or you might suggest to just change now 6 GB?

Answer 4

Cris Zhan-MSFT 6,661

Hi @pdsqsql ,

>Is it I need to Restart the Service so it will reinitialize again the changes?

Yes.
According to my test, when using the statement - alter database tempdb modify file(...) to modify the size of specified database files, if the specified size is greater than the size of the current database files, then in the SSMS UI (Properties-Files) and the size of actual physical files can show changes.

But if the specified size is smaller than the current file size, it will not be displayed in the SSMS UI, and the actual physical file will not change. Until the SQL Server service is restarted.

You can always verify the current set 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'

Pds 46 Reputation points

2021-12-01T22:03:08.227+00:00
Hello,
Somehow it's keep coming Inconsistent File size for TEMPDB files even after restarting Sql Server Service.
Any idea to further troubleshoot?

DBNAME LOGICAL_FILENAME SIZE_MB Initial Size MB tempdb tempdev 1024 29056 tempdb tempdev1 1024 29056 tempdb tempdev2 1024 1280 tempdb templog 1024 29056

Answer 5

pdsqsql 431

Thanks everyone for your help!

Share via

TEMPDB File size inconsistent file size

5 additional answers

Your answer