Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 tempdb is skipped

Author  Topic 

mhagen2531
Starting Member

7 Posts

Posted - 2004-07-14 : 12:52:16
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

4184 Posts

Posted - 2004-07-14 : 23:28:57
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

38200 Posts

Posted - 2004-07-15 : 12:58:47
So what's the query?

Tara
Go to Top of Page

mhagen2531
Starting Member

7 Posts

Posted - 2004-07-15 : 13:57:55
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 - 2004-07-15 : 14:01:44
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

22859 Posts

Posted - 2004-07-15 : 14:02:39
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

38200 Posts

Posted - 2004-07-15 : 14:03:17
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 - 2004-07-15 : 14:37:33
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 - 2004-07-15 : 14:39:51
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

38200 Posts

Posted - 2004-07-15 : 14:48:27
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 - 2004-07-15 : 17:54:41
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

38200 Posts

Posted - 2004-07-15 : 17:57:14
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 - 2004-07-23 : 13:47:17
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

38200 Posts

Posted - 2004-07-23 : 13:49:22
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
   

- Advertisement -