Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TEMPDB is growing to 400GB


TEMPDB is growing to 400GB

Author
Message
krishna.sathish07
krishna.sathish07
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 64
In our production server, tempdb is growing suddenly to 400GB (Earlier, it will reach maximum 210GB). There is no changes at server level or database level.

But, It is not happening in another server where the server configuration is same as production server.

Below the more information,

Server is SQL server 2008 R2 and this server always executing some stored procedures (15 sessions simultaneously) which will push data into web server.

Can someone help?
Keith Tate
Keith Tate
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 979
A couple of questions:
Is the growth due to the log file or the data file in TempDB?
Are you using Snapshot Isolation?
Have you checked for long running transactions?

What have you done so far to diagnosis the issue?

Once you find the issue you will have to look into a potential VLF issue too.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
shamantha4
shamantha4
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 69
Check for any active transactions which is using Temp db .


Cheers

Shamanth
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
index renuild / reorganize job or maintenance done with option (SORT_IN_TEMPDB = ON)
OR
ONLINE = ON
?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2883 Visits: 2235
There are multiple things to look for. In addition to above you can also check if tempdb is growing at the time of any maintenance?

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search