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?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-15 : 12:58:47
|
So what's the query?Tara |
|
|
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. |
|
|
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) |
|
|
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 |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
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! |
|
|
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 |
|
|
|