September 2, 2020 at 2:18 pm
I need to delete a lot of rows from some tables. There are millions of rows and only a few thousands should be left afterwards.
Based on experience this takes a lot of time and impacts other jobs that use the table. So what I would like to do instead is to:
That should make the process much faster than deleting that many rows.
But obviously I need to be able to ensure that other jobs don't add rows to the table while these things happen. So how can this be done?
I'm looking for someting like
ALLOCATE TABLE xxx WITH(EXCLUSIVE_LOCK)
do my things....
DEALLOCATE TABLE xxx WITH(EXCLUSIVE_LOCK)
Obviously that is not possible, but how can one do something like that in other ways?
September 2, 2020 at 3:38 pm
Something along these lines:
SELECT TOP (0) *
INTO dbo.maintable_backup
FROM dbo.maintable
--Edit: Added UNION ALL to cancel IDENTITY property, if present
UNION ALL
SELECT TOP (0) *
FROM dbo.maintable
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO dbo.maintable_backup
SELECT *
FROM dbo.maintable WITH (TABLOCKX)
WHERE <whatever>
TRUNCATE TABLE dbo.maintable
INSERT INTO dbo.maintable WITH (TABLOCKX)
SELECT *
FROM dbo.maintable_backup WITH (TABLOCKX)
END TRY
BEGIN CATCH
...
END CATCH
END TRANSACTION
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 2, 2020 at 5:00 pm
Thank you, looks promising. Not quite the session scoped lock I was thinking of, but using a transaction scoped lock is probably okay.
But, hmm...
Looking at the description of table hints in the MS documentation, it is not completely clear to me if the TABLOCKX lock is held only for the statement or if it is held until the end of the transaction like XLOCK and TABLOCK in combination?
September 2, 2020 at 6:57 pm
Yes, technically TABLOCKX is only for that statement. I guess in theory someone could INSERT a row between the SELECT and the TRUNCATE.
I should have added HOLDLOCK to the first query:
SELECT *
FROM dbo.maintable WITH (TABLOCKX, HOLDLOCK)
WHERE <whatever>
Or you can set the trans isolation level to SERIALIZABLE for the duration of that trans.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 2, 2020 at 7:16 pm
Include the TRUNCATE in the transaction. That'll lock the table until a COMMIT is done.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2020 at 4:09 pm
As Jeff noted, include TRUNCATE in here.
September 3, 2020 at 5:46 pm
How critical is your application?
You will have an outage between steps 2 and 3 and if the table has foreign key constraints you cannot use truncate table
If you are with SQL 2016 enterprise you could:
Partition the table, then use merge/split to drop a large number of rows.
or
Delete records in chunks
Don't forget to run update statistics
September 3, 2020 at 8:46 pm
As Jeff noted, include TRUNCATE in here.
I did include TRUNCATE in the trans, from the start. Not exactly sure how else you want the transaction structured.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 3, 2020 at 8:51 pm
Steve Jones - SSC Editor wrote:As Jeff noted, include TRUNCATE in here.
I did include TRUNCATE in the trans, from the start. Not exactly sure how else you want the transaction structured.
Agreed... didn't see your post until after I posted. I was responding to the OP's original post.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2020 at 9:14 pm
How critical is your application?
You will have an outage between steps 2 and 3 and if the table has foreign key constraints you cannot use truncate table
Don't forget to run update statistics
With FKs, true, but you could drop and recreate the FKs. I'd do that rather than have to DELETE that many rows from the table rather than just TRUNCATE it. The logging time alone will kill you DELETEing that many rows.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 3, 2020 at 9:39 pm
If the table has foreign key constraints, chances are you will also need to look at other tables too when deleting such a large number of data
If there are no constraints, and if you are allowed to, you could:
Copy the rows you want to keep into a new physical table, drop the original table, rename the physical table
That has its pitfalls too with permissions, constraints, but it could save you downtime
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply