Archivos y grupos de archivos de base de datosDatabase Files and Filegroups

Como mínimo, todas las bases de datos de SQL ServerSQL Server tienen dos archivos del sistema operativo: un archivo de datos y un archivo de registro.At a minimum, every SQL ServerSQL Server database has two operating system files: a data file and a log file. Los archivos de datos contienen datos y otros objetos, como tablas, índices, procedimientos almacenados y vistas.Data files contain data and objects such as tables, indexes, stored procedures, and views. Los archivos de registro contienen la información necesaria para recuperar todas las transacciones de la base de datos.Log files contain the information that is required to recover all transactions in the database. Los archivos de datos se pueden agrupar en grupos de archivos para su asignación y administración.Data files can be grouped together in filegroups for allocation and administration purposes.

Archivos de la base de datosDatabase Files

SQL ServerSQL Server tienen tres tipos de archivos, tal como se muestra en la tabla siguiente. databases have three types of files, as shown in the following table.

ArchivoFile DescripciónDescription
PrincipalPrimary El archivo de datos principal incluye la información de inicio de la base de datos y apunta a los demás archivos de la misma.The primary data file contains the startup information for the database and points to the other files in the database. Los datos y objetos del usuario se pueden almacenar en este archivo o en archivos de datos secundarios.User data and objects can be stored in this file or in secondary data files. Cada base de datos tiene un archivo de datos principal.Every database has one primary data file. La extensión recomendada para los nombres de archivos de datos principales es .mdf.The recommended file name extension for primary data files is .mdf.
SecundarioSecondary Los archivos de datos secundarios son opcionales, están definidos por el usuario y almacenan los datos del usuario.Secondary data files are optional, are user-defined, and store user data. Se pueden utilizar para distribuir datos en varios discos colocando cada archivo en una unidad de disco distinta.Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Además, si una base de datos supera el tamaño máximo establecido para un archivo de Windows, puede utilizar los archivos de datos secundarios para permitir el crecimiento de la base de datos.Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow.

La extensión de nombre de archivo recomendada para los archivos de datos secundarios es .ndf.The recommended file name extension for secondary data files is .ndf.
Registro de transaccionesTransaction Log Los archivos del registro de transacciones contienen la información de registro que se utiliza para recuperar la base de datos.The transaction log files hold the log information that is used to recover the database. Cada base de datos debe tener al menos un archivo de registro.There must be at least one log file for each database. La extensión recomendada para los nombres de archivos de registro es .ldf.The recommended file name extension for transaction logs is .ldf.

Por ejemplo, puede crearse una base de datos sencilla denominada Ventas con un archivo principal que contenga todos los datos y objetos y un archivo de registro con la información del registro de transacciones.For example, a simple database named Sales can be created that includes one primary file that contains all data and objects and a log file that contains the transaction log information. Por otra parte, puede crearse una base de datos más compleja, Pedidos , compuesta por un archivo principal y cinco archivos secundarios.Alternatively, a more complex database named Orders can be created that includes one primary file and five secondary files. 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.The data and objects within the database spread across all six files, and the four log files contain the transaction log information.

De forma predeterminada, los datos y los registros de transacciones se colocan en la misma unidad y ruta de accesoBy default, the data and transaction logs are put on the same drive and path. para administrar los sistemas de un solo disco,This is done to handle single-disk systems. pero puede que esto no resulte óptimo para los entornos de producción.However, this may not be optimal for production environments. Se recomienda colocar los archivos de datos y de registro en distintos discos.We recommend that you put data and log files on separate disks.

Nombres de archivo lógico y físicoLogical and Physical File Names

Los archivos de SQL Server tienen dos nombres:SQL Server files have two names:

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.logical_file_name: The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. 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.The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name : os_file_name es el nombre del archivo físico que incluye la ruta de acceso al directorio.os_file_name: The os_file_name is the name of the physical file including the directory path. Debe seguir las reglas para nombres de archivos del sistema operativo.It must follow the rules for the operating system file names.

Los archivos de datos y de registro de SQL Server se pueden colocar en sistemas de archivos FAT o NTFS.SQL Server data and log files can be put on either FAT or NTFS file systems. Se recomienda utilizar el sistema de archivos NTFS por las características de seguridad que ofrece.We recommend using the NTFS file system because the security aspects of NTFS. No se pueden colocar grupos de archivos de datos de lectura/escritura, y archivos de registro, en un sistema de archivos NTFS comprimido.Read/write data filegroups and log files cannot be placed on an NTFS compressed file system. 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.Only read-only databases and read-only secondary filegroups can be put on an NTFS compressed file system.

Cuando se ejecutan varias instancias de SQL Server en un único equipo, cada instancia recibe un directorio predeterminado diferente para albergar los archivos de las bases de datos creadas en la instancia.When multiple instances of SQL Server are run on a single computer, each instance receives a different default directory to hold the files for the databases created in the instance. Para obtener más información, vea Ubicaciones de archivos para las instancias predeterminadas y con nombre de SQL Server.For more information, see File Locations for Default and Named Instances of SQL Server.

Páginas de archivo de datosData File Pages

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.Pages in a SQL Server data file are numbered sequentially, starting with zero (0) for the first page in the file. Cada archivo de una base de datos tiene un número de identificador único.Each file in a database has a unique file ID number. 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.To uniquely identify a page in a database, both the file ID and the page number are required. 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.The following example shows the page numbers in a database that has a 4-MB primary data file and a 1-MB secondary data file.

data_file_pages

La primera página de cada archivo es una página de encabezado de archivo que contiene información acerca de los atributos del archivo.The first page in each file is a file header page that contains information about the attributes of the file. Algunas de las otras páginas del comienzo del archivo también contienen información de sistema, como mapas de asignación.Several of the other pages at the start of the file also contain system information, such as allocation maps. 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.One of the system pages stored in both the primary data file and the first log file is a database boot page that contains information about the attributes of the database. Para obtener más información sobre las páginas y los tipos de páginas, vea Descripción de páginas y extensiones.For more information about pages and page types, see Understanding Pages and Extents.

Tamaño de archivoFile Size

Los archivos de SQL Server pueden crecer de forma automática a partir del tamaño especificado inicialmente.SQL Server files can grow automatically from their originally specified size. Cuando se define un archivo, se puede especificar un incremento de crecimiento.When you define a file, you can specify a specific growth increment. Cada vez que se llena el archivo, el tamaño aumenta en la cantidad especificada.Every time the file is filled, it increases its size by the growth increment. Si hay varios archivos en un grupo de archivos, no crecerán automáticamente hasta que todos los archivos estén llenos.If there are multiple files in a filegroup, they will not autogrow until all the files are full. A continuación, el crecimiento tiene lugar por turnos.Growth then occurs in a round-robin fashion.

Cada archivo también puede tener un tamaño máximo especificado.Each file can also have a maximum size specified. Si no se especifica un tamaño máximo, el archivo puede crecer hasta utilizar todo el espacio disponible en el disco.If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. Esta característica es especialmente útil cuando SQL Server se usa como una base de datos incrustada en una aplicación para la que el usuario no dispone fácilmente de acceso a un administrador del sistema.This feature is especially useful when SQL Server is used as a database embedded in an application where the user does not have convenient access to a system administrator. 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.The user can let the files autogrow as required to reduce the administrative burden of monitoring free space in the database and manually allocating additional space.

Archivos de instantáneas de bases de datosDatabase Snapshot Files

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:The form of file that is used by a database snapshot to store its copy-on-write data depends on whether the snapshot is created by a user or used internally:

  • Una instantánea de base de datos que crea un usuario almacena sus datos en uno o más archivos dispersos.A database snapshot that is created by a user stores its data in one or more sparse files. La tecnología de archivos dispersos es una característica del sistema de archivos NTFS.Sparse file technology is a feature of the NTFS file system. Al principio, un archivo disperso no incluye datos de usuario y no se le asigna espacio en disco.At first, a sparse file contains no user data, and disk space for user data has not been allocated to the sparse file. Para obtener información general sobre el uso de los archivos dispersos en instantáneas de bases de datos y el crecimiento de estas, vea Ver el tamaño del archivo disperso de una instantánea de base de datos (Transact-SQL).For general information about the use of sparse files in database snapshots and how database snapshots grow, see View the Size of the Sparse File of a Database Snapshot.
  • Las instantáneas de bases de datos las utilizan internamente algunos comandos DBCC.Database snapshots are used internally by certain DBCC commands. Entre estos comandos se incluyen: DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC y DBCC CHECKFILEGROUP.These commands include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKFILEGROUP. Una instantánea de base de datos interna utiliza flujos de datos alternativos dispersos de los archivos de base de datos originales.An internal database snapshot uses sparse alternate data streams of the original database files. Como los archivos dispersos, los flujos de datos alternativos son una característica del sistema de archivos NTFS.Like sparse files, alternate data streams are a feature of the NTFS file system. 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.The use of sparse alternate data streams allows for multiple data allocations to be associated with a single file or folder without affecting the file size or volume statistics.

