Tag Info

New answers tagged

0

We have a similar situation and we had to create a larger script to resolve deletion in a proper manner. Note: Another issue you will run into with large delete operations (one or many transactions), is that your transaction logs will grow really fast during the runtime of the delete. So, be prepared for that in terms of disc space. It may be required ...


1

Core feature is the window function lag(). Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!): CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int) RETURNS integer AS $func$ DECLARE del_ct int; BEGIN -- this may or may not be necessary: -- ...


0

First, sorry if I have a bad English. Even if you dropped the constraints from the table you are trying to delete, the constraints of any children tables will affect the performance of the parent table. This occurs because the database needs to do an select on all the foreing keys pointed to the main table each line of delete to guarantee the integrity of ...


0

I found this article that talks about a situation where the DBA received the same error (15151) and determined the issue was related to an open session for that user. He doesn't mention anything about not being able to find the user, but I figured I would pass this on just in case it helps. Link to the article: SQL SERVER – Fix : Msg 15151, Level 16, ...


2

Short intro To connect to the server you have to have a login to access the server. To access a database you have to be a user in that database. All Logins are member of the server role public which can access the master database. So to find users to drop within a database you query sys.database_principals and to find logins sys.server_principals. When you ...


9

Should be something like this: DELETE A FROM table_example1 AS A INNER JOIN table_example2 AS B ON A.COLUMN1 =B.COLUMN1 AND A.COLUMN2 = B.COLUMN2 WHERE COLUMN_DATETIME > @Period; Alternatively: DELETE FROM A FROM dbo.table_example1 AS A WHERE EXISTS ( SELECT * FROM dbo.table_example2 AS B WHERE B.COLUMN1 = ...


4

In some cases, soft delete is not meant to be permanent. You can just defer the delete to some background job (say, right before you reorganize / rebuild indexes) so that the originating transaction doesn't have to wait for the deletes to occur (especially if you have cascading deletes, triggers, etc). In other cases, a soft delete is not a performance ...



Top 50 recent answers are included