SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 tempdb expands to full harddisk size suddenly
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

anniech2000
Starting Member

12 Posts

Posted - 05/17/2007 :  05:46:33  Show Profile  Reply with Quote
My production DB size is 2.6 GB but tempdb expands to 15GB (full harddisk size) suddenly. Any temp table or resultset would not have cause such usage, unless multiple users use them simultaneously. Is it most probably from un-committed transactions causing such a huge size build-up?

Is there any method to trace which transaction is causing this problem? I used SQL Server Profile Trace but it generates a 5MB log file in 5 minutes, so it cannot be used in 1 or 2 days as it will use up much harddisk space. Anyone can help? Thanks a lot!

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/17/2007 :  05:53:33  Show Profile  Visit spirit1's Homepage  Reply with Quote
are you running any administrative work like rebuilding indexes?
you can see open (unciommited) transactions in the activity monitor for each spid.

You can filter the events in the profiler. that will help with size.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

anniech2000
Starting Member

12 Posts

Posted - 05/17/2007 :  06:23:39  Show Profile  Reply with Quote
Thanks for your prompt reply.

We have some scheulde to backup transaction logs from databases which I am not sure is related to the problem or not. For transactions, I do not see any abnormal open transactions in activity monitor. There are open transactions, but are normal as usual. For profiler, I've just added "SQL:BatchCompleted", "StoredProcedure:Completed" and "File Auto Expand" to trace which transaction causing the problem, but the first event already occupy a lot of space. Any idea how to improve?

Thanks again.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/17/2007 :  10:00:17  Show Profile  Reply with Quote
Is data file or log file getting bigger?
Go to Top of Page

anniech2000
Starting Member

12 Posts

Posted - 05/17/2007 :  21:20:45  Show Profile  Reply with Quote
Data file expands to 15GB, while Log file is still at XX MB.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 05/17/2007 :  22:44:22  Show Profile  Reply with Quote
well, I would say that a likely candidate for out of control tempdb growth is a cartesion join somewhere. I have had to track this type of thing down in the past and Brian Moran has an article online that I used to help figure out what was going on. Check it out http://www.simple-talk.com/sql/database-administration/tracking-tempdb-growth/


-ec

Go to Top of Page

anniech2000
Starting Member

12 Posts

Posted - 05/18/2007 :  05:28:53  Show Profile  Reply with Quote
Thanks eyechart. I have followed the article and trace the tempdb growth by SQL Profiler Trace. However, as mentioned, it uses up many space in my harddisk and down-grade my SQL server performance so I cannot turn it on for a long time. Sorry I haven't used it before and I am afraid it will adversely affect the production server. How can I use it efficiently?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/18/2007 :  05:36:23  Show Profile  Visit spirit1's Homepage  Reply with Quote
run it on a different server, save trace files into the table on the other server.
that's the best you can do to easy the burden on the main server.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/18/2007 :  11:55:23  Show Profile  Reply with Quote
Did you enable row versioning? It uses lot of tempdb space. Also look for long running queries on the server.
Go to Top of Page

anniech2000
Starting Member

12 Posts

Posted - 05/21/2007 :  00:17:51  Show Profile  Reply with Quote
Thanks for all. I'm now saving the trace in separate harddisk and cut if from time to time.
For rmiao's question, I'm using SQL server 2000 and there should be no row versioning in my understanding.
Hope that I can find out some queries related to the growth.
Go to Top of Page

anniech2000
Starting Member

12 Posts

Posted - 05/25/2007 :  04:55:52  Show Profile  Reply with Quote
Having run the SQL trace for a few days, I found that a RPC (remote procedure call) of long duration seems to have cause the tempdb expansion. Running of the query is a short one but the calling of it spends ~9min and tempdb expands simultaneously with the same SPID. How can I find out the reason of such an abnoral RPC? Thanks in advance!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/25/2007 :  05:00:04  Show Profile  Visit spirit1's Homepage  Reply with Quote
are you returning a lot of data to the client?
what does this query do?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/25/2007 :  06:59:02  Show Profile  Reply with Quote
Cursor maybe??
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 05/25/2007 :  09:17:22  Show Profile  Visit funketekun's Homepage  Reply with Quote
i used this to fixed the same problem.

BACKUP LOG <database_name> WITH TRUNCATE_ONLY
GO
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/25/2007 :  12:44:19  Show Profile  Reply with Quote
I don't think that will work on TEMPDB ...
Go to Top of Page

DMcCallie
Yak Posting Veteran

USA
62 Posts

Posted - 05/25/2007 :  13:59:19  Show Profile  Reply with Quote
Sure it does!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/25/2007 :  14:15:40  Show Profile  Visit spirit1's Homepage  Reply with Quote
even thougth it does work that's not a solution that you can be happy with.
because your tempdb will grow again if the real problem isn't found.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/25/2007 :  14:31:09  Show Profile  Reply with Quote
"Sure it does!"

I took my answer from here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434#308532

but I haven't verified it for myself. Sorry if its wrong.

Thinking some more ... if TEMPDB hits a CheckPoint isn't it going to truncate anyway? (and if it doesn't is "backup log Tempdb with truncate_only" going to find any work to do?)


Kristen
Go to Top of Page

DMcCallie
Yak Posting Veteran

USA
62 Posts

Posted - 05/25/2007 :  14:38:27  Show Profile  Reply with Quote
I have one test server that has this problem about twice a year. When I receive an alert that tempdb log is 90% full I just go and truncate it with the command in question. It works and I haven't had a problem with it yet. If this was a production server I might investigate further...

Your are correct in your logic Kristen.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/25/2007 :  14:43:28  Show Profile  Reply with Quote
Sounds like a hole that you can get a coach-and-four-horses through then!!
Go to Top of Page

DMcCallie
Yak Posting Veteran

USA
62 Posts

Posted - 05/25/2007 :  14:48:23  Show Profile  Reply with Quote
Giddeup!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000