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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
tempdb database is a global resource for the entire instance of SQL Server and is used whenever the below object types are created in it..
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/
jogosCommented:
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  ....


 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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 ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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.
marrowyungSenior Technical architecture (Data)Author Commented:
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?

marrowyungSenior Technical architecture (Data)Author Commented:
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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.
marrowyungSenior Technical architecture (Data)Author Commented:
rrjegan17:

you mean CU udpate?

DBA100.
marrowyungSenior Technical architecture (Data)Author Commented:
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.
jogosCommented:
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> There is insufficient memory available in the buffer pool.

Can you let me know the below things to suggest a better value:

1. Min Server Memory of SQL Server
2. Max Server Memory of SQL Server
3. Physical Memory available in Server
4. Virtual Memory configured
5. Free space available in Drive where tempdb is available..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jogosCommented:
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.
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
jogosCommented:
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.
jogosCommented:
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.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.