November 10, 2008 at 9:40 am
We purchased an accounting software from small company in India. Every time there were a few reports when the users ran, it created a gigantic temp table and sometimes it caused an error 'TEMPDB is full'. There was no error in the log and there was no trace indicated something was terribly was wrong. The developer who was responsible for this application just restarted the window server. The application was running on window 2000 and using SQL Server 2000.
The recovery mode is set to simple. The tempdb is set to automatically grow by 10% and unrestricted file grow.
What can I do so I don't have to re-start the window server every time? He re-starts the server every week.
When the tempdb is set to unrestricted grow, does that mean it will grow up to the drive limit?
November 10, 2008 at 9:44 am
Yes, if set to unrestricted growth, it will grow until it fills the drive. You should also look at setting tempdb to grow a set amount instead of 10% each time. Each time it grows, it grows by a larger amount. This also means each time it grows, it takes longer.
November 10, 2008 at 9:46 am
Loner (11/10/2008)
The tempdb is set to automatically grow by 10% and unrestricted file grow.
What's the initial size? If you're using TempDB heavily, it must be changed from the default (2MB?) to something more reasonable. 10% is a dangerous growth as it means as the file grows larger the growth increments increase and those can strain the IO system (no instant initialisation on SQL 2000) and cause timeouts and other IO-related problems. I would suggest setting that to a fixed size. What size you set it to depends on the initial size of tempDB
What can I do so I don't have to re-start the window server every time? He re-starts the server every week.
Off hand, I don't know. Why is he restarting the server? Is tempDB filling the drive?
When the tempdb is set to unrestricted grow, does that mean it will grow up to the drive limit?
Yes.
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
November 10, 2008 at 9:51 am
Restarting the server weekly sounds lilke a case of really bad code.
Is there one procedure that is causing the issue?
November 10, 2008 at 11:25 am
According to the developer who is responsible for the application, he said the application generated a lot of temp table when running reports. The developer said it generated a lot of fragmentation of the disk which had the tempdb.
He thought it was best to restart the server to clear the fragmentation of the disk because it runs on window 2000 which I did not buy that.
I looked the original size is 40 MB
I did a sp_spaceused in tempdb
Database size = 80 MB unallocated space = 39.26 MB
Reserved = 760 KB
data = 288 KB
index page = 360 KB
unused = 112 KB
November 10, 2008 at 12:02 pm
80 MB is not large for TempDB. From the space used, it looks like it's only half used, which is fine. How big can it get?
If TempDB is sized properly and doesn't have to grow repeatedly, it won't fragment on disk
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
November 10, 2008 at 8:17 pm
Shoot... 80MB for TempDB??? I boot the servers at work with 9GB... I doubt that I would boot any server with less than a gig for TempDB. And, 10% growth rate is terrible... if the initial size is 1MB, it'll make 73 fragments just to get to 1GB.
Others have said it and I'll back them up on this one... rebooting the server to get TempDB back down from 80MB is very unneccessary to do. For this application, set it to at least 100MB and forget it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2008 at 5:41 am
Thanks everyone.
November 12, 2008 at 7:27 am
Oh....and look into shooting the developer 😀
November 12, 2008 at 6:11 pm
AndrewMurphy (11/12/2008)
Oh....and look into shooting the developer 😀
Um... for what? Using a paltry 40 MB of TempDB? Fragementing the disk because TempDB started out insanely small. Hell, I want to hire the developer that knows how to keep TempDB usage that small 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2008 at 6:07 am
That guy's title is Senior DBA/developer. Obviously he does not know anything about SQL Server administration and I am very much doubted he is a good SQL developer. He writes visual basic on the side. This is a classic case that the company wants to hire someone that can do everything !!!!!!
November 13, 2008 at 6:22 am
Loner (11/13/2008)
That guy's title is Senior DBA/developer. Obviously he does not know anything about SQL Server administration and I am very much doubted he is a good SQL developer. He writes visual basic on the side. This is a classic case that the company wants to hire someone that can do everything !!!!!!
Job titles do not really mean anything, I had to pick my current one and really could use anything I wanted. I also have to solve a lot of problems with our out-sourced projects and it shows that out-sourcing may seem like a cheaper option but can end up costing a lot more in the long-term.
November 13, 2008 at 6:34 pm
steveb (11/13/2008)
Boy oh boy! You just hit my favorite sore spot! 😛 Some dummies (we'll call them "high level management" ;)) at my previous job decided to outsource a rather large project to an over-seas company. They had 3 months to do it and that was supposed to be followed by 2 months of testin' and tweekin'. 3 months and a million dollars later, the GUI's looked like someone with a really bad twitch drew it on an Etch-a-Sketch, the database was a shambles, performance made flushing sounds, nothing met requirements, and there was no documentation in any of the code, database, or externally. They then turned around and told us salaried slobs that we had 2 months left to design, build, document, test, and tweek the project from womb-to-tomb. Then they scheduled 12 hour days just to make sure we got it done...
... I left the company...
... and they're still not done. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2008 at 6:05 am
Jeff
Don't even get me started with outsourcing. In my last company, I spent 4 days to finish a very complicated report. The outsourcing company had two developers working on the similar report and they spent 4 months working on it and could not even finish the query. They kindly asked if they could 'borrow' my code. I gave to them and still two months later, it was not done.
The application I was talking in this thread was developed by a company in India. Every time there is an issue, we submit a ticket and goes to never, never land. Is the company/people/culture so different in other part of the world ?;)
November 14, 2008 at 9:58 am
Jeff....
"Um... for what? Using a paltry 40 MB of TempDB?"
...if you read the original message...he was getting TEMPDB full....the only reason TEMPDB was being returned to 40MB was that was the initial size of the file....objective achieved totally by accident:D
I don't think he (or she) is the quality candidate for the next open position in your company....
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply