Size of tempdb isn't reducing the size even in single mode.

Question

Size of tempdb isn't reducing the size even in single mode.

TRACEY-320982

SSChampion

Points: 13653
More actions
July 12, 2019 at 9:48 pm

#3662527

I am trying to reduce the size of the tempdb database
1. Stop Server Server
Net Stop MSSQLSERVER (Name of Services)
2. Net Start MSSQLSERVER /c /m /T3604
Bring up SQL Studio and DO NOT hit Connect, Just Hit New Query
--Check before I run statement
use tempdb
select (size*8) as FileSizeKB, * from sys.database_files ORDER BY TYPE DESC
This is size 184320 max size 209920
Even if I try to reduce it a little bit it will not allow me.
4: ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 184310 KB), MAXSIZE = 209910 KB)
This generates no error.
--Check it works nothing is changed, even after a restart of SQL Server

It did work as the original size was 1024 and I increased it and then decided to decrease it as I am just testing this out.
So the increase works, but the reduce in size doesn't allow.

Any ideas ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 1

I am trying to reduce the size of the tempdb database

1.  Stop Server Server

Net Stop MSSQLSERVER   (Name of Services)

2. Net Start MSSQLSERVER /c /m /T3604

Bring up SQL Studio and DO NOT hit Connect, Just Hit New Query

--Check before I run statement

use tempdb

select (size*8) as FileSizeKB, * from sys.database_files ORDER BY TYPE DESC

This is size 184320 max size 209920

Even if I try to reduce it a little bit it will not allow me.

4: ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 184310 KB), MAXSIZE = 209910 KB)

This generates no error.

--Check it works nothing is changed, even after a restart of SQL Server

 

It did work as the original size was 1024 and I increased it and then decided to decrease it as I am just testing this out.

So the increase works, but the reduce in size doesn't allow.

 

Any ideas ?

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

I got it to work, I had to do all these extra commands

Question
I ran this
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1024000 KB)-->1000 MB  --Putting in 128000 as the size when viewing 
select size, * from sys.database_files ORDER BY TYPE DESC
I get 128000 as the size why is this ? as I said i wanted 1024000 KB in ALTER Command

Thanks

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 3

Tried in test environment and didnt find any issue in changing the file size. What is your model database file size?

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

Model 103 MB

For another test I changed the size to 204 MB
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 204 MB)
This works as increased it.

Them immediately tried to reduce it to  104 MB
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 104 MB)
It will not reduced the size.

I have to run these commands below.
use tempdb
 GO
 SELECT name, size
 FROM sys.master_files
 WHERE database_id = DB_ID(N'tempdb');
GO
 DBCC FREEPROCCACHE --clean cache
 DBCC DROPCLEANBUFFERS -- clean buffers
 DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
 DBCC FREESESSIONCACHE -- clean session cache
 DBCC SHRINKDATABASE(tempdb, 10); 
 dbcc shrinkfile ('tempdev')
 dbcc shrinkfile ('temp2') -- shrink db file tempdev2
 dbcc shrinkfile ('temp3') -- shrink db file tempdev3
 dbcc shrinkfile ('temp4') -- shrink db file tempdev4
 dbcc shrinkfile ('templog') -- shrink log file
 GO

Now after above i run
Them immediately tried to reduce it to  104 MB
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 104 MB)

It works.