Re: Help with blocking

From: Umut Nazlica (UmutNazlica_at_discussions.microsoft.com)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 09:29:07 -0800

Yes, that might be possible as i mentioned before in addition to this youn
capture a trace which includes log auto growth and the statements to be sure..

I don't know if it's possible with your application as you mentioned before
tablaA has certain amount of records and tableb (could be 5000 or 200000)is
vary it would be better to insert the records to a new table which
tablea.col1=tableb.col1 and drop tableb and rename new table as tableb, let's
assume that you have 100 records in tablea and 100000 records in tableb, why
delete all the 99900 records instead of adding 100 records to a new table and
drop oldone and rename new one.. (Well again this looks like a batch process
to me..)

Regards..

"Hugo Kornelis" wrote:

> On 8 Feb 2005 05:32:33 -0800, steveg@payerpath.com wrote:
>
> (snip)
> >While this second DELETE statement is "hung", sysprocesses shows that
> >it is sleeping. What would make SQL sleep this SPID on a simple
> >DELETE? I understand that it can happen when SQL is waiting for the
> >application to provide some feedback but this is a simple DELETE; there
> >shouldn't be any "back-and-forth" interaction between the client and
> >server that I'm aware of. Can anyone explain why a simple DELETE
> >statement would go to sleep?
>
> Hi Steve,
>
> I realize it's mentioned before in this thread, but this really sounds as
> if eitehr tempdb or the log file (probably the latter) is autogrowing.
> What is the average number of rows affected by this second delete?
>
> If I were you, I'd check that autoshrink is not accidentally enabled for
> this database, manually grow the log file to twice it's current size and
> check if this helps.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>