Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Adding an extra file to TEMPDB Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 3:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 1,963, Visits: 2,898
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1424785
Posted Wednesday, February 27, 2013 4:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 234, Visits: 927
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
Post #1424810
Posted Wednesday, February 27, 2013 9:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:37 PM
Points: 1,612, Visits: 1,536
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1424878
Posted Thursday, February 28, 2013 1:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1424947
Posted Thursday, February 28, 2013 1:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1424949
Posted Thursday, February 28, 2013 9:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:37 PM
Points: 1,612, Visits: 1,536
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425102
Posted Thursday, February 28, 2013 10:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1425179
Posted Thursday, February 28, 2013 10:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 1,963, Visits: 2,898
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425180
Posted Thursday, February 28, 2013 10:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1425183
Posted Thursday, February 28, 2013 10:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 1,963, Visits: 2,898
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425186
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse