Tempdb grow

Question

Tempdb grow

Akkare

SSCrazy

Points: 2848
More actions
January 16, 2012 at 8:24 am

#249624

Hi
I have sql server 2008 server with sp2.I am facing tempdb issue on this instance
every day tempdb is growing to 80 GB.could someone help me to find out the statements which is using more tempdb and give some recommendation.
Regards,
Akkare

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Set up a server-side trace to monitor tempdb data file growth events as well as general server activity and see whether there's anything obvious that's happening every time the file grows. This won't work for certain, since there are so many things that use tempdb, and because the file may be growing due to one operation rather than many spread over time. However, it's a good place to start.

John

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Further to this, use perfmon and the LogicalDisk: % free space counter as well. If this happens daily you should be able to see a clear spike or upward curve and this can help you when going through your trace files to see what transactions are causing it.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

See this script:

http://www.sqlservercentral.com/scripts/tempdb/72007/

It should help you identifying the culprit.

-- Gianluca Sartori

Eric Hu²º¹² Mr or Mrs. 500 Points: 506 More actions · Answer 4

Move the tempdb on a separate server.

--The confidence is a premise, the courage is a motive, the perseverance is assurance!
http://www.cnblogs.com/huyong/[/url]

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

Eric Hu²º¹² (1/17/2012)
Move the tempdb on a separate server.

Eric, you can't move TempDB to another server. It's required for SQL Server to be able to function. All you can do is identify what's causing TempDB growth and alternatively move it to a LUN with more available storage.

Shamless self promotion - read my blog http://sirsql.net

Eric Hu²º¹² Mr or Mrs. 500 Points: 506 More actions · Answer 6

Increase the file group.

--The confidence is a premise, the courage is a motive, the perseverance is assurance!
http://www.cnblogs.com/huyong/[/url]

Shivrudra SSCrazy Points: 2835 More actions · Answer 7

check is there any index rebuild job running daily

Regards,
Shivrudra W

Robert Murphy UK1 Default port Points: 1485 More actions · Answer 8

Eric Hu²º¹² (1/17/2012)
Increase the file group.

You can only have one filegroup for tempdb, you can increase the number of files for that filegroup which may increase performance. I think the issue here is not performance in this respect but discovering why tempdb is growing so rapidly during the day.

Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified