Eventos
31 mar, 23 - 2 abr, 23
Evento de aprendizaje de SQL, Fabric y Power BI más grande. 31 de marzo – 2 de abril. Use el código FABINSIDER para ahorrar $400.
Regístrate hoyEste explorador ya no se admite.
Actualice a Microsoft Edge para aprovechar las características y actualizaciones de seguridad más recientes, y disponer de soporte técnico.
Se aplica a: SQL Server
Azure SQL Managed Instance
Como mínimo, toda base de datos SQL Server tiene dos archivos de sistema operativo: un archivo de datos y un archivo de registro. Los archivos de datos contienen datos y otros objetos, como tablas, índices, procedimientos almacenados y vistas. Los archivos de registro contienen la información necesaria para recuperar todas las transacciones de la base de datos. Los archivos de datos se pueden agrupar en grupos de archivos para su asignación y administración.
Las bases de datos de SQL Server tienen tres tipos de archivos, como se muestra en la tabla siguiente.
Archivo | Descripción |
---|---|
Principal | Incluye información de inicio de la base de datos y apunta a los demás archivos de la misma. Cada base de datos tiene un archivo de datos principal. La extensión recomendada para los nombres de archivos de datos principales es .mdf . |
Secundario | Archivos de datos opcionales definidos por el usuario. Los datos se pueden distribuir en varios discos colocando cada archivo en una unidad de disco distinta. La extensión de nombre de archivo recomendada para los archivos de datos secundarios es .ndf . |
Registro de transacciones | El registro contiene la información que se utiliza para recuperar la base de datos. Cada base de datos debe tener al menos un archivo de registro. La extensión recomendada para los nombres de archivos de registro es .ldf . |
Por ejemplo, una base de datos sencilla denominada Sales
tiene un archivo principal que contenga todos los datos y objetos y un archivo de registro con la información del registro de transacciones. Puede crearse una base de datos más compleja, Orders
, compuesta por un archivo principal y cinco archivos secundarios. Los datos y objetos de la base de datos se reparten entre los seis archivos, y cuatro archivos de registro adicionales contienen la información del registro de transacciones.
De forma predeterminada, los datos y los registros de transacciones se colocan en la misma unidad y ruta de acceso para manipular los sistemas de disco único, Puede que esta opción no resulte óptima para los entornos de producción. Se recomienda colocar los archivos de datos y de registro en distintos discos.
Los archivos de SQL Server tienen dos tipos de nombre de archivo:
logical_file_name:
logical_file_name
es el nombre que se usa para hacer referencia al archivo físico en todas las instrucciones Transact-SQL. El nombre de archivo lógico tiene que cumplir las reglas de los identificadores de SQL Server y tiene que ser único entre los nombres de archivos lógicos de la base de datos.
os_file_name:
os_file_name
es el nombre del archivo físico que incluye la ruta de acceso al directorio. Debe seguir las reglas para nombres de archivos del sistema operativo.
Para obtener más información sobre NAME
y el argumento FILENAME
, consulte el archivo ALTER DATABASE y las opciones de grupo de archivos (Transact-SQL).
Sugerencia
Los archivos de datos y de registro de SQL Server se pueden colocar en sistemas de archivos FAT o NTFS. En sistemas Windows, Microsoft recomienda usar el sistema de archivos NTFS por las características de seguridad que ofrece.
Advertencia
No se admiten grupos de archivos de datos de lectura o escritura ni archivos de registro en un sistema de archivos NTFS comprimido. Solo las bases de datos de solo lectura y los grupos de archivos secundarios de solo lectura se pueden colocar en un sistema de archivos NTFS comprimido. Para ahorrar espacio, se recomienda encarecidamente usar compresión de datos en lugar de compresión del sistema de archivos.
Cuando se ejecutan varias instancias de SQL Server en un único ordenador, cada instancia recibe un directorio predeterminado diferente para guardar los archivos de las bases de datos creadas en la instancia. Para obtener más información, vea Ubicaciones de archivos para las instancias predeterminadas y con nombre de SQL Server.
Las páginas de un archivo de datos de SQL Server están numeradas secuencialmente, comenzando por cero (0) para la primera página del archivo. Cada archivo de una base de datos tiene un número de identificador único. Para identificar de forma única una página de una base de datos, se requiere el identificador del archivo y el número de la página. El siguiente ejemplo muestra los números de página de una base de datos que tiene un archivo de datos principal de 4 MB y un archivo de datos secundario de 1 MB.
Una página de encabezado de archivo es la primera página que contiene información acerca de los atributos del archivo. Algunas de las otras páginas del comienzo del archivo también contienen información de sistema, como mapas de asignación. Una de las páginas de sistema almacenadas en el archivo de datos principal y en el archivo de registro principal es una página de arranque de la base de datos que contiene información acerca de los atributos de la base de datos.
Los archivos de SQL Server pueden crecer de forma automática a partir del tamaño especificado inicialmente. Cuando se define un archivo, se puede especificar un incremento de crecimiento. Cada vez que se llena el archivo, el tamaño aumenta en la cantidad especificada. Si hay varios archivos en un grupo de archivos, no crecerán automáticamente hasta que todos los archivos estén llenos.
Para obtener más información sobre las páginas y tipos de páginas, vea Guía de arquitectura de páginas y extensiones.
Cada archivo también puede tener un tamaño máximo especificado. Si no se especifica un tamaño máximo, el archivo puede crecer hasta utilizar todo el espacio disponible en el disco. Esta característica es especialmente útil cuando se utiliza SQL Server como base de datos incrustada en una solicitud en la que el usuario no tiene acceso conveniente a un administrador del sistema. El usuario puede dejar que los archivos crezcan automáticamente cuando sea necesario y evitar así las tareas administrativas de supervisar la cantidad de espacio disponible en la base de datos y asignar más espacio manualmente.
Para obtener más información sobre la administración del archivo de registro de transacciones, vea Administrar el tamaño del archivo de registro de transacciones.
La forma de archivo que utiliza una instantánea de base de datos para almacenar sus datos de copia por escritura depende de si la instantánea la ha creado un usuario o se utiliza internamente:
DBCC CHECKDB
, DBCC CHECKTABLE
, DBCC CHECKALLOC
y DBCC CHECKFILEGROUP
. Una instantánea de base de datos interna utiliza flujos de datos alternativos dispersos de los archivos de base de datos originales. Como los archivos dispersos, los flujos de datos alternativos son una característica del sistema de archivos NTFS. El uso de los flujos de datos alternativos dispersos permite que varias asignaciones de datos se asocien a un único archivo o carpeta sin afectar a las estadísticas de tamaño o volumen.Por ejemplo, Data1.ndf
, Data2.ndf
y Data3.ndf
pueden crearse en tres unidades de disco, respectivamente, y asignarse al grupo de archivos fgroup1
. Se puede crear una tabla específicamente para el grupo de archivos fgroup1
. Las consultas de datos de la tabla se distribuirán por los tres discos, con lo que mejorará el rendimiento. Puede obtenerse la misma mejora del rendimiento con un solo archivo creado en un conjunto de bandas RAID (matriz redundante de discos independientes). No obstante, los archivos y grupos de archivos permiten agregar fácilmente nuevos archivos a discos nuevos.
Todos los archivos de datos se almacenan en los grupos de archivos que se indican en la tabla siguiente.
Grupo de archivos | Descripción |
---|---|
Principal | Grupo de archivos que contiene el archivo principal. Todas las tablas del sistema forman parte del grupo de archivos principal. |
Tabla optimizada para memoria | Un grupo de archivos optimizados para memoria está basado en un grupo de archivos de FILESTREAM. |
Secuencia de archivos | |
Definidas por el usuario | Cualquier grupo de archivos creado por el usuario al crear la base de datos o al modificarla. |
Cuando se crean objetos en la base de datos sin especificar a qué grupo de archivos pertenecen, se asignan al grupo de archivos predeterminado. Siempre existe un grupo de archivos designado como predeterminado. Los archivos del grupo de archivos predeterminado deben ser lo suficientemente grandes como para dar cabida a todos los objetos nuevos no asignados a otros grupos de archivos.
El grupo de archivos PRINCIPAL es el predeterminado, a menos que se cambie mediante la instrucción ALTER DATABASE. Los objetos y las tablas del sistema no se asignan al nuevo grupo de archivos predeterminado, sino que siguen asignados al grupo de archivos PRIMARY.
Para obtener más información sobre los grupos de archivos optimizados para memoria, vea Grupo de archivos optimizados para memoria.
Para obtener más información sobre los grupos de archivos FILESTREAM, consulte FILESTREAM y Crear una base de datos habilitada para FILESTREAM.
En el siguiente ejemplo se crea una base de datos en una instancia de SQL Server. La base de datos tiene un archivo de datos principal, un grupo de archivos definido por el usuario y el archivo de registro. El archivo de datos principal está en el grupo de archivos principal y el grupo de archivos definido por el usuario tiene dos archivos de datos secundarios. Una instrucción ALTER DATABASE
hace que el grupo de archivos definido por el usuario sea el grupo predeterminado. A continuación, se crea una tabla que especifica el grupo de archivos definido por el usuario. (En este ejemplo se usa una ruta de acceso genérica c:\Program Files\Microsoft SQL Server\MSSQL.1
para evitar que se especifique una versión de SQL Server).
USE master;
GO
-- Create the database with the default data
-- filegroup, filestream filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME=
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
( NAME = 'MyDB_FG1_Dat2',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
( NAME = 'MyDB_FG_FS',
FILENAME = 'c:\Data\filestream1')
LOG ON
( NAME='MyDB_log',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB
MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO
-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1;
GO
-- Create a table in the filestream filegroup
CREATE TABLE MyFSTable
(
cola int PRIMARY KEY,
colb VARBINARY(MAX) FILESTREAM NULL
)
GO
La siguiente ilustración resume los resultados del ejemplo anterior, excepto para datos FILESTREAM.
Los grupos de archivos utilizan una estrategia de relleno proporcional entre todos los archivos de cada grupo de archivos. A medida que se escriben datos en el grupo de archivos, el motor de base de datos SQL Server escribe una cantidad proporcional al espacio libre del archivo en cada archivo del grupo de archivos, en lugar de escribir todos los datos en el primer archivo hasta que esté completo. A continuación, escribe en el siguiente archivo. Por ejemplo, si el archivo f1
tiene 100 MB de espacio disponible y el archivo f2
tiene 200 MB de espacio disponible, se dará una extensión del archivo f1
, dos extensiones del archivo f2
, etc. De ese modo, los dos archivos se llenarán aproximadamente al mismo tiempo y se conseguirá crear una banda simple.
Por ejemplo, un grupo de archivos consta de tres archivos configurados para crecer automáticamente. Cuando se agota el espacio de todos los archivos del grupo de archivos, solo se expande el primer archivo. Cuando se llena el primer archivo y no se pueden escribir más datos en el grupo de archivos, se expande el segundo archivo. Cuando se llena el segundo archivo y no pueden escribirse más datos en el grupo de archivos, se expande el tercer archivo. Si se llena el tercer archivo y no se pueden escribir más datos en el grupo de archivos, se vuelve a expandir el primer archivo, y así sucesivamente.
Las siguientes reglas afectan a los archivos y grupos de archivos:
sales.mdf
y sales.ndf
, que contienen datos y objetos de la base de datos sales, no pueden ser utilizados por otra base de datos.Recomendaciones para trabajar con archivos y grupos de archivos:
DBCC CHECKDB
para garantizar la integridad física de todas las bases de datos que residen en cada volumen.Para obtener más recomendaciones sobre la administración del archivo de registro de transacciones, vea Administrar el tamaño del archivo de registro de transacciones.
Eventos
31 mar, 23 - 2 abr, 23
Evento de aprendizaje de SQL, Fabric y Power BI más grande. 31 de marzo – 2 de abril. Use el código FABINSIDER para ahorrar $400.
Regístrate hoyCursos
Módulo
Configuración de recursos de SQL Server para obtener un rendimiento óptimo - Training
Configuración de recursos de SQL Server para obtener un rendimiento óptimo
Certificación
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administre una infraestructura de base de datos de SQL Server para bases de datos relacionales locales e híbridas en la nube mediante las ofertas de bases de datos relacionales PaaS de Microsoft.
Documentación
Utilizar grupos de archivos y archivos para almacenar datos - SQL Server
Utilizar grupos de archivos y archivos para almacenar datos
Agregar archivos de datos o de registro a una base de datos - SQL Server
Obtenga información sobre cómo agregar datos o archivos de registro a una base de datos en SQL Server 2019 mediante SQL Server Management Studio o Transact-SQL.
Archivos y grupos de archivos ALTER DATABASE - SQL Server (Transact-SQL)
Actualice los archivos y los grupos de archivos de una base de datos mediante Transact-SQL.