Hi I prefer this syntax:

WITH ItemsToBeDeleted

AS

(

SELECT *

, row_number() over (partition by item_no ORDER BY id) as RowNumber

FROM item_store

)

DELETE FROM ItemsToBeDeleted Where RowNumber = 2

Much more efficient.

Regards,

JP

Hi

This is quicker:

alter database [db_name] set single_user with rollback immediate

go

Hi

The 'PATINDEX, convert and the use of the % wildcard will  lead to a table scan. (check execution plan). I guess that the execution plan also shows that a (table)...

Erik

Thanks, that's exactly what I needed. We will be moving that appliation to sql2005 in a few months so let's hope they've correctly implemented the new isolation level!

JP

Erik,

Yes it is read uncommitted, that's for sure, but then again, why don't we get 'the other' error message? (There is a message stating that 'data chas changed due tot...

Hi Jayakumar,

We're running on SP4 + fixes up to 2192 and the problem still occurs. This particular post is about a SP3 fix. This should be resolved in SP4.

JP

Ian,

We tried to get MS Support to fix the problem, but unfortunately they didnt' succeed in finding any solutions. We're still getting the error on a almost daily basis....

JP

Check the account you use to start SQL Server service. Check the NTFS permissions on drive D. The account should be listed there (or via group membership) and should have...

Hi

I'm not not quit sure about this issue, but are the NTFS permissions OK?

JP

Hi

You need to backup the things you like to restore in case of an amergency. It seems that you have diskbackups of your databases, made with a maintenance plan. It...

Hi

In MSDE autoclose is turned on automatically and you'd better set it to off in SQL 2000.

JP

Hi

restart of service can take a while when tlog is big...but it will complete eventually and indeed commit all transaction in db.

JP

Hi,

Just google on error number: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167068&SiteID=1

JP

Great article!  

I've noticed this behaviour as well on one of my production databases 🙁

There is a good explanation on MS site: http://support.microsoft.com/kb/271566/ and a related article about a fix...

Ryan,

You're right about the diff processes competing for the same disk when you would use RAID 5. I think that creating 2 arrays (RAID1) is a better choice. If, you...