Delete Script Keeps Trying to Recompile 
Author Message
 Delete Script Keeps Trying to Recompile

Hi@

I am running SQLServer 2000 Enterprise Edition on Windows
2000 Advanced Server. I have the following delete
statement, delete from y where s = 26. This delete
statements works on all of my test databases which are
running the same veriosn of SQLServer and Windows, but
does not work on either production server. I have traced
and monitored the statement, and it is caught in a never
ending loop trying to recompile. The statement begins, but
never compiles so never ends.
Has anyone run into anything like this before? There are
no triggers on table y, and I receive no errors.

Any help would be greatly appreciated.

Thanks.
Deanna



Sat, 31 Jul 2004 03:59:43 GMT
 Delete Script Keeps Trying to Recompile

How many records are affected by this delete?  Are there
any dependencies? Do you suspect transaction log file
space problem?
Quote:
>-----Original Message-----

>I am running SQLServer 2000 Enterprise Edition on Windows
>2000 Advanced Server. I have the following delete
>statement, delete from y where s = 26. This delete
>statements works on all of my test databases which are
>running the same veriosn of SQLServer and Windows, but
>does not work on either production server. I have traced
>and monitored the statement, and it is caught in a never
>ending loop trying to recompile. The statement begins,
but
>never compiles so never ends.
>Has anyone run into anything like this before? There are
>no triggers on table y, and I receive no errors.

>Any help would be greatly appreciated.

>Thanks.
>Deanna
>.



Sat, 31 Jul 2004 04:17:30 GMT
 Delete Script Keeps Trying to Recompile
There is only 1 record affected. I pared the query down
from trying to delete 230 records to just deleting an
individual record. There are no dependencies. No, I don't
think the log file is the issue, but will take a look. I
get no indication that naything is wrong except that it
just keeps trying to recompile. I was wondering if there
could be something different in the optimizer that mightbe
causing this? I did forget to mention that the databases I
have tested this on are all restores of a January 29
backup of production.

Thansk for the quick response.

Quote:
>-----Original Message-----
>How many records are affected by this delete?  Are there
>any dependencies? Do you suspect transaction log file
>space problem?
>>-----Original Message-----

>>I am running SQLServer 2000 Enterprise Edition on
Windows
>>2000 Advanced Server. I have the following delete
>>statement, delete from y where s = 26. This delete
>>statements works on all of my test databases which are
>>running the same veriosn of SQLServer and Windows, but
>>does not work on either production server. I have traced
>>and monitored the statement, and it is caught in a never
>>ending loop trying to recompile. The statement begins,
>but
>>never compiles so never ends.
>>Has anyone run into anything like this before? There are
>>no triggers on table y, and I receive no errors.

>>Any help would be greatly appreciated.

>>Thanks.
>>Deanna
>>.

>.



Sat, 31 Jul 2004 04:30:59 GMT
 Delete Script Keeps Trying to Recompile
deanna,

have you run 'sp_updatestats' after restore?

--
-oj
http://rac4sql.home.attbi.com


Quote:
> There is only 1 record affected. I pared the query down
> from trying to delete 230 records to just deleting an
> individual record. There are no dependencies. No, I don't
> think the log file is the issue, but will take a look. I
> get no indication that naything is wrong except that it
> just keeps trying to recompile. I was wondering if there
> could be something different in the optimizer that mightbe
> causing this? I did forget to mention that the databases I
> have tested this on are all restores of a January 29
> backup of production.

> Thansk for the quick response.
> >-----Original Message-----
> >How many records are affected by this delete?  Are there
> >any dependencies? Do you suspect transaction log file
> >space problem?
> >>-----Original Message-----

> >>I am running SQLServer 2000 Enterprise Edition on
> Windows
> >>2000 Advanced Server. I have the following delete
> >>statement, delete from y where s = 26. This delete
> >>statements works on all of my test databases which are
> >>running the same veriosn of SQLServer and Windows, but
> >>does not work on either production server. I have traced
> >>and monitored the statement, and it is caught in a never
> >>ending loop trying to recompile. The statement begins,
> >but
> >>never compiles so never ends.
> >>Has anyone run into anything like this before? There are
> >>no triggers on table y, and I receive no errors.

> >>Any help would be greatly appreciated.

> >>Thanks.
> >>Deanna
> >>.

> >.



Sat, 31 Jul 2004 04:38:51 GMT
 Delete Script Keeps Trying to Recompile
No I haven't, but the script works on the restored test
database, but not on production.
Quote:
>-----Original Message-----
>deanna,

>have you run 'sp_updatestats' after restore?

>--
>-oj
>http://rac4sql.home.attbi.com


message

>> There is only 1 record affected. I pared the query down
>> from trying to delete 230 records to just deleting an
>> individual record. There are no dependencies. No, I
don't
>> think the log file is the issue, but will take a look. I
>> get no indication that naything is wrong except that it
>> just keeps trying to recompile. I was wondering if there
>> could be something different in the optimizer that
mightbe
>> causing this? I did forget to mention that the
databases I
>> have tested this on are all restores of a January 29
>> backup of production.

>> Thansk for the quick response.
>> >-----Original Message-----
>> >How many records are affected by this delete?  Are
there
>> >any dependencies? Do you suspect transaction log file
>> >space problem?
>> >>-----Original Message-----

>> >>I am running SQLServer 2000 Enterprise Edition on
>> Windows
>> >>2000 Advanced Server. I have the following delete
>> >>statement, delete from y where s = 26. This delete
>> >>statements works on all of my test databases which are
>> >>running the same veriosn of SQLServer and Windows, but
>> >>does not work on either production server. I have
traced
>> >>and monitored the statement, and it is caught in a
never
>> >>ending loop trying to recompile. The statement begins,
>> >but
>> >>never compiles so never ends.
>> >>Has anyone run into anything like this before? There
are
>> >>no triggers on table y, and I receive no errors.

>> >>Any help would be greatly appreciated.

>> >>Thanks.
>> >>Deanna
>> >>.

>> >.

>.



Sat, 31 Jul 2004 06:33:08 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SP:s, recompile, keep plan

2. Have keep recompiling procedure

3. sp's keep recompiling

4. create script of recompiling procedures and reindexing tables

5. Need recompile invalid objects script...

6. Why must I recompile to rerun a script?

7. Script to find all invalid objects and recompile

8. to recompile or not recompile postgres

9. WITH RECOMPILE does not recompile???

10. Please help - Keep getting authentication failure when trying to restore database

11. SQL 7.0 does not start but keeps trying

12. FIXED: Please help - Keep getting authentication failure when trying to restore database


 
Powered by phpBB® Forum Software