January 27, 2005 at 12:33 pm
I have an database from an accounting application that is approximately 55 GB. The problem is that the software allows users to create reports and queries on the fly. The issue is that tempdb is sometimes growing out of control during production hours and I need to figure out who is doing it.
The first time this happened, tempdb was set to grow unrestricted and it grew to > 200 GB and filled the drive. The application itself writes logs of what is going on and we were able to track it down to a new hire who obviously knew enough to be dangerous. The query was something like "select * from hugeTableA, hugeTableB where 1 = 1" .
So, I set the maximum size of tempdb first to 20GB and set up an alert if tempdb ran out of space. It filled up again. We couldn't figure out what the problem was so I increased to 40GB. It happened again this week, but no obvious clue as to what happened.
I would like to set up some kind of monitoring of tempdb space used, but I would like to know if there is a way to connect objects in tempdb back to spid's. Or if anyone else has ideas on what I need to look for, I would appreciate it!
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 27, 2005 at 12:59 pm
Kathi,
Sp_lock will tell you the locks present in your databases including tempdb. There is another sp called sp_lock2 (I think I got it from microsoft) it is essentially the same procedure but gives you usernames and tablenames. You can modify it to pass in a database name to see the current locks within a specific database.
Let me know if you can't find sp_lock2 and I can get it for you.
Tom
January 27, 2005 at 1:29 pm
I don't have sp_lock2.
So, I should monitor the space used in tempdb and if it reaches a certain percent start saving the results of sp_lock in a table and send an alert. Thanks for the idea, I'll give it a try.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 27, 2005 at 2:01 pm
I got sp_lock2 from the microsoft support site.
I would monitor it all the time and see what processes/users are creating and using tempdb that much.
Tom
January 27, 2005 at 2:02 pm
I seem to remember a configuration setting that sets the maximum query cost.
I'm at home at the moment so don't have access to BOL or a server to tell you exactly what it is but I'm pretty sure it does exist.
January 27, 2005 at 2:11 pm
I have to be careful not to harm any of the legitimate work, like creating invoices so the firm gets money and I get paid, with whatever I do. Some of the real work queries take hours to run. I'll try the monitoring first and see if I can pinpoint the bad stuff.
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 28, 2005 at 6:26 am
You don't mention whether the application is home-grown or a vendor supplied package ? If it's a vendor suplied package have you tried to contact their helpdesk line posing the question about the unbridled tempdb growth ? If it's a home-grown application, maybe a conversation with the system architect or a key developer might be in order. In the past I've found both avenues to be very helpful.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 28, 2005 at 8:30 am
It is a packaged application, a "premier" accounting package for law firms. Thanks.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 28, 2005 at 12:42 pm
Can it be the case that your logic requires to use a lot of temp tables? Make sure they are being dropped at the end of the stored proc. Also you may want to create 2 or 3 temp files x 10gb each(put a cap on them).
January 28, 2005 at 1:57 pm
Since it is a packaged app, I don't have any control over how tempdb is used. I'm thinking that I'll have to add another tempdb file, but in it won't solve the problem in the long run. I really need to just identify who is causing the problem, then the accounting IS staff will make sure the individual gets some additional training.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 31, 2005 at 1:53 am
I work with a variety of web CMSs and one of the things they share in common is a config file that allows me to switch their own internal logging on.
I was wondering if your packaged app had something similar.
Although my CMSs have an official set of values that can be used for logging during a recent support call their tech guys detailed some hidden config settings that are used purely for debug and support purposes.
It may be worth asking your vendor whether such facilities exist in you app. Make sure you don't ask a salesman.
February 20, 2005 at 12:41 pm
Can you run SQL Server Profiler and capture all those queries that gets fired against the entire server, may be that shall provide you some insight, saying which all SQL could be the culprit.
paul
February 20, 2005 at 1:38 pm
I have the lock monitoring set up and so far no tempdb problems since it was set up. The application itself has logging of all statements, so if I know approximately when the problem started I can find the statements. By using the lock monitoring, I will be able to pinpoint the time and therefore the ugly query.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply