Post reply

change initial size of tempdb

  • March 2, 2012 at 1:02 pm

    #269410

    I am seeing one of our drives are out of space.

    And the tempdb is on that drive, it used about 20 gb space.

    I go to management studio, and right click tempdb database, I see it inital size is 20 gb.

    But if I see the spaced in the tempdb,

    Data file is 20 gb, free space is 99%.

    I tried to change the intial size to 10 gb, but when I reopen it, it changed back to 20 gb again.

    What could be the problem?

    Thanks

  • March 2, 2012 at 1:16 pm

    #1454356

    size will not change until you stop and restart the server.

    at that time tempdb , since it will be recreated, will change to 10G instead of it's current size.

    I don't think you can shrink tempdb while the server is running.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 2, 2012 at 1:32 pm

    #1454366

    But how it knows it will change to 10 gb?

    I tried to change the size on then database property-file-inital size window, it not save it, when I reopen it, it changed to 20gb again.

  • March 2, 2012 at 3:17 pm

    #1454393

    Any help please?

    Thanks

  • March 2, 2012 at 3:26 pm

    #1454397

    The following will change the initial size to 10,000MB. You will need to restart the SQL Server service for it to take effect.

    ALTER DATABASE tempdb

    MODIFY FILE

    (NAME = tempdev, SIZE = 10000MB)

    GO

    You can validate the change by running:

    SELECT name, size/128 as SizeMB from sys.master_files

    WHERE database_id = DB_ID('tempdb')

  • March 2, 2012 at 3:29 pm

    #1454398

    Thanks.

    Is there also a way to change in SSMS, like I said in earlier post it won't save it.

    Thanks

  • March 2, 2012 at 4:12 pm

    #1454406

    sqlfriends (3/2/2012)

    Thanks.

    Is there also a way to change in SSMS, like I said in earlier post it won't save it.

    Thanks

    you've been told twice already so, third time lucky

    you will need to restart the service for the change to take effect

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • March 2, 2012 at 4:27 pm

    #1454409

    I understand after I restart the service, the changes will take effect if I use the SQL statement to change it.

    The thing I don't understand is by using ssms, will that work?

    Also I just tried to do this on a tempdb on my local server on my workstation, I found from SSMS I can change the tempdb to a smaller size, and after I reopen it , it shows new size. I don't even need to restart the service, but why on the production server it cannot be changed?

    Thanks

  • March 2, 2012 at 7:08 pm

    #1454412

    Further experience on the production server, I found if I run the sql statement to change the file size, then I open ssms, it changed there, even I don't restart the service.

    I know it will take effect after restart.

    But if I changed in production ssms , it won't save after I reopen it.

    But on my local server, I can change it in SSMS, when reopen it, it saves the changes.

    The different behaviour on different computer confuse me.

    Also if I change the restrict of file growth from unlimited to certain size, will that also take effect after tempdb restarted, I do see it saved on the produciton server after I change it in SSMS this time for this setting.

    Thanks

  • March 2, 2012 at 8:00 pm

    #1454415

    sqlfriends (3/2/2012)

    Further experience on the production server, I found if I run the sql statement to change the file size, then I open ssms, it changed there, even I don't restart the service.

    I know it will take effect after restart.

    But if I changed in production ssms , it won't save after I reopen it.

    But on my local server, I can change it in SSMS, when reopen it, it saves the changes.

    The different behaviour on different computer confuse me.

    Also if I change the restrict of file growth from unlimited to certain size, will that also take effect after tempdb restarted, I do see it saved on the produciton server after I change it in SSMS this time for this setting.

    Thanks

    that behavior has to do with the size used.

    on your local, if you expand tempdb to something huge, then turn around and change the value again to a smaller value, it never actually filled that space...just reserved it, so there's no problem un-reserving that space.

    on production, it actually grew to 20 gig because it needed it for whatever reason...once it was used, it remains at 20 gig until restart.

    after changing it to 10 gig, it could very well be that later that afternoon, it will be 20 gig again, as it might expand due to needs.

    so what you are seeing is you cannot immediately change the size reserved to be what is actually been used (even if it is 99% free...it's because ti was used by some process)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 2, 2012 at 11:46 pm

    #1454417

    Any changes to tempdb should be followed by a service restart. When you add files they will take affect immediately but you need to restart to force the proportionate fill algorithm evenly across the files.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • November 14, 2012 at 12:21 am

    #1558755

    Do I need to restart SQL Server Service after changing initial size and growth rate?

  • November 14, 2012 at 3:31 am

    #1558828

    are you increasing or decreasing the initial size?

    are the files all currently the same initial size?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • November 14, 2012 at 5:58 am

    #1558884

    Increasing the size to 10GB (Normal Tempdb size of my production Server tempdb).

    Only one file. No plan to split the tempdb file.

Viewing 14 posts - 1 through 13 (of 13 total)

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