0

I arrived at work to today to discover one of our SQL 2005 servers had run out of disk space. On examination the database causing the problem was tempdb. It seems to have grown from around 8mb to 16gb, causing me some concern. After kicking everyone out of the server and restarting the problem the tempdb is now back to its original size, not a problem.

So I now decided to try and trace the query(s) causing the tempdb to grow. There are only two active databases on the server so I launched the SQL server profiler. I ran it using the "blank" template with the following events selected:

  • All of Errors / Warnings
  • T-SQL
  • Stored Procedures

I then threw this into the database tuning advisor which is now reporting that "67% of the consumed workload has syntax errors".

Question1) should I be worried about such a high level of syntax errors? The errors are coming from a very well known supplier of project management software, should I be contacting them regarding these errors?

Question2) Are the events I selected likely to discover the root cause of my tempdb growth?

Apologies for the long questions, trying to include as much details as I can.

Thanks in advance for any advice I receive.

Improve this question
4
  • 1
    For the growth, was anyone doing a large insert or update operation without batching? In my experience that is the most frequent cause of a tempdb issue. Commented Nov 4, 2010 at 14:57
  • Also you may think about posting this on serverfault.com as well..that site is all admins and such. Commented Nov 4, 2010 at 15:10
  • "well known supplier of project management" does emphatically not indicate that their software is well written particularly on the database side. Our "well known supplier of project management" has one of the worst database backends I've ever seen. Commented Nov 4, 2010 at 15:17
  • Thanks JNK for the comments, I'll add it on that website too. How would I monitor for large inserts / updates? Using triggers or through SQL Server Profiler? Commented Nov 4, 2010 at 15:28

1 Answer 1

Reset to default
2

I have used this one Properly Sizing the SQL Server TempDB Database to monitor the growth. Hope this helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.