Grupos de archivosFilegroups

Cada base de datos tiene un grupo de archivos principal.Every database has a primary filegroup. Este grupo de archivos contiene el archivo de datos principal y cualquier otro archivo secundario que no se encuentre en otro grupo de archivos.This filegroup contains the primary data file and any secondary files that are not put into other filegroups. Se pueden crear grupos de archivos definidos por el usuario para agrupar archivos con fines administrativos y de asignación y ubicación de datos.User-defined filegroups can be created to group data files together for administrative, data allocation, and placement purposes.

Por ejemplo, pueden crearse tres archivos, Datos1.ndf, Datos2.ndf y Datos3.ndf, en tres unidades de disco respectivamente para asignarlos posteriormente al grupo de archivos grArchivos1.For example, three files, Data1.ndf, Data2.ndf, and Data3.ndf, can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A continuación, se puede crear una tabla específicamente para el grupo de archivos grArchivos1.A table can then be created specifically on the filegroup fgroup1. Las consultas de datos de la tabla se distribuirán por los tres discos, con lo que mejorará el rendimiento.Queries for data from the table will be spread across the three disks; this will improve performance. Puede obtenerse la misma mejora del rendimiento con un solo archivo creado en un conjunto de bandas RAID (matriz redundante de discos independientes).The same performance improvement can be accomplished by using a single file created on a RAID (redundant array of independent disks) stripe set. No obstante, los archivos y grupos de archivos permiten agregar fácilmente nuevos archivos a discos nuevos.However, files and filegroups let you easily add new files to new disks.

Todos los archivos de datos se almacenan en los grupos de archivos que se indican en la tabla siguiente.All data files are stored in the filegroups listed in the following table.

Grupo de archivosFilegroup DescripciónDescription
PrincipalPrimary Grupo de archivos que contiene el archivo principal.The filegroup that contains the primary file. Todas las tablas del sistema se asignan al grupo de archivos principal.All system tables are allocated to the primary filegroup.
Definidos por el usuarioUser-defined Cualquier grupo de archivos creado específicamente por el usuario al crear la base de datos o al modificarla.Any filegroup that is specifically created by the user when the user first creates or later modifies the database.

Grupo de archivos predeterminadoDefault Filegroup

Cuando se crean objetos en la base de datos sin especificar a qué grupo de archivos pertenecen, se asignan al grupo de archivos predeterminado.When objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup. Siempre existe un grupo de archivos designado como predeterminado.At any time, exactly one filegroup is designated as the default filegroup. 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.The files in the default filegroup must be large enough to hold any new objects not allocated to other filegroups.

El grupo de archivos PRINCIPAL es el predeterminado, a menos que se cambie mediante la instrucción ALTER DATABASE.The PRIMARY filegroup is the default filegroup unless it is changed by using the ALTER DATABASE statement. 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.Allocation for the system objects and tables remains within the PRIMARY filegroup, not the new default filegroup.

Ejemplo de archivos y grupos de archivosFile and Filegroup Example

En el siguiente ejemplo se crea una base de datos en una instancia de SQL Server.The following example creates a database on an instance of 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.The database has a primary data file, a user-defined filegroup, and a log file. 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.The primary data file is in the primary filegroup and the user-defined filegroup has two secondary data files. Una instrucción ALTER DATABASE hace que el grupo de archivos definido por el usuario sea el grupo predeterminado.An ALTER DATABASE statement makes the user-defined filegroup the default. A continuación, se crea una tabla que especifica el grupo de archivos definido por el usuario.A table is then created specifying the user-defined filegroup. (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).(This example uses a generic path c:\Program Files\Microsoft SQL Server\MSSQL.1 to avoid specifying a version of SQL Server.)

USE master;
GO
-- Create the database with the default data
-- 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)
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

La siguiente ilustración resume los resultados del ejemplo anterior.The following illustration summarizes the results of the previous example.

filegroup_example

CREATE DATABASE (Transact-SQL de SQL Server)CREATE DATABASE (SQL Server Transact-SQL)

Opciones File y Filegroup de ALTER DATABASE (Transact-SQL)ALTER DATABASE File and Filegroup Options (Transact-SQL)

Adjuntar y separar bases de datos (SQL Server)Database Detach and Attach (SQL Server)