marrowyung
asked on
tempDB keep growthing after the deployment of SQL server 2008 SP2
Dear expertist,
After we deploy SQL server 2008 SP2, we start to receive more and more incident about the out of space of the TEMPDB drive.
May I know if anyone tried this kind of experience? any fix needs ?
As we can't restart the SQL server in order to free disk space up, any other suggestion? session of tempdB hard to kill as user is using it.
DBA100.
After we deploy SQL server 2008 SP2, we start to receive more and more incident about the out of space of the TEMPDB drive.
May I know if anyone tried this kind of experience? any fix needs ?
As we can't restart the SQL server in order to free disk space up, any other suggestion? session of tempdB hard to kill as user is using it.
DBA100.
Tempdb on c:-drive of your server and there is no place?
You can change the db-location of the tempdb and after next sqlserver shutdown it will be recreated on that new location.
"After deploy"
Then high tempdb usage also can be a symptom of other problems like not optimal maintenance plans or testing the maintenance plans on bad moments. Initial uploads from data, performance tests ....
You can change the db-location of the tempdb and after next sqlserver shutdown it will be recreated on that new location.
"After deploy"
Then high tempdb usage also can be a symptom of other problems like not optimal maintenance plans or testing the maintenance plans on bad moments. Initial uploads from data, performance tests ....
<<May I know if anyone tried this kind of experience? any fix needs ?>>
If your tempdb grows, you need to provision space accordingly. The application needs it. What size are we tlking about ?
If your tempdb grows, you need to provision space accordingly. The application needs it. What size are we tlking about ?
Usually, a TEMPDB database size should be around 1/10 up to 1/2 of the size of the main application running on the same server based on the application usage of TEMPDB.
ASKER
jogos:
"Tempdb on c:-drive of your server and there is no place?
You can change the db-location of the tempdb and after next sqlserver shutdown it will be recreated on that new location.
"After deploy"
Then high tempdb usage also can be a symptom of other problems like not optimal maintenance plans or testing the maintenance plans on bad moments. Initial uploads from data, performance tests ....
"
We do it on a separated volume already for 2 year until recently! we just think it only happens after the deployment of SQL server 2008 SP2. As before we are doing SP1, we don't have so much incident about that.
That's why I am asking if this is a known issue of SP2. Any idea? What SP have you deployed?
"Tempdb on c:-drive of your server and there is no place?
You can change the db-location of the tempdb and after next sqlserver shutdown it will be recreated on that new location.
"After deploy"
Then high tempdb usage also can be a symptom of other problems like not optimal maintenance plans or testing the maintenance plans on bad moments. Initial uploads from data, performance tests ....
"
We do it on a separated volume already for 2 year until recently! we just think it only happens after the deployment of SQL server 2008 SP2. As before we are doing SP1, we don't have so much incident about that.
That's why I am asking if this is a known issue of SP2. Any idea? What SP have you deployed?
ASKER
Racimo:
"<<May I know if anyone tried this kind of experience? any fix needs ?>>
If your tempdb grows, you need to provision space accordingly. The application needs it. What size are we tlking about ? "
As I told jogo,we just think it only happens after the deployment of SQL server 2008 SP2. As before we are doing SP1, we don't have so incident about that.
I knew we need to provision space accordingly, but it will be too late for that as we only have 10MB space left and adding disk can be serveral disk. The size already serveral GB for tempdB only.
"Usually, a TEMPDB database size should be around 1/10 up to 1/2 of the size of the main application running on the same server based on the application usage of TEMPDB. "
what you mean the size of the main application running means ? I don't underand what is 1/2 of it .
we use script/DMV to find out the size the internal or user object using on tempdB and we know the session id of it and we can killl it.
But one thing, this company is operating in 23x7 manner can we can't even restart it or failover to other nodes. we are already doing more than that.
So for me, I just guess it probably the effect of SP2 as SP1 dont' give it evne application keep updating it.
DBA100.
"<<May I know if anyone tried this kind of experience? any fix needs ?>>
If your tempdb grows, you need to provision space accordingly. The application needs it. What size are we tlking about ? "
As I told jogo,we just think it only happens after the deployment of SQL server 2008 SP2. As before we are doing SP1, we don't have so incident about that.
I knew we need to provision space accordingly, but it will be too late for that as we only have 10MB space left and adding disk can be serveral disk. The size already serveral GB for tempdB only.
"Usually, a TEMPDB database size should be around 1/10 up to 1/2 of the size of the main application running on the same server based on the application usage of TEMPDB. "
what you mean the size of the main application running means ? I don't underand what is 1/2 of it .
we use script/DMV to find out the size the internal or user object using on tempdB and we know the session id of it and we can killl it.
But one thing, this company is operating in 23x7 manner can we can't even restart it or failover to other nodes. we are already doing more than that.
So for me, I just guess it probably the effect of SP2 as SP1 dont' give it evne application keep updating it.
DBA100.
>> That's why I am asking if this is a known issue of SP2. Any idea? What SP have you deployed?
>> So for me, I just guess it probably the effect of SP2 as SP1 dont' give it evne application keep updating it.
Applying SP2 would not have created this issue.
Kindly check for any code level fixes or patches applied to your Server after the time you have applied SP2.
>> So for me, I just guess it probably the effect of SP2 as SP1 dont' give it evne application keep updating it.
Applying SP2 would not have created this issue.
Kindly check for any code level fixes or patches applied to your Server after the time you have applied SP2.
ASKER
rrjegan17:
you mean CU udpate?
DBA100.
you mean CU udpate?
DBA100.
ASKER
the messag we got is:
Error 802: [Microsoft] [ODBC SQL Server Driver] [SQL Server] There is insufficient memory available in the buffer pool.
[Microsoft][ODBC SQL Server Driver] [SQL Server] Check terminated. A failure was detected while collecting facts. Possibly tempdb out of spce or a system table is inconsistent.
Any similiar experience on how to solve it without restart/failover to other nodes.
DBA100.
Error 802: [Microsoft] [ODBC SQL Server Driver] [SQL Server] There is insufficient memory available in the buffer pool.
[Microsoft][ODBC SQL Server Driver] [SQL Server] Check terminated. A failure was detected while collecting facts. Possibly tempdb out of spce or a system table is inconsistent.
Any similiar experience on how to solve it without restart/failover to other nodes.
DBA100.
Check your memomry configurations http://msdn.microsoft.com/en-us/library/aa337354.aspx.
And did you check if the tempdb can grow , that is space availlable on disk and not limited in its definition?
Did you check if your maintance is still running (full backup, transaction log backup, index, ....)
Can you do CHECKDB? If it's realy 'system table is inconsistent' you have to take that on.
And did you check if the tempdb can grow , that is space availlable on disk and not limited in its definition?
Did you check if your maintance is still running (full backup, transaction log backup, index, ....)
Can you do CHECKDB? If it's realy 'system table is inconsistent' you have to take that on.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And any clue if there are if there are (alsways smae) heavy things bussy on that moment?
If your tempdb cannot grow you because of disk size, you always can add another datafile.
If your tempdb cannot grow you because of disk size, you always can add another datafile.
ASKER
"And any clue if there are if there are (alsways smae) heavy things bussy on that moment?
"
did anyone here keep repeating before?
That DB are gbusy all the time all the time ... we are running 23x7 and we are not allowed to stop DB.
yes we can add one more disk but we prefer to extend the current disk as it involve $$ and time, which is not what we want.. I already rise this request.
"
did anyone here keep repeating before?
That DB are gbusy all the time all the time ... we are running 23x7 and we are not allowed to stop DB.
yes we can add one more disk but we prefer to extend the current disk as it involve $$ and time, which is not what we want.. I already rise this request.
I heard your server is always bussy. But we are looking for the source of your problem, this can be basicly 5 things
- memory configuration -> no response from you
- tempdb is limited (by lack of diskspace to extend or by definition) -> no response from you on if you realy are out of disk or there is a size limit on your tempdb (or encrypted see below)
- ' or a system table is inconsistent.' so do checkdb -> no response
- does your maintenance run properly -> no response
And on the when? So is there a specific job that can be tuned
- is there a specific heavy job that always is busy on the moment you get the error --> your response 'yes my server is 23/7 busy' that's on most locations but is not saying anything about the circumstances of the error
Size of tempdb (and memory) is correlated to the size of data you process, so many concurrent users, large databases , large tables, sorts, temp tables .... the all play a role.
So it's worth to preallocate sufficient space for your tempdb and depending on how heavy you stress your tempdb it wlll be 1/10 to 1/2 of your main database.
Your "yes we can add one more disk but we prefer to extend the current disk as it involve $$ and time, which is not what we want..."
Is this saying 'yes our tempdb cannot grow because of diskspace?' Well know that I don't get $ for my time here and and I like to help people but when problem is located our time is up.
- memory configuration -> no response from you
- tempdb is limited (by lack of diskspace to extend or by definition) -> no response from you on if you realy are out of disk or there is a size limit on your tempdb (or encrypted see below)
- ' or a system table is inconsistent.' so do checkdb -> no response
- does your maintenance run properly -> no response
And on the when? So is there a specific job that can be tuned
- is there a specific heavy job that always is busy on the moment you get the error --> your response 'yes my server is 23/7 busy' that's on most locations but is not saying anything about the circumstances of the error
Size of tempdb (and memory) is correlated to the size of data you process, so many concurrent users, large databases , large tables, sorts, temp tables .... the all play a role.
So it's worth to preallocate sufficient space for your tempdb and depending on how heavy you stress your tempdb it wlll be 1/10 to 1/2 of your main database.
Your "yes we can add one more disk but we prefer to extend the current disk as it involve $$ and time, which is not what we want..."
Is this saying 'yes our tempdb cannot grow because of diskspace?' Well know that I don't get $ for my time here and and I like to help people but when problem is located our time is up.
we use script/DMV to find out the size the internal or user object using on tempdB and we know the session id of it and we can killl it.I hope you know what that session is doing. But this (hithout the killing) is the way to find heavy jobs, jobs that can be loocked after if the can be made using less resources without loosing functionality (filter earlier, unneeded sorts/distinct, select only needed columns not select * ,less data in temptables, delete temptables, use transactions , .....).
And don't forget with your DMV's its also easy to find missing indexes, statistics, io or lock bottlenecks ... and any session that spends more time processing keeps also it's space in tempdb longer allocated.
<<what you mean the size of the main application running means ? I don't underand what is 1/2 of it .>>
I mean up to half the total size of the database files used by the main application. That occurs for instance when row versioning is activated.
I mean up to half the total size of the database files used by the main application. That occurs for instance when row versioning is activated.
http://msdn.microsoft.com/en-us/library/ms345368.aspx
You can find some troubleshooting tips on why tempdb is growing very fast here:
http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html
And some useful tips regarding tempdb configuration here:
http://www.johnsansom.com/sql-server-resources/sql-server-performance/the-tempdb-database/