dthansen
asked on
Recovery Model for Bulk Operations
We have a few stored procedures that do some heavy select into (millions of records at a time).
It has been recommend that we alter the database to a Bulk-logged recovery model during the window in which we perform the bulk operations.
We currently have the database in a Simple recovery model. Based on SQL documentation, both Bulk-Logged and Simple recovery models support high-performance bulk copy operations.
Is there any performance benefit to switch the database from Simple recovery model to Bulk-logged recovery model while we perform the 'select into'?
Thanks,
Dean
It has been recommend that we alter the database to a Bulk-logged recovery model during the window in which we perform the bulk operations.
We currently have the database in a Simple recovery model. Based on SQL documentation, both Bulk-Logged and Simple recovery models support high-performance bulk copy operations.
Is there any performance benefit to switch the database from Simple recovery model to Bulk-logged recovery model while we perform the 'select into'?
Thanks,
Dean
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
1. alter database SOMEDB set recovery BULK_LOGGED
2. select column1, column2 into sometable from sometable2 (nolock)
3. alter database SOMEDB set recovery Simple
Thanks,
Dean