query with union all in multiple select statements

Last Post 06 Aug 2008 07:10 AM by jchung05. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
jchung05
New Member
New Member

--
05 Aug 2008 10:19 AM
Hi all
I had few queries with few union all's were working on SQL Server 2000 SP3 but after I upgraded to SP4 then gave me problems. It happened to one of my VIEWS that uses multiple UNION ALL's and one Stored procedure as well. I just don't under statement why after upgraded to SP4 then gave me so much trouble. One thing I found my tempdb kept filling up with the query that never returned value and another problem if I used sp_who2 to check the processes, I could see the query process SPID is blocking the system process (i.e SPID 6 is blocked by SPID 60). The only thing I can do for the view just broke down to multiple views that uses less UNION ALLs then it works but the Stored Proc I couldn't break it down because the required data. On the other hand, I tried to KILL the process but the process never be able to terminate even I closed the Query Analyzer and after few days ran SP_WHO2 it still shows the process is running and the system process still being blocked by this process. Basically, I need to restart the SQL Server to clean this up.

I am wondering anyone has seen this problem before and how did you fix it? By the way, my query worked for past 5 years until I upgraded to SP4. it's greatly appreciate if you can guide me this. Thanks in advance.

Jimmy
jchung05
New Member
New Member

--
06 Aug 2008 07:10 AM
the query was never ended even I used KILL spid (sp_who2)

SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName
52 RUNNABLE sa KUEHNE-JCHUNG . VIAXREF KILLED/ROLLBACK 313 67 08/06 09:53:22 Microsoft SQL Server Management Studio - Query

the system process (sp_who2)

SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
9 BACKGROUND sa . 52 master TASK MANAGER 0 11 08/01 13:32:32 9

when I used EM to click on tempdb -> table

SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
56 sleeping RUSGEN\Administrator VIADEV2 52 tempdb SELECT 187 38 08/06 09:56:42 MS SQLEM 56


The tempdb is also blocked by this process and filled up all space for some reason. all these spid's wont be able to clean until the SQL Server is restarted.
jchung05
New Member
New Member

--
06 Aug 2008 07:45 PM
I ran in local server query analyzer getting the same result the values never returned. I did try the execution plan but since it's Stored Proc, so noting I can see. I knew exactly where the problem is now but unable to result. Here is my SP in pseodo code

Proc SP1
Begin
create table #temp1
insert into #temp1
select v1,v2 from t1

insert into #temp2
execute proc SP2 -- here is the problem never ended query

End

If I comment out SP2, then I can get the SP1 query returns values. If I just executed SP2, I can get the value returned as well. but not both. I am sure it works on 2000 SP3 but just gave me problem on SP4 after I ran the upgrade. It seems the tempdb was filled up so it wont be able to go anywhere. I tried to increase the tempdb but it's no help either.
jchung05
New Member
New Member

--
07 Aug 2008 11:01 AM
I didn't use profiler but if nothing return can't see anything in there I understand. Yes, I did create before insert.


Acceptable Use Policy
---