Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » change initial size of tempdb
Add to briefcase
14 posts, Page 1 of 2
12»»

change initial size of tempdb Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sqlfriends
Posted Friday, March 02, 2012 1:02 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 1,627, Visits: 2,827
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
Post #1261017
Lowell
Posted Friday, March 02, 2012 1:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 11,784, Visits: 28,041
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1261021
sqlfriends
Posted Friday, March 02, 2012 1:32 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 1,627, Visits: 2,827
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.

Post #1261033
sqlfriends
Posted Friday, March 02, 2012 3:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 1,627, Visits: 2,827
Any help please?

Thanks
Post #1261065
JeremyE
Posted Friday, March 02, 2012 3:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 2,413, Visits: 2,679
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')
Post #1261069
sqlfriends
Posted Friday, March 02, 2012 3:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 1,627, Visits: 2,827
Thanks.

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

Thanks
Post #1261071
Perry Whittle
Posted Friday, March 02, 2012 4:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:04 AM
Points: 5,242, Visits: 11,256
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"
Post #1261082
sqlfriends
Posted Friday, March 02, 2012 4:27 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 1,627, Visits: 2,827
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
Post #1261086
sqlfriends
Posted Friday, March 02, 2012 7:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 1,627, Visits: 2,827
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
Post #1261090
Lowell
Posted Friday, March 02, 2012 8:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 11,784, Visits: 28,041
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1261093
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.