July 30, 2009 at 11:29 am
Can some one answer the below questins
Assume that I have the below problem
My tempdb log file is full and I need to troubleshoot...
I tried the below optins
1. Tried to Shrink the log file no solution -- May be some open tran.. and killed and those are in roll back state...
2. Ran Backup log tempdb with truncate_only -- No use...
some times it works some times it does not work....
Is there any way to increase the tempdb log size, I know that If I alter the tempdb it will not be effected until you stop and start the SQL Server.
I am getting confusion on working tempdb issue, I do not why people ask this question often even though they are not facing this issue....
If you are being asked
Your tempdb is full and you need to resolve it and you should not reboot the SQL Server what is your answer?
Rajesh Kasturi
July 30, 2009 at 11:31 am
My solution would depend on whether the disk is full or not, at the very least.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2009 at 11:37 am
i agree with GSquared;
if the disk is NOT full, that implies someone might have put a hard limit on the max size of tempdb; you'd need to allow it to autogrow.
After that, start diagnosing from there: checking for open transactions (DBCC OPENTRAN i think) and killing whatever spid might be making the temp log grow to a crazy size.
Lowell
July 30, 2009 at 12:30 pm
And then a thorough code review (we're talking proctology exam) to determine if there's one procedure with a problem or if there's a systemic issue with all the code being written by the dev team. It's seldom a single thing that fills the tempdb. It's usually a bunch of badly written or designed procedures that are causing the issue.
----------------------------------------------------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
July 30, 2009 at 12:48 pm
I have had to add an additional file to the tempdb database. I created the file on another disk with more room. When the data modification was over remove the file from tempdb.
July 30, 2009 at 7:55 pm
Rajesh kasturi (7/30/2009)
I do not why people ask this question often even though they are not facing this issue....
It's a very typical question asked of people with the words "SQL Server DBA, MCITP - Database Administrator" on their resume during an inteview. They want to know that when it happens, the person being interviewed will actually be able to handle it.
If you are being asked
Your tempdb is full and you need to resolve it and you should not reboot the SQL Server what is your answer?
The first two words of the answer would be "It Depends..." followed by a very well thought out answer of not only how to solve that problem, but what steps you'll take to prevent it in the future because the interview depends on it.
I am getting confusion on working tempdb issue...
Here... let me Google that for you...
Apologies for the bit of sarcasm, but if you're going to advertise yourself as an "SQL Server DBA" with an "MCITP - Database Administrator" kicker, you'd better get used to correctly answering questions like that. To more succinctly answer your question about why people ask such a question, every DBA needs to know the answer to questions like that mostly off the top of their head because such problems WILL happen on your watch and you WILL be expected to fix it... quickly. If you haven't made your "DBA S-H Blackbook" up and put it on a memory fob yet, the correct solution and procedure to this problem would be a dandy first addition to it. I recommend you actually practice the problem several times. 😉
--Jeff Moden
July 31, 2009 at 12:59 am
Jeff,
I knew some ways to get rid of tempdb issue.
Why I posted the question is,
Who expects reply to this quesion.
I agree with you, I am still learning SQL Server DBA, I do not have much experience.
I apologies for putting MCITP, I removed it.
I appreciate your comment Today I learned some thing....
Rajesh Kasturi
July 31, 2009 at 5:37 am
Rajesh kasturi (7/31/2009)
Jeff,I knew some ways to get rid of tempdb issue.
Why I posted the question is,
Who expects reply to this quesion.
I agree with you, I am still learning SQL Server DBA, I do not have much experience.
I apologies for putting MCITP, I removed it.
I appreciate your comment Today I learned some thing....
As errors go, it's one that you run into on occasion, so it's not that unusual a question to ask. Understanding that the first response is "it depends" but then being able to walk through various scenarios just demonstrates an understanding of how SQL Server works and mechanisms for manipulation to dig yourself out of a hole.
----------------------------------------------------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
July 31, 2009 at 6:19 am
Rajesh kasturi (7/31/2009)
Jeff,I knew some ways to get rid of tempdb issue.
Why I posted the question is,
Who expects reply to this quesion.
I agree with you, I am still learning SQL Server DBA, I do not have much experience.
I apologies for putting MCITP, I removed it.
I appreciate your comment Today I learned some thing....
Wow! Absolutely not the response I expected. This thread started out like threads from a couple of other individuals who turned out to be, well... let's just say I'm very happy I don't have to work with them.
That is definitely not the case here. Not that you need to impress me but I have to tell you, I'm impressed. Well done and good form, Rajesh. I look forward to working with you in the future even if it's only on this forum.
--Jeff Moden
July 31, 2009 at 6:38 am
Now that I've at least made the attempt to extract the tennis shoe laces from my mouth, Grant hit the nail on the head. TempDB is one of the more important resources that an SQL Server system has. Nearly everything uses it in one form or another as a working area especially when a task cannot be resolved within the confines of memory.
One of the main catastrophe's that can occur is when some new or ad-hoc code is executed and it has a scalability problem like a purposeful triangular join (half a cross join) or an accidental cross join (Cartesian Product)... in the presence of a large number of source rows, it can and will cause a "runaway" growth of TempDB. It can happen even in a locked down system where Developers are not allowed to promote their own code because people don't always test for such scalability issues either because there's a time crunch or the Development servers simply don't have enough data to test with.
Also, internal code generators such as Hibernate and NHibernate can produce code that will be horribly resource intensive, as well. We just had a situtation where a Developer wrote some code that did an "eager GET" (get's all the data required in a single monster query) against 32 tables... it worked fine and fast on the test system. As soon as they promoted it to production, WHAM!... TempDB went nuts and performance tanked.
There are certainly ways to help prevent this from happening in the future... as you can see, just locking down a system isn't enough. In the absence of large volumes of test data, only educating the developers and testers to practice one of Grant's specialties of analyzing the query execution for abberant row counts and other problems will help prevent such problems in the future.
There's a whole lot more about the use, woes, and apparent fraility of TempDB... stopping runaway and preventing unnecessary large growth while still allowing the proper use of this most important resource is something that should appear in every interview and on every DBA test there is.
--Jeff Moden
July 31, 2009 at 8:38 am
I apologies for putting MCITP, I removed it.
So have you genuinely earned the MCITP qualification or are you just another committing the crime of deception?
It may make someone feel grand putting these letters after their name but it will seriously harm a career in IT if it is not genuine.
July 31, 2009 at 8:48 am
Rajesh kasturi (7/31/2009)
I apologies for putting MCITP, I removed it.
Nothing wrong with having the cert listed in your Sig if you've earned it. I used to have all mine listed. Just be aware that people will assume a certain amount of knowledge if you're listing certifications and hence will react like Jeff did if you ask very beginner questions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2009 at 10:04 am
I strongly agree with what Gail said. I'll also ask folks to consider the following... there are some extremely heavy hitters who have certs out the whazoo and some, like Gail, Grant, Peso, and a couple of other extremely knowledgeable folks have also earned (unlike some that we've seen)the Microsoft MVP award. Yet, they don't list such things in their signature lines... they'd rather let their work speak for them and speak it does! Volumes!
On the flip side, we've seen more than our fair share of people with enough letters after their names to start a dictionary and a good number of them have demonstrated little in the area of knowledge. Some have also demonstrated an extreme amount of arrogance even as they were demonstrating their ignorance.
Knocking rings is fine but I'd rather see what folks actually know. To me, titles mean nothing... only talent does.
I've still got a few shoe laces to get done chewing on so I'll get off this soapbox now. 😛
--Jeff Moden
August 1, 2009 at 8:13 am
Thanks...
Rajesh Kasturi
August 1, 2009 at 8:18 am
P Jones (7/31/2009)
I apologies for putting MCITP, I removed it.
So have you genuinely earned the MCITP qualification or are you just another committing the crime of deception?
It may make someone feel grand putting these letters after their name but it will seriously harm a career in IT if it is not genuine.
I am genuinely certified person with MCITP...If you want I would share my Microsoft Transcript offline...
Rajesh Kasturi
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply