Si este artículo es el primero que está viendo a la serie del Registro de transacciones de SQL Server, le recomiendo que consulte los artículos anteriores (consulte la tabla de contenido a continuación), en los cuales describimos la estructura interna del Registro de transacciones de SQL Server, la función vital que desempeña el uso del Registro de transacción para mantener la base de datos en un estado coherente y así poder recuperar la base de datos corrupta o la tabla modificada por error en un momento específico.
SQL es un lenguaje de consulta estructurada (en inglés, Structured Query Language). Nos permite realizar diferente operaciones en los sistemas de gestión de bases de datos relacionales. Consiste en un lenguaje de definición de datos (DDL), un lenguaje de manipulación de datos (DML) y un lenguaje de control de datos (DCL). En éste articulo podéis ver la diferencia de este conjunto de lenguajes. Utilizando este lenguaje podemos generar cientos y miles de tipos de consultas sobre nuestras bases de datos.
Una sentencia es una orden de código en lenguaje SQL. Data Definition Language (DDL): CREATE, ALTER, DROP, TRUNCATE. Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE. Data Control Language (DCL): GRANT, REVOKE. A diferencia de la manipulación, permite controlar el acceso a la base de datos, nos garantizará parte de la seguridad. Transaction Control Language (TCL): COMMIT, ROLLBACK, SET TRANSACTION. Se utilizan para controlar las transacciones en una base de datos, por ejm sirven para si necesitamos ejecutar varias sentencias de código SQL una detrás de la otra, para poder realizar una tarea en particular, para ello nos sirven este tipo de estructuras denominadas Sentencias de Control de Transacciones.
Modelos de Recuperación y Truncamiento del Registro de Transacciones
Así también discutimos en este artículo los tres modelos de recuperación, completo, simple y de registro masivo, que controlan cómo se escribirán las transacciones en el archivo de registro de transacciones de SQL Server y, finalmente, cómo poder administrar y monitorear el crecimiento del registro de transacciones de SQL Server.
Al tratar de configurar su base de datos con el modelo de recuperación simple, el registro de transacciones de SQL Server se va a marcar como inactivo y se va a truncar automáticamente después de confirmar la transacción activa. Este no es el caso con los modelos de recuperación de base de datos completa y de registro masivo.
Cuando la base de datos se configura con el modelo de recuperación completa, el registro de transacciones de SQL Server en el archivo de registro de transacciones se marcará como inactivo después de confirmar la transacción, sin truncarse automáticamente, ya que estará esperando que se lleve a cabo una copia de seguridad del registro de transacciones. Tiene que recordar que solo es la copia de seguridad del registro de transacciones, pero NO la copia de seguridad completa de la base de datos, truncará los registros de transacciones del archivo de registro de transacciones y lo pondrá a disposición para su reutilización.
La copia de seguridad del registro de transacciones de SQL Server solo se puede obtener de la base de datos cuando el modelo de recuperación de esa base de datos está lleno o se ha registrado en bloque. Aparte de eso, la copia de seguridad del registro de transacciones va a requerir que se tome al menos una copia de seguridad completa de esa base de datos como punto de partida para la nueva cadena de copia de seguridad. Tenemos que hacer una copia de seguridad completa de la base de datos para poder realizar una copia de seguridad del registro de transacciones para esa base de datos.
Vamos a usar el comando BACKUP DATABASE T-SQL para poder realizar la operación de copia de seguridad completa de la base de datos en nuestro ejemplo. Para poder obtener más información sobre las distintas formas y opciones para realizar copias de seguridad de bases de datos en SQL Server, puede consultar la serie de copias de seguridad y restauración de SQL Server.
Una vez que se haya realizado la copia de seguridad completa de la base de datos, vamos a comenzar a tomar las copias de seguridad del Registro de transacciones para la base de datos. La primera copia de seguridad del registro de transacciones va a obtener una copia de seguridad de todas las transacciones que ocurrieron en la base de datos desde la última copia de seguridad completa. Por otro lado, las copias de seguridad del Registro de transacciones que siguen a la primera copia de seguridad del Registro de transacciones van a tomar copias de seguridad de todas las transacciones que ocurrieron en la base de datos desde el momento en que se detuvo la última copia de seguridad del Registro de transacciones.
La copia de seguridad completa y todas las siguientes copias de seguridad del registro de transacciones hasta que se tome una nueva copia de seguridad completa se denomina cadena de copia de seguridad. Esta cadena de respaldo es muy importante para poder recuperar la base de datos en un punto específico en el tiempo, en el caso de cualquier cambio realizado incorrectamente o corrupción de la base de datos.
La frecuencia de la copia de seguridad del registro de transacciones va a depender de la importancia de sus datos, el tamaño de la base de datos y el tipo de carga de trabajo a la que sirve esta base de datos. Si la base de datos está dañada, se nos recomienda el crear una copia de seguridad de registro de cola que nos va a permitir restaurar la base de datos al momento actual. Se usa una copia de seguridad de registro de cola para poder capturar todos los registros de registro que aún no se han realizado.
Suponga que ha ejecutado la siguiente declaración DELETE por error sin proporcionar la cláusula WHERE. Si ha diseñado una solución de copia de seguridad adecuada, los datos se pueden recuperar fácilmente restaurando la base de datos al punto específico en el tiempo antes de ejecutar la instrucción DELETE. Esto puede hacerlo desde la ventana Restaurar base de datos, el Servidor SQL va a devolver la cadena de copia de seguridad completa que se toma de esa base de datos. Pero si usted conoce el momento exacto de ejecución de la declaración DELETE, puede restaurar la base de datos a ese punto específico en el tiempo antes de la ejecución de la declaración DELETE, sin tener la necesidad de saber qué archivo de Registro de transacciones contiene ese punto en el tiempo.
Truncamiento del Registro de Transacciones
El truncamiento del Registro de transacciones de SQL Server es el proceso en el que todos los VLF que están marcados como inactivos se van a eliminar del archivo de Registro de transacciones de SQL Server y ya estarán disponibles para su reutilización.
TRUNCATE_ONLY: Opción de copia de seguridad del registro de transacciones, que rompe la cadena de copia de seguridad de la base de datos y trunca los registros de transacciones disponibles.
Cuando el archivo de registro de transacciones de la base de datos se nos trunca, el espacio truncado se liberará y estará disponible para su reutilización. Pero el tamaño del archivo del Registro de transacciones no se va a reducir, ya que el espacio truncado no se desasignará. Entonces, el proceso de recuperación del espacio de registro de transacciones por desasignar los VLFs libres y devolverlo de nuevo al sistema operativo se llama una operación de registro de transacciones de contracción.
La operación de reducción del archivo del Registro de transacciones solo se puede llevar acabo si hay espacio libre en el archivo del Registro de transacciones, que puede estar disponible la mayor parte del tiempo después de truncar la parte inactiva del Registro de transacciones. En la página reducir archivo, tiene que cambiar el Tipo de archivo a Registro y elija el archivo de Registro de transacciones que logre reducir. Liberar espacio no utilizado en el archivo de registro de transacciones al sistema operativo y reduce el archivo a la última extensión asignada. Liberar espacio no utilizado en el archivo de registro de transacciones al sistema operativo e intente reubicar filas en páginas no asignadas.
No es posible reducir el archivo de registro de transacciones a un tamaño menor que el tamaño del archivo de registro virtual, incluso si este espacio no se utiliza. Esto se debe al hecho de que el archivo de registro de transacciones solo puede reducirse al límite del VLF.
Sentencias DML: INSERT, UPDATE, DELETE
Después de la sentencia Select, Insert es la sentencia más importante y utilizada en el lenguaje de manipulación de datos. La sentencia Insert permite añadir datos al esquema, esto es, añadir información a la base de datos. La información se almacenará en forma de filas, al tratarse de un modelo relacional. Si sólo queremos insertar un atributo el resto se almacenarán con el valor Nulo, salvo en el caso que haya atributos declarados como No Nulo, en los cuales deberemos especificar un valor. Otra forma de añadir filas a una base de datos es utilizar el resultado de una consulta. El siguiente código insertaría los valores de la tabla1 en la tabla2.
La sentencia update actualiza los valores una o varias filas de una tabla, sin necesidad de borrarla e insertarla de nuevo. No hace falta especificar un valor a la hora de actualizar uno o varios atributos, sino que se puede usar una expresión.
Con la sentencia delete se borran las filas de una tabla. Para que se borren las filas de una tabla se deben cumplir cada una de las condiciones de seguridad que el administrador determine y se deben cumplir las reglas de integridad referencial (permiten mantener la información de las tablas de la base de datos de forma coherente). Tabla1 es de donde queremos borrar las filas, y condición es la condición que se debe cumplir para que se borren las filas. Como en los dos casos anteriores, si aparece el resultado (1 row(s) affected), los cambios se han realizado correctamente.
TRUNCATE TABLE vs. DELETE
Existe otra sentencia, truncate table, que funciona igual que delete. La instrucción delete elimina una a una cada fila y graba una entrada en el registro de transacciones por cada fila eliminada. Truncate table quita todas las filas de una tabla, pero permanece la estructura y sus columnas, restricciones, índices.
Quita todas las filas de una tabla o las particiones especificadas de una tabla, sin registrar las eliminaciones individuales de filas. Nombre de la tabla que se va a truncar o de la que se quitan todas las filas. table_name debe ser un valor literal. WITH ( PARTITIONS ( {
La DELETE instrucción quita las filas una a la vez y registra una entrada en el registro de transacciones para cada fila eliminada. Cuando la DELETE instrucción se ejecuta mediante un bloqueo de fila, cada fila de la tabla se bloquea para su eliminación. Una vez ejecutada una DELETE instrucción, la tabla todavía puede contener páginas vacías. Por ejemplo, las páginas vacías de un montón no se pueden desasignar sin al menos un bloqueo de tabla exclusivo (LCK_M_X). Si la operación de eliminación no usa un bloqueo de tabla, la tabla (montón) podría contener muchas páginas vacías. En el caso de los índices, la DELETE instrucción puede dejar páginas vacías detrás.
TRUNCATE TABLE quita todas las filas de una tabla, pero la estructura de la tabla y sus columnas, restricciones, índices, etc., permanecen. Si la tabla contiene una columna de identidad, el contador para dicha columna se restablece al valor de inicialización definido para ella. Si no se definió ningún valor de inicialización, se usa el valor 1 predeterminado. Cuando se trunca una tabla que usa 128 extensiones o más, el motor de base de datos aplaza las desasignaciones de página reales y sus bloqueos asociados, hasta después de que la transacción se confirme. El truncamiento se produce en dos fases independientes: lógica y física. En la fase lógica, las unidades de asignación existentes usadas por la tabla y sus índices se marcan para la desasignación y se bloquean hasta que la transacción se confirma. En la fase física, un proceso en segundo plano quita las páginas marcadas para la desasignación. Se hace referencia a ello mediante una FOREIGN KEY restricción . TRUNCATE TABLE no puede activar un desencadenador porque la operación no registra eliminaciones de filas individuales. El permiso mínimo necesario es ALTER en table_name. TRUNCATE TABLE los permisos predeterminados para el propietario de la tabla, los miembros del sysadmin rol fijo de servidor y los db_ownerdb_ddladmin roles fijos de base de datos y no se pueden transferir.
A. En el siguiente ejemplo se quitan todos los datos de la tabla JobCandidate.
B. En el ejemplo siguiente se trunca las particiones especificadas de una tabla con particiones.
C. TRUNCATE TABLE es la sentencia SQL para eliminar todos los registros/filas de una tabla sin afectar al esquema y estructura de la misma. A diferencia de la sentencia DELETE FROM, TRUNCATE TABLE no admite cláusula WHERE, por lo que no se podrán eliminar registros específicos. TRUNCATE TABLE no admitirá en la definición de su sentencia el argumento IF EXISTS. Sin embargo, cuando tenemos tablas particionadas, admitirá el argumento WITH PARTITIONS donde se especificará que partición en concreto truncar.
TRUNCATE TABLE tiene algunas limitaciones que habrá que tener en cuenta y consideración. Ente ellas la más importante y destacada es que no se podrá utilizar dicha sentencia sobre tablas referenciadas por una restricción Foreign Key a otra tabla. No se puede truncar la tabla ‘Clientes’.
DELETE vs TRUNCATE en SQL Server | Diferencias, ejemplos y cuándo usar cada uno
En el próximo artículo de esta serie, analizaremos las mejores prácticas que se deben aplicar al registro de transacciones para poder obtener un rendimiento óptimo.