Este método que se muestra es muy útil, cuando queremos eliminar miles de registros de una tabla sin generar un bloqueo de tabla, permitiendo que pueda ser accesada por mas usuarios.
Es siempre recomendable depurar nuestras tablas, pero que pasa cuando tenemos que eliminar miles de registros y cuando nuestra base de datos es de 24x7.
Si nosotros trataramos de ejecutar un simple delete como este:
DELETE tbl_depura WHERE fecha < '200600101'
Lo que ocurriria es que SQL Server para optimizar la memoria escala el tipo de bloqueo de tipo página a tabla, de esta manera solo genera un solo bloqueo, en el caso contrario tendría que generar un bloqueo por cada página o registro que fuera a ser eliminado generando miles de bloqueos y consumiendo la memoria de SQL Server.
De esta manera con un bloqueo de tabla si un usuario quisiera consultar información del año 2006 no podría, tendría que esperar a que el DELETE terminara.
Una manera de evitar bloquear a los usuario por tanto tiempo, es reducir el número de bloqueos que se acumulan por transacción y prevenir que se escala el bloqueo a tipo tabla, de la siguiente forma:
SET ROWCOUNT 500
delete_more:
DELETE FROM tbl_depura WHERE fecha < '200600101'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
De esta manera se estaría borrando registros en transacciones de 500 registros sin bloquera toda la tabla por completo y liberando los bloqueos en cada transacción.
Esto también evita que se presente el siguiente mensaje de error cuando SQL Server se queda sin memoria para alojar más bloqueos.
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.