QRY will not stop!

Last Post 05 Dec 2007 06:35 AM by nosepicker. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
richmondlake
New Member
New Member

--
05 Dec 2007 04:45 AM
Hello all.

Sorry if this sounds familiar but I did not know exactly how to phrase the search to look for previously posted answers for this one.

I took over a SQL 2000 box and things got worse from there.

Basically I have a DTS Package which contains 4 TSQL statements. 3 of the 4 work fine, one never stops running. The one which never stops is the third of the 4 and the 4th step depends on it to complete before it can run so essentially it stops my nightly processing cold in it’s tracks.

The main source table the TSQL hits is the largest in the database and is over 40 GB in size. There are millions of records in this table. All the particular statement is doing is asking that big table for a date in a date field for yesterday. If there is no date, it inserts yesterday’s date. That’s all it does.

This Package used to work fine overnight without exception.

THEN, we got new boxes so I moved the whole thing over to two new boxes. One for DEV and one for PROD. I estimated that since the new boxes were 4 times faster and had 10 times more memory and duel processors that everything would work faster.

Funny thing is the Third step in the Package did not work at all when I moved it all over to the new boxes.

The boxes are bigger, faster, stronger, yet the 3rd part of the package just keeps running and never stops.

I ran this code (see below) on my tables to fix index issues but it did not work.

I did shrink and it did not work.

I did update statistics but it did not work.

The only thing I could do to get this to work again was to create a Temp table to dump the records (for one day) about to be evaluated from that main source table into then ran the TSQL against that new temp table as the main source table then updated the original big source table with the date if it was NULL in the temp table.


Here is the index cleaning I did.
****************************************************************
USE databasename
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '
+ rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG (databasename, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch
****************************************************************


Now the question.

WTF?

How could it not work on the new boxes but run on the old crapy box?

To be clear, I did not do the SQL Install on the new boxes so I do not know for sure if there was a problem with the install on the two new boxes. The two new boxes are remote and when I got in, everything looked okay, just a big empty new Install waiting for data.

Also, here is about what I did to get the database and objects over to the new boxes.

I DTS over what I could to the new boxes and did back up / restore for the tables. The rest, I did saves and brought them over to the new boxes and either re created the objects or copy/pasted to make UDFs and packages, Etc….

It should be noted that all other aspects of the Database worked fine, just this 3rd part of this one Package fails to finish.

Other DTS jobs work fine. Everything w
nosepicker
New Member
New Member

--
05 Dec 2007 06:35 AM
Instead of defragging the indexes, try completely rebuilding them (i.e., dropping them and recreating them).
richmondlake
New Member
New Member

--
05 Dec 2007 06:51 AM
Interesting.

Do I run same code but replace defrag with drop where that word appears? Or perhaps someone already posted a drop/rebuild index sample…

Thank you for the idea.
You are not authorized to post a reply.

Acceptable Use Policy