Service Broker is part of the answer, that's why you don't see any jobs. The other part is that it is essentially reading the logs and using that to determine...

If you shrink the file you'll have to do the index maintenance regardless of you you do it, sorry. The real question is what are you trying to do.

If this...

Mani-584606 (5/10/2010)

---

Its working when I provided the below

myserveramed instance

But the same is NOT working when I accessed through VPN. From VPN the below is working myserver\<named instance>,port.

What should I do...

There are a whole bunch of things that could br going on here.

First some questions. Is this a new cluster you have just built, or an existing cluster just experiencing...

Jeff Moden (5/6/2010)

---

As well as what Leo said, sometimes...

In SQL 2005, Managment Studio

Right Click the server ->...

How big is the table you are testing against?

Also are the table layouts the same?

Also doing a DBCC DROPCLEANBUFFERS before each run avoids the use of the cache.

Leo

Yes!

I'm not sure what you mean byt this:

.... However my problem is that SQL will automatically wraps delete in a transaction, and thus moves of the records its about to...

You said: In other words, the records were added chronologically most of the time (but not always).

Check if ther are any records that would be incorrectly deleted if...

If you are also shrinking the database my recommendations are:

Do the shrink on the file

Shrink it in small amounts multiple times, rather than trying to go down in one big...

You asked: Why are you saying that to find the TOP (1000000) sql must check all the records?

The only way to find the TOP record is to look at them...

A further comment is to try use a clustered index/primary key in the WHERE clause. You don't say if the table is clustered on [DATE].

If [DATE] isn't clustered but there's...

1st problem you have is in:

IF EXISTS SELECT TOP 1..

2nd Problem:

DELETE TOP (1000000)..

To find the TOP (1000000) sql must check all the records.

Rather:

SET ROWCOUNT 1000000

DECLARE @deleted

SET @deleted = 1000000 ...

That's about 7 messages a second (assuming 24 hour day), which isn't so bad unless there are big peaks.

Sorry to say it, but this is a typical response from a...

MAX Degree of Parallelism. Or the number of processors SQL will assign to a query. Opinion varies on what it should be set to, but on SQL2K and SQL2K5 OLTP...