Sometimes we need to delete big amount of records from the table in SQL Server database. If number of records is quite big (several millions) the simple delete command will hang:
In this case we can use the following approach: delete records by chunks (e.g. 100000 items per operation, but it depends on how many columns your table has. It if has a lot of columns and data, you may adjust this number for your scenario) until all records will be deleted. For example imagine that we have table [Log] used for logging some operations:
And suppose that this table grows quite fast and we need periodically delete old log records from it (e.g. older than 2 weeks). It can be done by creating job in SQL Server agent. For this job create the step which will run the following code:
This script will delete records from the Log table by chunks in 100000 rows until all records which are older than 2 weeks won’t be deleted and won’t hang because of big amount of data.