April 1, 2011 at 12:10 am
SQL Server 2005
Windows Server 2003
RAM 32 Gigs
We have a scheduled SQL JOB for updating the tables in 200 Gigs of Database. It was working fine from years. The step includes 5 steps of pulling data and Inserting , updating many tables. It is scheduled at 02:00 AM, it was fine till last month. Weired..! Now we are facing TempDB full, as suddenly increase to 150 gigs in an hour from the initial size 30gigs. Its getting increased to 120 gigs in 30 mins once it finished 3rd Step in the SQL Job.
where we have only solution of stopping SQL job and recycling SQL Server services to clear TempDB and recreate, as we are getting out of memory and even we see CPU 100% utilizing.
I wonder to see in sudden increase to 120 Gigs of huge size of TempDB.
Iam thinking of scheduling 3 jobs at different intervals, steps 1,2,3 in one, 4th step in job 2, 5th step in job 3. In between the job steps clearing of TempDB by shrinking.
But how long ? is there any better solution for this. This is the third week we are facing with it.
Every day night between 2 AM - 3 AM, we are seeing wonder on this server.
Ran profiler and found traces , however these are the same Procedures which we have from past 3 yrs. Data is not that much huge of updating, inserting or modifying. not more than a million records.
Please help me to sort this issue, suggestions please...!!
Thanks in advance...
- Win.
Cheers,
- Win.
" Have a great day "
April 1, 2011 at 12:43 am
Obviously something that is running requires using a lot of tempdb space, maybe from a large sort, or something. Why would you want to shrink the tempdb back down? If it needs to be that big leave it that big. (We have a 140 GB tempdb on one server to support some large operations.)
Have you looked at the execution plans of the procedures? Have you narrowed it down to one procedure? Are the table and/or indexes highly fragmented? Are the statistics out of date?
There isn't much we can do with what you have given us.
Can you post the code for the third step to give us an idea of what is going on? (Since you think it is a major part of the problem.)
April 1, 2011 at 1:10 am
we are shrinking back down as we are reaching out of space on that drive. and the DB files also rest on the same drive. So we have only one solution to make sure to not be in complications.
Iam stucked over there, not able to perform any action directly without approval... Thats bad situation we are in...
Indexes - we are scheduling it once a week. Statistcs we update regularly, we did all. But still.
Code - we haven't changed any in tha code from past 2 years , its the same code we are using from long ago..
Donno why this server started behaving this much weired...
ThX,
-Win.
Cheers,
- Win.
" Have a great day "
April 1, 2011 at 4:27 am
You need to identify which parts of the process are specifically hitting tempdb. That's step #1. Then you need to figure out what in those processes is causing tempdb to load up so much and then change that, whatever it might be. There's no way to know from here.
There is a good article at MSDN describing how to manage tempdb and observe which processes are using it. There is also a good one showing how to optimize your tempdb set up.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 1, 2011 at 4:52 am
Thank you Grant for your information.
The process of SQL Job steps hit tempdb at 3rd step and 4th step which inserts data into 35 tables, pulled from other source.
I tried checking execution plan as well. It shows the same tables where it is trying to load the data, bulk insert. It was fine till last month , from this 20 days it became a Star for me.
While inserting if I try Trace, the SQL Server not responding as its reaching to CPU 100%, TempDB full in 240 gigs of drive. It starts from 40 gigs and reaches 140gigs at 4th step and reaches 100gigs at 3 step. No handy on othere traces, so we are stopping the JOB and re-run once the TempDB reaches 90%.
All the ways looks me blind. Got hung in my mind, what to do. As, this is not the best practise and the worst thing I even never wish to do so.
Cheers,
- Win.
" Have a great day "
April 1, 2011 at 5:14 am
So you know the query that is causing the problem. What is that query doing? How is it hitting tempdb so hard? Can you get the actual execution plan and post it here? You've already narrowed it down. You just need to address the issues.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 1, 2011 at 6:23 am
Yes.
The step 3 and 4 has 19 storedprocedures, which pulls data from other source and which creates #temp tables and bulk inserts into my DB. Execution plan shows 8 tables as Index Scan. But we reIndex them very frequently.
Once the bulk insert step starts on this server automatically CPU starts increasing and the TempDB starts growing. If I try to connect or TS the server, it takes too long time connect. And some times it wont even connect or TS , when it reached 95% of CPU levels.
Asked Appl team to finetune the Procedure.
Can you suggest me, that what could be the necessary steps to be taken care even though its brought back with fine tune of procedures.
Cheers,
- Win.
" Have a great day "
April 1, 2011 at 6:48 am
- Win. (4/1/2011)
Yes.The step 3 and 4 has 19 storedprocedures, which pulls data from other source and which creates #temp tables and bulk inserts into my DB. Execution plan shows 8 tables as Index Scan. But we reIndex them very frequently.
Once the bulk insert step starts on this server automatically CPU starts increasing and the TempDB starts growing. If I try to connect or TS the server, it takes too long time connect. And some times it wont even connect or TS , when it reached 95% of CPU levels.
Asked Appl team to finetune the Procedure.
Can you suggest me, that what could be the necessary steps to be taken care even though its brought back with fine tune of procedures.
Without seeing the code, no, not really.
But if you're getting index scans, I'd be willing to be you're also getting hash joins, which builds tables in tempdb. Or, you might be seeing merge joins but with a sort operation, which builds tables in tempdb. Or, I wouldn't be surprised if you had one or more other operations, all probably hitting tempdb.
You might want to reexamine your load process. If it's loading everything to temp tables first, that can be the issue, right there. But if you then combine that with problematic queries that have to load stuff into tempdb, the problem just gets worse.
Then, on top of that, are your indexes in place for the data loads? If so, the indexes are resorting themselves out using tempdb. Again, adding to the overall load.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply