July 7, 2013 at 11:42 pm
we are facing issue in tempdb file size , initial size we allocate 2 GB , but after restart sql server , size would be 1 MB , its gone 2 GB, plz anyone help , How size will keeping on 2 GB after restart sql server ..
Any settings ?
Thanks
Jerry
July 8, 2013 at 2:14 am
TempDb is used for several things in SQL Server. DBCC CHECKDBs, temp tables, row versioning, etc. If TempDb keeps getting to 2GB in size I would say that is a good indicator of the size it needs to be in order to handle all of the temporary objects that is being requested of it on a routine basis.
I would recommend changing the default size of TempDb from 1MB to 2GB to prevent the growth actions after SQL Server is restarted.
Joie Andrew
"Since 1982"
July 8, 2013 at 6:35 am
Joie Andrew (7/8/2013)
TempDb is used for several things in SQL Server. DBCC CHECKDBs, temp tables, row versioning, etc. If TempDb keeps getting to 2GB in size I would say that is a good indicator of the size it needs to be in order to handle all of the temporary objects that is being requested of it on a routine basis.I would recommend changing the default size of TempDb from 1MB to 2GB to prevent the growth actions after SQL Server is restarted.
Hi Joie
I think the OP means that he set the initial size of his TempDB datafile to 2GB, restarted SQL Server and the size went back to 1MB.
July 8, 2013 at 10:37 am
If you grow the files, they should be at that size on restart. Are you sure you manually grew them and didn't just assume they'd stay the same because they grew from activity?
July 8, 2013 at 11:05 am
Any settings need to be do on Model database ?
July 8, 2013 at 11:13 am
No.
What exactly happened and why is it a concern?
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
July 8, 2013 at 8:20 pm
Joie Andrew (7/8/2013)
TempDb is used for several things in SQL Server. DBCC CHECKDBs, temp tables, row versioning, etc. If TempDb keeps getting to 2GB in size I would say that is a good indicator of the size it needs to be in order to handle all of the temporary objects that is being requested of it on a routine basis.
I would recommend changing the default size of TempDb from 1MB to 2GB to prevent the growth actions after SQL Server is restarted.
Hi Joie
I think the OP means that he set the initial size of his TempDB datafile to 2GB, restarted SQL Server and the size went back to 1MB.
Ah yes, I totally turned that around in my head when I read it.:ermm:
Thanks for the clarification!
Joie Andrew
"Since 1982"
July 9, 2013 at 1:17 am
There is nothing to worry about. TempDB is recreated each time the instance/server is restarted and it will consume however much space it requires to perform its function. The fact it is so small by a restart is nothing to be concerned about as (by the very nature of the file itself) the data contained within is temporary.
July 10, 2013 at 5:47 am
kevaburg (7/9/2013)
There is nothing to worry about. TempDB is recreated each time the instance/server is restarted and it will consume however much space it requires to perform its function. The fact it is so small by a restart is nothing to be concerned about as (by the very nature of the file itself) the data contained within is temporary.
It absolutely is something to be concerned about. You want to have TempDB properly sized, so in this case the OP would want to size it at 2GB or higher. If you allow TempDB to "autogrow" you will most likely have performance issues as the TempDB file(s) grow.
Steve
July 10, 2013 at 10:28 am
After you issue the command(s) below, the next time SQL starts, tempdb will be sized accordingly.
Split the 2GB into equal parts, for however many tempdb data files you have.
For this example, I'll assume you have 2 data files -- if not, adjust accordingly.
ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev, SIZE = 1GB )
ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev2, SIZE = 1GB )
ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog, SIZE = 512MB ) --<< or whatever values matches your max expected usage
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 19, 2013 at 10:15 am
Steve-3_5_7_9 (7/10/2013)
kevaburg (7/9/2013)
There is nothing to worry about. TempDB is recreated each time the instance/server is restarted and it will consume however much space it requires to perform its function. The fact it is so small by a restart is nothing to be concerned about as (by the very nature of the file itself) the data contained within is temporary.It absolutely is something to be concerned about. You want to have TempDB properly sized, so in this case the OP would want to size it at 2GB or higher. If you allow TempDB to "autogrow" you will most likely have performance issues as the TempDB file(s) grow.
Steve
This is something that could become very contentious. Why would growth on the TEMPDB be a problem if the database itself is properly sized (the DB determines that itself) and growth rate is properly set?
This, from my point of view, only becomes a problem if the database server is restarted on a regular basis. The people that insist on restarting a Windows Server / Database Server make this a problem all by themselves.
I don't think the problem is in answering the question "why doesn't it maintain the settings I give it" rather "why was the instance restarted in the first place?"
Because TEMPDB is recreated each time the instance is started, it must also be populated each time the instance starts with the data the needs to be loaded. Resizing (if done properly) is not a huge issue in SQL Server although I am not taking away the fact that it requires resources to perform. The most intensive operation here is repopulating the database so it can operate in the way it should. This is the equivalent of restarting an Oracle database and wondering why each initial query execution is slower than normal simply because the shared pool has been flushed.
Anyway, that is why I believe it isn't a problem.....
(Sorry the answer took so long.....hoilday has a somewhat higher priority!)
July 19, 2013 at 10:57 am
I'm not sure I completely understand your reply, but it seems as though your saying just let TempDB grow as needed.
TempDB should be sized just like any other database. One should know their workload, or at least monitor the TempDB over several weeks to see what is a healthy size for it. Once this is determined, you then can set the size of the TempDB, so that when the server is restarted, it will be at the correct size, thereby having to use "auto grow".
"auto grow" on TempDB and other database can cause serious performance issues, and only be used as a safety net.
As far as "repopulating" TempDB being important. What would it be repopulated with? This database only exists for temporary operations. Once a connection is closed any "temp" objects created (tables, views, etc..) by that SPID will be dropped. TempDB doesn't store query cache, execution plans, or stats. A restart of the server flushes the cache, memory.
Steve
July 19, 2013 at 11:25 am
Steve-3_5_7_9 (7/19/2013)
TempDB should be sized just like any other database. One should know their workload, or at least monitor the TempDB over several weeks to see what is a healthy size for it. Once this is determined, you then can set the size of the TempDB, so that when the server is restarted, it will be at the correct size, thereby having to use "auto grow".
+1
July 19, 2013 at 7:28 pm
kevaburg (7/19/2013)
Steve-3_5_7_9 (7/10/2013)
kevaburg (7/9/2013)
There is nothing to worry about. TempDB is recreated each time the instance/server is restarted and it will consume however much space it requires to perform its function. The fact it is so small by a restart is nothing to be concerned about as (by the very nature of the file itself) the data contained within is temporary.It absolutely is something to be concerned about. You want to have TempDB properly sized, so in this case the OP would want to size it at 2GB or higher. If you allow TempDB to "autogrow" you will most likely have performance issues as the TempDB file(s) grow.
Steve
This is something that could become very contentious. Why would growth on the TEMPDB be a problem if the database itself is properly sized (the DB determines that itself) and growth rate is properly set?
This, from my point of view, only becomes a problem if the database server is restarted on a regular basis. The people that insist on restarting a Windows Server / Database Server make this a problem all by themselves.
I don't think the problem is in answering the question "why doesn't it maintain the settings I give it" rather "why was the instance restarted in the first place?"
Because TEMPDB is recreated each time the instance is started, it must also be populated each time the instance starts with the data the needs to be loaded. Resizing (if done properly) is not a huge issue in SQL Server although I am not taking away the fact that it requires resources to perform. The most intensive operation here is repopulating the database so it can operate in the way it should. This is the equivalent of restarting an Oracle database and wondering why each initial query execution is slower than normal simply because the shared pool has been flushed.
Anyway, that is why I believe it isn't a problem.....
(Sorry the answer took so long.....hoilday has a somewhat higher priority!)
I guess I don't understand what you're talking about when you speak of "repopulating" TempDB. TempDB requires no repopulation on restart. Some things in the system do use it once TempDB has been restarted but it doesn't require "repopulation" in any classic sense. Allocating the space for it takes no time if you have instant allocation enabled. And, TempDB has nothing to do with "shared pool" stuff. That would memory/cache where data and execution plans are loaded and, hopefully, reused. TempDB has little to do with any of that.
Perhaps when you're speaking of "repopulating 'the DB'", you're not talking about TempDB but are talking about some other data DB?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2013 at 7:51 am
Hi,
There are some existing recommendations for the tempdb files. These two are most advised:
- If the server has more than 8 cores then start with 8 tempdb files; or
- The number of tempdb files should be 1/4 to 1/2 of the number of cores.
For the "proportional fill" algorithm it's vital the sizes and growths be equal for all files.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply