Tempdb is suddenly growing

Last Post 30 Jun 2010 05:39 AM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Andy.L
New Member
New Member

--
29 Jun 2010 11:05 PM
Hi
I'm having problems with a tempdb on my SQL Cluster. I have SQL cluster with 2 nodes in an active/passive configuration. Each node has 24 cores.
I've configured tempdb to use 24 datafiles, each with a initial size of 1535mb (giving us a total of 36gn for tempdb). But at an regular interval tempdb begins to grow and I get an notification about running out of disk capasity on the volumes hosting tempdb. Luckally this is located on our SAN, but anyway this is not right.
What is the best way to find out WHY my tempdb is beginning to suddenly grow. I suspect that there's a job that is run that causes this. .

/Andy.l
gunneyk
New Member
New Member

--
30 Jun 2010 05:39 AM
Andy,

First off 24 data files is probably way over kill and you are most likely hurting performance more than helping at that point. The way to see how many you need to is monitor the PAGELATCH_UP waits and if you have a lot of them in tempdb then you may need mroe files. But You might want to think about reducing the number and monitor the wait stats for a while. As for what is using tempdb this white paper has tons of good info on tempdb and how to see what is using it.

http://technet.microsoft.com/en-us/...66545.aspx

Have you checked for open transactions in tempdb? Is this the data or log file that is growing? What version and service pack are you running? There was an older bug that prevented checkpoints in tempdb. Try running a checkpoint in tempdb and see if that works. Here is another link you may find of interest:

http://sqlblog.com/blogs/linchi_she...owing.aspx
You are not authorized to post a reply.

Acceptable Use Policy