SQL Server Performance

Change Tempdb initial size

Discussion in 'General DBA Questions' started by rklimes, Oct 21, 2007.

  1. rklimes New Member

    I would like to lower the initial size of tempdb.
    I have tried running
    USE master
    (NAME = tempdev,
    SIZE = 2048MB)

    but get error
    MODIFY FILE failed. Specified size is less than current size.

  2. techbabu303 New Member

    Try this script from books online
    --Increase the size of the tempdb device
    DISK INIT name = 'tempdb1',physname = 'c:mssqldata empdb1.DAT',vdevno = 100, size = 12800
    --Increase the size of tempdb
    ALTER DATABASE tempdb ON tempdb1 = 25
  3. techbabu303 New Member

    For expanding the dafualt logical file tempdev ua can also use the below
    use master
    ALTER DATABASE tempdb ON tempdev = 25
    The above with increase it by 25 MB
  4. techbabu303 New Member

    I guess if the specifies size is less than current size u cannot use this command , as your error ,message states.
    I am not sure if restart and rebuild of system database can fix that, I hope gurus have something up there sleeve on this.
  5. techbabu303 New Member

    This worked for me ,
    dbcc shrinkfile ('tempdev',6,TRUNCATEONLY)
    Traget size in MB = 6
    TRUNCATEONLY = gives the reclaimed space to OS.
  6. satya Moderator

    I would like to ask what is the reason to shrink the TEMPDB size, if the other processes needs space for calculations then TEMPDB is used extensively and it will grow again. So try to reduce number of round trips in this case and I'm sure we can suggest a workaround if you can explain the problem.
  7. rklimes New Member

    the tempdb was just created with the wrong size. It was created on our reporting database with the same size as the production DB. The rep DB does not need a tempdb with the same allocated size as production. The reason I need it smaller is that the Initial size is 17gb and is taking up space on disk that is becoming limited.
  8. satya Moderator

    Ok, follow as suggested above and you might still get in to the issues in going upper size, you have to control usage of temp database within the queries or run in smaller batches.
  9. rklimes New Member

    I am not sure my problem is being understood correctly. I do not have a problem with the tempdb growing too large. It is almost never used. When sql server was installed on this server the settings for the tempdb were copied from our production environment (which does need a large tempdb). Since the tempdb was created at a large size I cannot decrease the size of the database beyond the specified Initial size. The size of the tempdb is 17gb which is unneccesarily large.
  10. rklimes New Member

    Thanks for the replies. I have found a solution to my problem. I started sql server from command line using

    sqlservr -c -f
    sql server started in minimum configuration mode with a tempdb size of 1MB. I then ran my statement above effectively increasing tempdb size from 1MB to 2048MB (but decreasing from 17GB). after restarting service tempdb size was 2048MB.
  11. satya.sqldba New Member

    This problem is there for not only tempdb but any database, you cannot shrink it beyond the initial size, but I have tried a work around that has worked for me in the past.
    Try re-setting the initial size of the data file by increasing it by 5 or 10MB and then try to issue the shrink command, it has worked for me
  12. satya Moderator

  13. rklimes New Member

    I have already found a solution, which I stated in my previous post. It is the same thing and the KB provided. thanks.
  14. satya Moderator

    Ok, [:)] it will be hard without any such reference for the previous posts.

Share This Page