SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding an extra file to TEMPDB


Adding an extra file to TEMPDB

Author
Message
ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20382 Visits: 7427
Robert Davis (2/27/2013)
ScottPletcher (2/27/2013)


If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size? [And from a performance perspective, that's what you want it to do!]


No, it returns to the defined size, not the physical size. The defined size for tempdb files are maintained in master.sys.master_files whereas the current size is maintained in tempdb.sys.database_files. These two tables are the same for all databases except tempdb. Only tempdb is tracked differently like this.



OK. I always explicitly specify the size of tempdb data files, and they don't grow, so mine come back up the same size.

I have had to used start up procs in the past, but hopefuly that's obsolete now, if you can issue an ALTER on the size and have the file automatically become that smaller size when SQL restarts.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
PearlJammer1
PearlJammer1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1722 Visits: 1452
Ok thanks for the info.
So just to be clear about this one.
1. I can put the secondary tempdb file on the same disc as the primary file.
2. Make the second file an inital size of say 1Gb
3. Reduce the current size of the primary tempdb to 1 Gb (or whatever size i have set in point 2 above)
4. Reboot the server for the change in the primary file to take effect.

Regarding point number 2 above. My current size is 1.5gb, so would it not be better to make the second file 1.5gb or is the reason for your suggestion of 800mb because there would be 2 files of 800mb each therefore equating to approx 1.5gb in total ?
Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?
Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?

Thanks
Steven
Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6870 Visits: 1632
Yes, that was the reason for his suggestion of two files at 800MB so that together they would be about 1.5 GB. Personally, I always advocate pre-sizing your tempdb files to consume ~90% of the available drive space.

You can read my recommendations in full detail in the white paper I wrote on tempdb: [url=https://www.idera.com/Action/RegisterWP.aspx?WPID=43][/url]



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233251 Visits: 46361
ScottPletcher (2/27/2013)
GilaMonster (2/27/2013)
ScottPletcher (2/27/2013)
Then, set up a start up proc to shrink both tempdb files ... I'd say to 1G each, so you have some cushion. You really don't ever want the tempdb data files dynamically growing if you can avoid it.


Why would you set a startup proc to shrink TempDB when restarting SQL sets TempDB back to it's defined size? If the files are wanted at 1GB each, set the size of both to 1GB and when SQL starts up the files will be 1 GB each without any need for shrinks (which is documented to be able to cause corruption in TempDB)


If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size?


No, it's set to it's default size. So if the file's initial size is 800MB and it's grown to 1.5 GB, on restart it'll be 800MB again. That's the only safe way to shrink TempDB files (other than starting SQL in single user mode and running a shrinkfile). Any form of shrink where there's a chance for other users (even at the point of startup) can cause corruption that requires a restart to fix.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233251 Visits: 46361
stehoban (2/27/2013)
Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?


Not shrink. Alter database and set the initial size of the original TempDB file. That size takes effect on a restart of SQL, resulting in 2 files of the defined size.

Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?


No reboot necessary. Not SQL agent though, restart SQL Server itself

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6870 Visits: 1632
GilaMonster (2/28/2013)
[b]
[quote]Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?


No reboot necessary. Not SQL agent though, restart SQL Server itself


Did you mean to say "no reboot necessary" or "no, reboot necessary"?


The reason for the reboot is so the free space will be the same in all files. Technically, it's not necessary, but it's the only way to ensure that the free space is the same and all files get used equally.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233251 Visits: 46361
Robert Davis (2/28/2013)
GilaMonster (2/28/2013)
[b]
[quote]Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?


No reboot necessary. Not SQL agent though, restart SQL Server itself


Did you mean to say "no reboot necessary" or "no, reboot necessary"?


The reason for the reboot is so the free space will be the same in all files. Technically, it's not necessary, but it's the only way to ensure that the free space is the same and all files get used equally.


I meant exactly what I said. No reboot necessary. A restart of the SQL Server service is very strongly recommended. A reboot of the entire server is not required at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20382 Visits: 7427
So you don't use a startup proc to verify tempdb data files are all the same size and sync them if they are not?

How, then, do you become aware of and correct the situation where one or more, but not all, of the tempdb data files have automatically extended? Maybe there is some other method of which I am not aware and could use.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233251 Visits: 46361
You don't need to correct the situation where one of more of the files has expanded because on restart of SQL the files will be back to their defined size. If both files have a sized defined in the system catalogs of 1GB, then after a restart of SQL they will both be 1GB, regardless of what they automatically grew to before the restart.

Also, if the files are sized the same to start and have the same growth increment, proportional fill will ensure that they fill at the same time and they should all grow at the same time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20382 Visits: 7427
GilaMonster (2/28/2013)
You don't need to correct the situation where one of more of the files has expanded because on restart of SQL the files will be back to their defined size. If both files have a sized defined in the system catalogs of 1GB, then after a restart of SQL they will both be 1GB, regardless of what they automatically grew to before the restart.

Also, if the files are sized the same to start and have the same growth increment, proportional fill will ensure that they fill at the same time and they should all grow at the same time.



So proportional fill will "ensure" that? I wasn't sure that was guaranteed. I know it wasn't in the past.

If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search