was the db cloned onto a new server?In which case user may not be synched with login.

Run exec sp_change_users_login 'report' in the new database, if PRSDEV is listed run

exec...

its not in any system tables but it is held in the registry. Also could get it from the services applet.

if you are backing up to a file or device of the same name ensure you tick the box to overwrite the backup rather than append.

This will of course...

are you shrinking the database after the index rebuilds? that will fragment your data

itm sounds like security had been set to only allow windows authentication and this only took effect when instance restarted on failover. Not sure why reverted back going back to...

Jeff, 'straight' delete code -

delete from AssociationLinks

where RecordId not in

(

select min(RecordId)

from AssociationLinks

group by WorkspaceEntityId,SourceEntityId,TargetEntityId,Name,RecordStatus)

Janine - fix worked. could you explain...

you can restore a database that is marked suspect.

oh and amount of log was per batch.....

Michael,

the db was in simple mode, I monitored logspace used to see how large it got.

I would expect the select to be fast, but it does have the disadvantage of...

the results are in:

straight delete took 8 min 47 secs and created 4GB of tran log

crawler delete took 21:49 at batches of 100,000 (40MB of log) and 9:22 at batches...

other than a database restore , only way this can happen is if someone\something in app removes rights. Use profiler to monitor for event security\audit object permissons event.

Also - ...

As for the younger generation job-hopping more than my (previous) generation yes I think that is true and is partly due to a culture of instant gratification and not being...

Michael,

thanks for the info. so the delete should be:

delete from AssociationLinks

where RecordId not in

(

select min(RecordId)

from AssociationLinks

group by WorkspaceEntityId,SourceEntityId,TargetEntityId,Name,RecordStatus

)

and the...

Jeff, cheers mate, code as well!

I like it, all in one step with no temp table stage.

I think I get it. select min recordid for each group of duplicates, join...

thankyou both.

Just to re-cap Jeff, you reckon best option to use Michaels subquery to select out to a new table, and will be faster than the delete? That will give...