SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 tempdb is skipped
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mhagen2531
Starting Member

7 Posts

Posted - 07/14/2004 :  12:52:16  Show Profile  Reply with Quote
Has anyone seen the SQL Server error:

"tempdb is skipped. You cannot run a query that requires tempdb"?

We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks!
- Mike

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/14/2004 :  23:28:57  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Ask the normal questions I guess. What windows and SQL Server service packs are you running?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/15/2004 :  12:58:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
So what's the query?

Tara
Go to Top of Page

mhagen2531
Starting Member

7 Posts

Posted - 07/15/2004 :  13:57:55  Show Profile  Reply with Quote
It's a simple query, used for mapping staff names to application role descriptions:

SELECT USER_NAME, ROLE_NAME, ROLE_DISPLAY_NAME
FROM User_Role_Map_Detail
WHERE ( USER_NAME = 'mike.hagen' )

The odd thing is, the 5 queries that are run before we get to this code work fine.
Go to Top of Page

mhagen2531
Starting Member

7 Posts

Posted - 07/15/2004 :  14:01:44  Show Profile  Reply with Quote
Windows 2000 Server, with SP3:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/15/2004 :  14:02:39  Show Profile  Reply with Quote
USER_NAME is a reserved word, maybe try putting the two occurences in square brackets?
[USER_NAME]

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/15/2004 :  14:03:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you sure that this is the query causing the error? I don't think it is. I would suggest running SQL Profiler until the problem occurs. Once it occurs, analyze the results prior to the error. Let us know what you find.

Tara
Go to Top of Page

mhagen2531
Starting Member

7 Posts

Posted - 07/15/2004 :  14:37:33  Show Profile  Reply with Quote
I'm pretty sure it isn't having a problem with the column name being a reserved work, as it works fine in Query Analyzer.
Go to Top of Page

mhagen2531
Starting Member

7 Posts

Posted - 07/15/2004 :  14:39:51  Show Profile  Reply with Quote
I'm sure that particular query is the one causing the error, as we're using Log4Net to log all the queries on a logging server. The persistance layer is trapping a SqlException as that particular tSql is being executed.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/15/2004 :  14:48:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by mhagen2531

I'm pretty sure it isn't having a problem with the column name being a reserved work, as it works fine in Query Analyzer.



A query working fine in Query Analyzer does not mean it will work fine somewhere else. If you are following best practices, the query will be in a stored procedure so you can easily put brackets around the reserved word. If the query is in your code, well that'll be a little harder to change it then.

Tara
Go to Top of Page

mhagen2531
Starting Member

7 Posts

Posted - 07/15/2004 :  17:54:41  Show Profile  Reply with Quote
Actually, the code works fine %99.9, apart from the exception that occurs once a week or so that requires us to restart the SQL Server instance.

About using stored procedures, I agree, I would rather put things like that in SPs, but that was one of our design choices. To make the application database-agnostic, we couldn't use database-specific features, such as using procedures.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/15/2004 :  17:57:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
I suggest running SQL Profiler to capture what is occurring on the database server. I don't know anything about Log4Net, but I'm sure SQL Profiler captures more stuff. If SQL Profiler doesn't capture the exception, then the problem is outside of SQL.

You might consider installing the latest security patch for SQL Server as it contains bug fixes. Your version number will be 818 if it's installed.

Tara
Go to Top of Page

mhagen2531
Starting Member

7 Posts

Posted - 07/23/2004 :  13:47:17  Show Profile  Reply with Quote
So, I think we finally figured out what was happening. It turns out that our host had applied a patch that required the database server to be rebooted during a scheduled outage. However, the application on the web server still had connections cached to the database, which were invalid at that point. In the .Net implementation of connections, they only support the statuses Open and Closed, and Broken (as in this case) might be supported in future implementations. So it boils down to the connection trying to use resources that are no longer there, but that doesn’t explain this specific message.

It seems the solution at this point is to restart IIS whenever SQL Server is restarted. I guess when our host said 24/7 availability, they meant except for when they have to reboot servers!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/23/2004 :  13:49:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes 24/7 availability always includes maintenance windows. The point is that 24/7 availability is no unplanned downtime. Scheduled outage is planned.

Tara
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000