Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Deleting Massive data from a table in SQL

Author  Topic 

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-01-20 : 07:43:07
I have to delete a ton of data from a SQL table. What is the best way to do this. I have a unique identifier called the version. I would like to use if not in these versions then delete. I tried to using the statement below, but learned the hard way that it created an error this is the message I got....Msg 9002, Level 17, State 4, Line 3
The transaction log for database 'MonthEnds' is full due to 'ACTIVE_TRANSACTION'.


I was reading about truncate, I am not sure how I would do this or how I would setup the statement. Any help would be great, thanks.


Delete Products
where versions were not in (('48459CED-871F-4971-B888-5083990332BC','D550C8D3-58C7-4C74-841D-1C1675F19AE3','C77C7817-3F04-4145-98D3-37BB1610DB35',
'21FE83FA-476D-4604-80EF-2ED57DEE2C16','F3B50B81-191A-4D71-A406-011127AEFBE1','EFBD48E7-E30F-4047-909E-F14DCAEA4181','BD9CCC41-D696-406B-A48D-B60C30521456',
'528D0D4A-9D98-4D61-926E-7612DA326694','DF5AA83D-595B-4F6F-85A2-8773AB9079D9','D570BF53-DA30-463E-8149-7F627AACC10F','0FEE5E05-0D60-4A72-A50A-65BA70123210',
'EB9EC0AB-7673-4634-88FE-AB84D6570487','AB886433-6124-4655-91F3-04854777ECAC','FA41D674-6150-452E-8E65-F400D24F6B21','71029716-7798-4AB8-A39E-73D16F934363','C4B6C4D3-E303-492E-B347-8CA65B0AC433',
'8FCA8E04-CA87-482B-819E-04E90558AAE1','7F02C850-4B82-42B1-9053-5522104B9663','71A81520-D2CC-43A9-A243-0B885E984E46','C5389DE9-D637-4B15-A2EB-BD3F26C2B26D','D9E204E3-4F51-4CFC-B307-77A16DEFECEC',
'C8BEBFBC-D362-4D0F-A555-B281FC2B3023','EFA64956-C2CF-41FC-8E21-F060597DAFCB','77A8DE56-6F7F-4490-8BED-AA6809B947EF','0F4C1E5F-B689-4DCB-931B-10E7423CCE19','B4A61FB1-77F6-418A-B8BC-636D5A91169E','DCD1B891-BC5F-4B6A-8590-32702B965C1E',
'B5D7906A-850D-4E5C-96CB-8D90310BC325','A95C69A9-CD5C-405B-B475-37E80774C6B9','3E8917F1-4409-419A-907A-ABB5A6508CA2','AC71FC9A-C891-4703-BDB3-7252FB550E5D','CCFF8A4C-AAF5-41C8-A4E1-97D54D98E0DB','423B795E-8281-46B6-9BEB-A29045EB8383',
'5A2E45B9-B28F-4FA6-90DD-7924B2835972','B5D7BB27-DB13-4597-AB85-D38B5CBF3CB3','0410B50F-4C92-4324-AE02-2C108690EED7','1FFF10C6-3DA1-49E1-9461-66C60F635DAE','496B180D-8806-4A62-A35F-9AB3ED0F0C71','A910FD5D-135F-4EC3-84B6-A2F245274190','AD49CC5E-BE52-4228-B1FB-C3BDB1410D66',
'072C1734-72D6-4D0E-B6F1-946750F87C27','E58D08D8-F87C-4584-8035-75DEF14E1DD5','CA9F2EDF-9D3F-481E-A82E-B9DEB7D795D3','68A58A2C-439F-42D3-B094-CF3FC8999E35','ED34712F-EAE9-4C71-90D6-96780F66738D','6222E9F0-E81F-4270-B607-8DC49D5C9DB7','A6A23EB5-CA7B-4CA3-93EA-1B45B90A3C58',
'3DB61860-1CD6-4278-87C7-DAE177A6CC1B','AE081ECF-2A28-48F0-9B92-0133ACAA5A75','B757B8F0-3CFD-4BA0-AAB8-03C6CF4992D8','4D7F679D-813C-4BF5-870E-894DC3D9063C','319913A0-5F20-45D3-AE62-E66B5AFFB4BB','0866EB19-3AC9-4386-96A6-BFD8BD5D1D9F','254A2D37-9AE5-4655-9794-B011CF265720','3385D34A-5D3C-460D-81A1-FF6E823F096E',
'9B32DFD5-D9F9-4EF3-98AC-D1A32BF83DDE','3AE81E0D-5599-4887-9CE4-BE8E512F536A','5796859A-D26D-46D8-9DAC-2D495903E398','0A36FC63-7F83-4F9C-B183-FB36E7E59012','794F5397-B93E-46D5-9EB6-F83875BDE18B','2362E7F2-2667-404D-A29C-9AE8FC85E219','5F5F821A-4080-43E0-9D8D-09ABC1B94BFB','0F44126F-D3AB-4819-BBB5-F46D28008913','80C1E817-945B-4BD1-822F-E5D64015B75E',
'4B324E82-740E-4885-A704-8D1D502A653A','A06E72D5-E918-4E61-A0A3-E9FFA95F7882','195D6F7A-529F-4DCC-AC0E-D05D76BCD0B2','F49E8519-D479-4D81-8D02-C82CFAE6B7BE','75190F79-D5F2-4138-92C6-0B2B1CF2111F','C098C52E-44FD-498C-95AF-72CACFE0A269','64CC6E3B-8A0E-4936-AEB3-472530E2AFB3',
'2EF1A7E6-A11C-430B-9C70-0050DC14F67E','A549429E-0FD9-416C-9E3A-3D370B06E237','5DF2CE22-422E-484E-A095-DB5096120AE3','83CD6889-84D4-4E8F-9DA6-7E8BE97202E8','8678B159-A0FC-4212-B3F5-BBAB708E9912','905197F6-53D0-42D9-89FA-376D2FAD669A','B04A2848-EA91-4DCC-99D2-EC027454FF87','1E9942F2-4332-48C3-A106-8E1D2F669A66',
'E0EE3ABC-6680-4F2E-A97A-A486FF08A8E1','C4801FB0-9A0F-4026-81B8-B93DA9D3D772','314C6B1C-A79D-46B5-A87F-FC7763BFF96E','FE18C44A-3A30-4B8B-BBA3-815912914581','B94290F2-1415-4F3A-AF4B-0A61D914BF28','8466AEA7-7447-4CDB-80A3-6DADB1925068')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 08:04:14
Try doing it in batches so that log file doesnt get filled up. Create a temporary table with values you use in NOT IN. use a NOT EXISTS logic with it and delete in batches. so something like below

declare @temp table
(
versionid uniqueidentifier
)

insert @temp
values... all your above values

declare @cnt int = 1

while @cnt > 0
begin
Delete top (100000) p
from Products
where not exists (select 1 from @temp where versionid = p.versions)

SET @cnt = @@ROWCOUNT
end


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2014-01-20 : 11:52:05
If your database uses Full Recovery model you will also need to backup the Log File frequently during the time that the Delete is running. We set our Log Backup to be every 2 minutes during high volume deletes.

If users need to have access to the database, during the delete, then you could also add a WAITFOR delay - of say 5 - 10 seconds - in the LOOP so that the deletes also have a period of quiet for other processes to run.
Go to Top of Page
   

- Advertisement -