BULK Delete

Question

BULK Delete

VastSQL

SSC-Dedicated

Points: 36097
More actions
May 11, 2015 at 4:05 am

#317313

HI Experts,
We have a scenario in which the application scheduler deletes 5million records twice a day and insert almost same amount of data.
1.Is there anyway this can be achieved with less logging to database?
Will it help if change the recovery mode to Bulk-Logged? IF yes, is it possible to change the recovery mode before and after transaction and will it affect the Always On or any other options??
Thanks in Advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Looks like a good place for partitioning. If you can make your table partitioned on the predicate you use for deletes, gettin rid of that data can be a simple partition switch, which is a metadata only operation (pretty instantaneous). Then you can simply drop the table resulting from the partition switch off.

In the same way, you could bulk insert data to a new table and switch it in as a partition.

BULK LOGGED should help you minimize the amount of log. Please make sure to read about the limitations of the bulk logged recovery model and how it affects the ability to perform point in time restores.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

BTW, partitioning is an enterprise-only feature.

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ratheesh.K.Nair (5/11/2015)
1.Is there anyway this can be achieved with less logging to database?

Insert, maybe, delete no.

Will it help if change the recovery mode to Bulk-Logged?

For the insert, maybe, for the delete, no

IF yes, is it possible to change the recovery mode before and after transaction and will it affect the Always On or any other options??

You can't switch to bulk-logged without removing the availability group first..

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

...will it affect the Always On...

OUCH! I missed this part...

-- Gianluca Sartori

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

GilaMonster (5/11/2015)
Ratheesh.K.Nair (5/11/2015)
1.Is there anyway this can be achieved with less logging to database?
Insert, maybe, delete no.
Will it help if change the recovery mode to Bulk-Logged?
For the insert, maybe, for the delete, no
IF yes, is it possible to change the recovery mode before and after transaction and will it affect the Always On or any other options??
You can't switch to bulk-logged without removing the availability group first..

Thanks a lot Gail.. Is there anyway i can achieve the result i want??

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 6

spaghettidba (5/11/2015)
Looks like a good place for partitioning. If you can make your table partitioned on the predicate you use for deletes, gettin rid of that data can be a simple partition switch, which is a metadata only operation (pretty instantaneous). Then you can simply drop the table resulting from the partition switch off.
In the same way, you could bulk insert data to a new table and switch it in as a partition.
BULK LOGGED should help you minimize the amount of log. Please make sure to read about the limitations of the bulk logged recovery model and how it affects the ability to perform point in time restores.

Thanks spaghettidba