0

I have been currently running this query for the past 5 days and it still hasn't completed.

I wanted to see if someone could look at my query and let me know if there is a way to speed it up.

A little background (this is a query to purge threat event 1095 from our SQL database for our ePO server. The events are clogging up the database currently these events account for 165GB of the database. I have been running the query to check away at blocks of the data. The current query is purging data between '2016/03/20' and '2016/05/20. I need to keep 120 days of data for security reasons).

SET ROWCOUNT 10000

DELETE FROM epoEvents
WHERE (([EPOEvents].[ThreatEventID] = '1095') 
       AND ([EPOEvents].[ReceivedUTC] BETWEEN '2016/03/20' AND '2016/05/20'))

WHILE @@rowcount > 0
BEGIN
    DELETE FROM epoEvents
    WHERE (([EPOEvents].[ThreatEventID] = '1095') 
           AND ([EPOEvents].[ReceivedUTC] BETWEEN '2016/03/20' AND '2016/05/20'))
END

SET ROWCOUNT 0
GO

Any help is appreciated.

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Thomas Garcia
  • 1
  • Did you set recovery mode to simple so fewer logs generated? as outlined here: http://stackoverflow.com/questions/24213299/how-to-delete-large-data-of-table-in-sql-without-log and discussed https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server Nearly a duplicate question to 1st link... – xQbert May 17 '17 at 15:46
  • When deleting more than *5000 rows* at once, SQL Server will do a **lock escalation** and basically lock the **whole table** exclusively, so that not even `SELECT` operations are possible against that table (until the delete transaction is done). So I'd strongly recommend deleting **fewer than 5000 rows** at once! – marc_s May 17 '17 at 15:51
  • Hi xQbert I confirmed that recovery mode is set to simple already. marc_s thanks for the information. I will adjust my query to delete less than 5000 rows at once. – Thomas Garcia May 17 '17 at 16:10

0 Answers0