Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TEMPDB is growing to 400GB Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 12:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:52 AM
Points: 57, Visits: 58
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?
Post #1502755
Posted Tuesday, October 8, 2013 1:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:53 AM
Points: 597, Visits: 937
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
Post #1502789
Posted Tuesday, October 8, 2013 11:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 29, 2014 12:35 PM
Points: 4, Visits: 67
Check for any active transactions which is using Temp db .


Cheers

Shamanth
Post #1502921
Posted Wednesday, October 9, 2013 6:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
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
Post #1503062
Posted Wednesday, October 9, 2013 6:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1503065
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse