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

  • TRACEY-320982

    SSChampion

    Points: 13489

    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: 13489

    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: 13489

    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: 35725

    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: 13489

    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.

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

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