I tried the second query you added Sean also, and following it through it looks spot on in the logic too.

It's somehow filtering out 59 extra records ( in a...

Many thanks, that works perfectly, is exactly what I was looking for.

Very much appreciated 🙂

Just to add clarity if there is any confusion as to what i'm struggling to select;

There are several DOC_NO duplicates

There are many more REV_NO duplicates

I want to select all records,...

Thanks for the query.

Unfortunately it is filtering out the documents with no file, but isn't filtering out duplicate DOC_NO + REV_NO, so seems to be acting the same as...

CREATE TABLE MYTABLE

(

DOC_NO NVARCHAR (10),

REV_NO NVARCHAR (5),

FILE_NAME NVARCHAR (20),

ADD1 NVARCHAR (10),

ADD2 NVARCHAR (10),

ADD3 INT

)

INSERT INTO MYTABLE (DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3)

SELECT 'ABC123', 'A', 'abc123.pdf', 'CON1', 'LOC1', 1 UNION ALL

SELECT...

Thanks Igor, that seems to have done the job, just using Excel...

I'm sure it's doable in SQL, but as I'm reporting in Excel anyway, it's easy enough to do the...

Thanks for the script, I'll keep a hold of it for future reference! 🙂

I eventually managed to resolve the issue, I think by selecting the second instance of EVERY duplicate...

Are you sure there's not something other than SQL on the box using a reasonable amount of memory?

If you've got 19GB and the top cache object is 121MB (I'm assuming...

I use the below query to check the contents of the buffer cache, to list the objects and count;

-- Breaks down buffers used by current database by object (table, index)...

No suggestions? 🙁

I'll be working on this today, so if I find a solution/cause, I'll post it up...

I just cleared the wait stats on the test box and re-ran the query to see if it helped track down the cause.

When I run the query directly on the...

Thanks, attachments added....can't see a "huge" difference between them to be honest...

Why is a single mdf file being classed as a problem though?

Are there large disk queues?

If so, splitting up onto multiple files may not help (in some cases it can...

Create the additional files on the desired drives, and then you should identify the most utilised tables and split them accordingly.

You would simply change the storage for the clustered index,...

As above, but I usually tend to have dedicated drives for tempdb (data / log) and the swap file (if applicable).