| Author |
Topic |
|
anniech2000
Starting Member
12 Posts |
 Posted - 2007-05-17 : 05:46:33
|
| 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
11752 Posts |
Posted - 2007-05-17 : 05:53:33
|
| 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 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
anniech2000
Starting Member
12 Posts |
Posted - 2007-05-17 : 06:23:39
|
| 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. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-17 : 10:00:17
|
| Is data file or log file getting bigger? |
|
|
|
anniech2000
Starting Member
12 Posts |
Posted - 2007-05-17 : 21:20:45
|
| Data file expands to 15GB, while Log file is still at XX MB. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
anniech2000
Starting Member
12 Posts |
Posted - 2007-05-18 : 05:28:53
|
| 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? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-18 : 05:36:23
|
| 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 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-18 : 11:55:23
|
| Did you enable row versioning? It uses lot of tempdb space. Also look for long running queries on the server. |
|
|
|
anniech2000
Starting Member
12 Posts |
Posted - 2007-05-21 : 00:17:51
|
| 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. |
|
|
|
anniech2000
Starting Member
12 Posts |
Posted - 2007-05-25 : 04:55:52
|
| 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! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-25 : 05:00:04
|
| are you returning a lot of data to the client?what does this query do?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 06:59:02
|
| Cursor maybe?? |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-05-25 : 09:17:22
|
| i used this to fixed the same problem.BACKUP LOG <database_name> WITH TRUNCATE_ONLYGO |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 12:44:19
|
| I don't think that will work on TEMPDB ... |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2007-05-25 : 13:59:19
|
| Sure it does! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-25 : 14:15:40
|
| 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 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 14:31:09
|
| "Sure it does!"I took my answer from here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434#308532but 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 |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2007-05-25 : 14:38:27
|
| 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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 14:43:28
|
| Sounds like a hole that you can get a coach-and-four-horses through then!! |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2007-05-25 : 14:48:23
|
| Giddeup! |
|
|
|
Next Page
|
tempdb expands to full harddisk size suddenly