El Objeto de Base de Datos

Al crear una base de datos, esta se guarda en al menos dos archivos separados. Un archivo contiene los datos, tablas del sistema y demás objetos de la base de datos, y el otro archivo guarda el registro de transacciones.

La base de datos SQL Server

Cuando se crea una base de datos SQL se debe estimar el tamaño que ésta alcanzará para crear los archivos necesarios.

• Toda base de datos tiene un archivo primario de datos (.mdf) y uno o mas archivos para el log de transacciones (.ldf). También puede tener archivos secundarios de datos (.ndf).
• Cuando crea una base de datos, una copia de la base de datos model que incluye las tablas del sistema se copia a la nueva base de datos. El tamaño minimo de una base de datos debe ser de un tamaño mayor que la base de datos model.
• Los archivos de la base de datos tienen un nombre de archivo que es el utilizado por el sistema operativo, y un nombre lógico que es utilizado por SQL Server para referirse al archivo.
• La data es almacenada por páginas (pages). Cada página tiene capacidad para 8 KB.
• Una fila no puede almacenarse en dos páginas. El tamaño máximo de una fila es de 8092 bytes.
• Las tablas y los índices se almacenan por extensiones (extents). Una extensión es un conjunto de 8 páginas contiguas. Esto significa que dos objetos de la base de datos no pueden compartir una extensión, salvo el caso de las tablas muy pequeñas.
• Los archivos del log de transacciones almacenan la información necesaria para la recuperación de la base de datos ante la eventualidad de una falla del sistema. Por defecto, el tamaño del log de transacciones es del 25% del tamaño de los archivos de datos.

El Registro de Transacciones (Transaction Log)

El archivo de registro de transacciones almacena todas las transacciones llevadas a cabo con las sentencias INSERT, UPDATE y DELETE. El proceso se lleva a cabo de la siguiente manera:

• Una aplicación envía una modificación de datos al servidor SQL.
• Las páginas de datos afectadas por la modificación son localizadas en el caché, o leídas desde el disco al caché, si no se encuentran en ella.
• Cada sentencia de modificación de datos es registrada en el log de transacciones antes que el cambio se lleve a cabo en la base de datos.
• El punto de control (checkpoint) escribe las transacciones confirmadas (commited) en la base de datos.
• Si el sistema falla, el proceso de recuperación automático usa el Iog de transacciones para recuperar todas las transacciones comprometidos y anula todas las transacciones incompletas.

Los grupos de archivos

Los archivos de una base de datos se pueden agrupar en grupos de archivos para su asignación y administración. Algunos sistemas pueden aumentar su rendimiento controlando la ubicación de datos e índices en unidades de disco específicas. Los grupos de archivos pueden resultar de ayuda en este proceso.

El administrador del sistema puede crear grupos de archivos en cada unidad de disco, después asignar tablas específicas, índices o datos text, ntext o image de una tabla, a grupos de archivos específicos. Ningún archivo puede pertenecer a más de un grupo de archivos. Las tablas, los índices y los datos text, ntext e image, se pueden asociar a un grupo de archivos, en cuyo caso todas sus páginas estarán asignadas a dicho grupo de archivos.

Los archivos de registro nunca forman parte de un grupo de archivos. El espacio del registro se administra de forma separada del espacio de datos. Los archivos de un grupo de archivos no crecen automáticamente mientras haya espacio disponible en alguno de los archivos del grupo de archivos. Hay dos tipos de grupos de archivos: 

Primary

El grupo de archivos principal contiene el archivo de datos principal y los demás archivos asignados específicamente a otro grupo de archivos. Todas las páginas de las tablas del sistema están asignadas al grupo de archivos principal.

Definidos por el usuario

Los grupos de archivos definidos por el usuario son los grupos de archivos especificados mediante la palabra clave FILEGROUP en las instrucciones 
CREATE DATABASE o ALTER DATABASE

Creación de bases de datos

Para crear una base de datos en SQL Server 2000 utilice una de las tres herramientas siguientes:

• El Asistente para la creación de base de datos
• El Administrador Corporativo de SQL Server
• La Instrucción CREATE DATABASE

Para crear una base de datos debe definir:

• El nombre de la base de datos
• El tamaño de la base de datos
• Los archivos dónde la base de datos va a residir

La sentencia CREATE DATABASE

CREATE DATABASE nombre_bd

ON PRIMARY

( NAME = nombre_lógico_data ,

FILENAME = ‘ubicación_y_nombre_archivo’ ,

SIZE = tamaño_en_MB ,

MAXSIZE = tamaño_máximo_en_MB ,

FILEGROWTH = incremento_crecimiento )

LOG ON

( NAME = nombre_lógico_log ,

FILENAME = ‘ubicación_y_nombre_archivo’ ,

SIZE = tamaño_en_MB ,

MAXSIZE = tamaño_máximo_en_MB ,

FILEGROWTH = incremento_crecimiento )

• nombre_lógico_data, nombre_lógico_log es el nombre a utilizar cuando en una sentencia SQL se tiene que hacer referencia al archivo de datos o al archivo de log respectivamente.
• ubicación_y_nombre_archivo es una cadena que incluye la ruta y el nombre del archivo. La ruta debe especificar una carpeta existente en el servidor en el que está instalado SQL.
• tamaño_en_MB especifica el tamaño del archivo.
• tamaño_máximo_en_MB es el máximo tamaño que puede alcanzar el archivo si se requiriera de espacio adicional.
• incremento_crecimiento es la cantidad de espacio que se añade al archivo cada vez que se necesita espacio adicional. Se puede especificar en MB o en porcentaje.

Ejemplo:

CREATE DATABASE biblioteca
ON PRIMARY
( NAME = biblioteca_data ,

FILENAME = ‘c:\mssql7\data\practica\biblioteca.mdf’ ,

SIZE = 10 ,

MAXSIZE = 15 ,

FILEGROWTH = 25% )

LOG ON

( NAME = biblioteca_log ,

FILENAME = ‘c:\mssql7\data\practica\biblioteca.ldf’ ,

SIZE = 4 ,

MAXSIZE = 6 ,

FILEGROWTH = 1MB )

Cuando se crea una base de datos, su nombre se registra en la tabla de sistema sysdatabases de la base de datos master.

Configuración de la base de datos

En algunas ocasiones necesitará cambiar la configuración de la base de datos para poder realizar alguna tarea especial. Si desea conocer qué opciones puede configurar para su base de datos, ejecute el procedimiento. sp_dboption.

Algunas de esas opciones pueden configurarse desde SQL Server Enterprise Manager. Todas pueden configurarse mediante el procedimiento sp_dboption.

Opciones mas frecuentemente utilizada

Ejemplo:

Si deseamos que en el log de transacciones de nuestra base de datos  biblioteca no se consuma el espacio rápidamente, podemos configurar la opción trunc. log on chkpt. de manera tal que todas aquellas transacciones para las que se verificó el commit sean removidas del log.

Para ello, ejecutamos la siguiente orden:

sp_dboption biblioteca, 'trunc. log on chkpt.', TRUE

Para verificar qué opciones están configuradas a TRUE en la base de datos, ejecutamos:

sp_dboption biblioteca

Los siguientes procedimientos pueden ser utilizados para obtener información acerca de las bases de datos:

Administración del crecimiento de la base de datos

Incremento del tamaño de un archivo de la base de datos

ALTER DATABASE nombre_bd

MODIFY FILE ( NAME = nombre_lógico_archivo ,

SIZE = nuevo_tamaño_en_MB )

Adición de un archivo a la base de datos

ALTER DATABASE nombre_bd

ADD FILE | ADD LOG FILE

( NAME = nombre_lógico_archivo ,

FILENAME = ‘ubicación_y_nombre_archivo’ ,

SIZE = tamaño_en_MB ,

MAXSIZE = tamaño_máximo_en_MB ,

FILEGROWTH = incremento_crecimiento )

Eliminación de un archivo de base de datos

ALTER DATABASE nombre_bd

REMOVE FILE nombre_lógico_archivo

La eliminación del archivo solo puede ejecutarse si el mismo está vacío.

Reducción del tamaño de una base de datos

DBCC SHRINKDATABASE ( nombre_bd , porcentaje_libre_final )

porcentaje_libre_final representa el porcentaje de espacio libre después de la reducción del tamaño.

Reducción del tamaño de un archivo de base de datos

DBCC SHRINKFILE ( nombre_lógico_archivo , tamaño_final_en_MB )

Revisión de la arquitectura física de la base de datos

Todas las bases de datos del servidor

sp_helpdb

Información de una base de datos específica

sp_helpdb nombre_bd

Archivos de la base de datos

sp_helpfile

Espacio utilizado por los datos de la base de datos

sp_spaceused

Espacio utilizado por el log de transacciones de la base de datos

DBCC SQLPERF (logspace)

Eliminación de bases de datos

Al eliminar una base de datos, elimina la base de datos y los archivos usados por esta base de datos.

Métodos de eliminación de una base de datos

Puede eliminar una base de datos usando SQL Server Enterprise Manager o ejecutando la sentencia DROP DATABASE.

Sintáxis

DROP DATABASE nombre_base_datos [ , ...n ]

Ejemplo

Este ejemplo elimina varias bases de datos usando una sola sentencia.

DROP DATABASE planilla, northwind, pubs

Cuando elimine una base de datos, tenga en cuenta las siguientes pautas:

• Con SQL Server Enterprise Manager, puede eliminar solo una base de datos en el momento.
• Con Transact-SQL, puede eliminar varias bases de datos en seguida.
• Después que elimine una base de datos, cada login ID que usó en esa base de datos en particular se cambia en la base de datos master.

Restricciones para eliminar una base de datos

No puede eliminar una base de datos si:

• la base de datos está en proceso de restauración.
• la base de datos está siendo utilizada por algún usuario.
• la base de datos está publicando cualquiera de sus tablas como parte de un proceso de replicación.

Aunque SQL Server le permite eliminar la base de datos del sistema msdb, no debe eliminarla si usa o piensa usar:

• SQL Server Agent.
• Replication
• SQL Server Web Wizard
• Data Transformation Services (DTS)