基础知识,2005中的文件和文件组

数据库是数据的仓库,用于存储数据,而存储数据需要媒介,现在的存储媒介,最常用的是硬盘,土豪一点的服务器使用固态硬盘(SSD),特殊用途的服务器使用内存。数据库最常用的存储文件是数据文件和日志文件,数据文件用于存储数据,由一个主数据文件(.mdf)和若干个辅助数据文件(.ndf)构成;日志文件用于存储事物日志,由.ldf文件构成。不同的文件可以存分布到不同的物理硬盘上,这样便于分散硬盘IO,提高数据的读取速度。

SQL Server的逻辑存储结构为文件组(file group)、区(extent)、数据页(data page)。
  SQL Server 将数据库映射为一组操作系统文件。数据和日志信息绝不混合在同一个文件中,而且一个文件只由一个数据库使用。文件组是文件的命名集合,用于简化数据存放和管理任务(例如,备份和还原操作)。

 

一、配置管理器

数据文件的组合,称作文件组(File Group),数据库不能直接设置存储数据的数据文件,而是通过文件组来指定。

数据库文件

SQL Server 数据库具有三种类型的文件:

  • 主数据文件
    主数据文件是数据库的起点。除了存储系统以及用户数据以外,主数据文件还存储了数据库中的所有辅助数据文件以及重做日志文件的路径、名称、大小等信息。SQL Server通过读取主数据文件得到其他数据文件及重做日志文件的信息,这个功能与Oracle控制文件相似。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是 .mdf。
  • 次要数据文件
    除主数据文件以外的所有其他数据文件都是次要数据文件,次数据文件一般只存储用户数据。某些数据库可能不含有任何次要数据文件,而有些数据库则含有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
  • 日志文件
    日志文件包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件,当然也可以有多个。日志文件的推荐文件扩展名是 .ldf。

SQL Server 不强制使用 .mdf、.ndf 和 .ldf 文件扩展名,但使用它们有助于标识文件的各种类型和用途。
  在 SQL Server 中,数据库中所有文件的位置都记录在数据库的主文件和 master 数据库中。大多数情况下,SQL Server 数据库引擎使用 master 数据库中的文件位置信息。但是,在下列情况下,数据库引擎使用主文件的文件位置信息初始化 master 数据库中的文件位置项:

  • 使用带有 FOR ATTACH 或 FOR ATTACH_REBUILD_LOG 选项的 CREATE DATABASE 语句来附加数据库时。
  • 从 SQL Server 2000 版或 7.0 版升级时。
  • 还原 master 数据库时。

1、文件和文件组的含义与关系

 

一,文件组和文件的作用

SQL Server的数据存储在文件中,文件是实际存储数据的物理实体,文件组是逻辑对象,SQL Server通过文件组来管理文件。

图片 1

 

一个数据库有一个或多个文件组,主文件组(Primary File Group)是系统自动创建的,用户可以根据需要添加文件组。每一个文件组管理一个或多个文件,其中主文件组中包含主数据文件(master data file),扩展名是.mdf,这个文件是系统默认生成的,并且在数据库中是唯一的;辅助文件的扩展名是.ndf,是用户根据需要添加的。主文件组中也可以包含辅助文件,除了主文件组之外,其他文件组只能包含辅助文件。

例如,查看示例数据库的文件组,Primary 是住文件组,勾选Default表示住文件组是默认的文件组,这意味着,如果在create table和create index中没有指定FileGroup选项,那么SQL Server将使用默认的文件组来存储数据。

图片 2

文件组是一个逻辑实体,实际上,数据存储在文件中(.mdf和.ndf)中,每一个文件组中都包含文件,因此,在create table和create index命令中指定文件组,那么数据就会存储到文件组包含的文件中。

图片 3

从上图中可以看到,数据库文件的元数据:

  • Logical Name是数据文件的逻辑名称,用于数据压缩 DBCC ShrinkFile等;
  • 数据库文件有两种类型:Rows Data(存储数据)和Log(存储日志),
  • Initial Size是文件的初始大小,
  • Autogrowth表示文件自动增加的大小,Maxsize是文件大小的最大值,
  • Path参数表示文件的路径,
  • File Name是文件的物理名称,逻辑名称和物理名可以是不同的。

用户也可以使用sys.database_filessys.filegroups 查看数据的文件和文件组的元数据。

数据库文件组

为便于分配和管理,可以将数据库对象和文件一起分成文件组。SQL Server的文件组由若干个数据文件组成。
  SQL Server的文件组分为primary文件组和用户文件组,分别对应Oracle数据库中的system表空间和用户表空间。

  • primary文件组
    主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。与Oracle数据库的system表空间相似,primary文件组不能删除,其名称primary也是固定不能修改的。
  • 用户定义文件组
    用户定义文件组是通过在 CREATE DATABASE 或 ALTER DATABASE 语句中使用 FILEGROUP 关键字指定的任何文件组。

日志文件不包括在文件组内。日志空间与数据空间分开管理。
  SQL Server数据库中没有对应于Oracle临时表空间的文件组,SQL Server的多版本数据(undo)以及排序或散列操作所产生的临时数据都存储于tempdb系统数据库中,多个数据库共用tempdb数据库。

一个文件不可以是多个文件组的成员。表、索引和大型对象数据可以与指定的文件组相关联。在这种情况下,它们的所有页将被分配到该文件组,或者对表和索引进行分区。已分区表和索引的数据被分割为单元,每个单元可以放置在数据库中的单独文件组中。
  在 SQL Server数据库中,不允许删除包含表或索引的文件组,这与Oracle不同,在Oracle中,如果表空间中包含数据,使用drop tablespace删除表空间时,可以附加including contents子句。
  每个数据库中均有一个文件组被指定为默认文件组。如果创建表或索引时未指定文件组,则将假定所有页都从默认文件组分配。一次只能有一个文件组作为默认文件组。如果没有指定默认文件组,则将主文件组作为默认文件组。db_owner 固定数据库角色成员可以将默认文件组从一个文件组切换到另一个。

文件和文件组的设计规则
下列规则适用于文件和文件组:

  • 一个文件或文件组不能由多个数据库使用。例如,任何其他数据库都不能使用包含 sales 数据库中的数据和对象的文件 sales.mdf 和 sales.ndf。
  • 一个文件只能是一个文件组的成员。
  • 事务日志文件不能属于任何文件组。

每个数据库有一个主数据文件.和若干个从文件。文件是数据库的物理体现。 文件组可以包括分布在多个逻辑分区的文件,实现负载平衡。文件组允许对文件进行分组,以便于管理和数据的分配/放置。例如,可以分别在三个硬盘驱动器上创建三个文件(Data1.ndf、Data2.ndf 和 Data3.ndf),并将这三个文件指派到文件组 fgroup1 中。然后,可以明确地在文件组 fgroup1 上创建一个表。对表中数据的查询将分散到三个磁盘上,因而性能得以提高。在 RAID(磁盘冗余阵列)条带集上创建单个文件也可以获得相同的性能改善。然而,文件和文件组使您得以在新磁盘上轻易地添加新文件。另外,如果数据库超过单个 Microsoft Windows 文件的最大大小,则可以使用次要数据文件允许数据库继续增长。

1.管理服务

二,使用文件组的优势

在实际开发数据库的过程中,通常情况下,用户需要关注文件组,而不用关心文件的物理存储,即使DBA改变文件的物理存储,用户也不会察觉到,也不会影响数据库去执行查询。除了逻辑文件和物理文件的分离之外,SQL Server使用文件组还有一个优势,那就是分散IO负载,其实现的原理是:

  • 对于单分区表,数据只能存到一个文件组中。如果把文件组内的数据文件分布在不同的物理硬盘上,那么SQL Server能同时从不同的物理硬盘上读写数据,把IO负载分散到不同的硬盘上。
  • 对于多分区表,每个分区使用一个文件组,把不同的数据子集存储在不同的磁盘上,SQL Server在读写某一个分组的数据时,能够调用不同的硬盘IO。

这两种方式,其本质上,都是使每个硬盘均摊系统负载,提高IO性能。

创建分区表时,不同的分区可以使用相同的文件组,也可以使用不同的文件组。因此,在设计文件组时,应尽量把包含的文件包含在不同的硬盘上,以实现物理IO的最大分散化。

在创建文件时,服务器CPU核的数量,决定最大的并发IO度,应该根据CPU 核的数量创建多个文件。通常情况下,文件的数量和CPU核的数量一致,是最优化的设计。

还有,应该根据硬盘的性能来创建文件组,日志文件存储到性能最好的硬盘上,而查询延迟要求高的数据,也需要存储到性能最好的硬盘上。

不是所有的数据都是同等重要的,应该根据业务需求和查询延迟,对数据分级,因此,在设计文件组时,应该把级别高的数据分散,而把那些基本用不到的数据存储到性能差的,用于存储归档数据的硬盘上,以实现服务器性能的合理配置。

区(extent)

extent是给表或索引分配存储空间的单位,也是管理空间的基本单位。
  在SQL Server中,extent的大小是固定的8个连续的数据页,64KB,这意味着 SQL Server 数据库中每 MB 有 16 个区。在创建文件组时,不能指定类似Oracle中的autoallocate或uniform size子句定义extent的大小,在这方面,SQL Server的灵活性稍差一些。

图片 4

  SQL Server对表的分配extent的方式与Oracle不同。为了使空间分配有效,SQL Server 不会将所有区分配给包含少量数据的表,所以SQL Server不会对空表分配extent,extend的分配会延迟到对表添加记录时。
  SQL Server 有两种类型的区:

  • 混合区(mixed extent):混合区由多个表或索引共用,最多可由八个对象共享。 区中八页的每页可由不同的对象所有。
  • 统一区(uniform extent):统一区由由单个对象所有。区中的所有 8 页只能由一个表或索引专用。

通常对表或索引分配的前8个数据页会在混合区内分配,以后的数据页则在统一区内分配,这种方式与Oracle不同,Oracle的一个区只能分配给一个表或索引,不能多个对象共用,或者也可以说,Oracle只有SQL Server中的统一区一种类型。

2、文件、文件组在实践应用中常见的问题

  使用配置管理器可以启动、停止、重新启动、继续或暂停服务。

三,指定文件组

在创建表时,在on子句中指定文件组,那么数据将存储在该文件组包含的文件中:

CREATE TABLE [dbo].[student](
...
) ON [PRIMARY]

在创建索引时,在on子句指定文件组选项,那么该表的索引结构将存储在文件组包含的文件中:

CREATE NONCLUSTERED INDEX [idx_student_stuname] 
ON [dbo].[student]( [stuname] ASC ) 
ON [PRIMARY]

在创建分区时,在TO自居中指定文件组,每一个分区将存储到文件组中:

REATE PARTITION SCHEME scheme_name
AS PARTITION function_name 
TO ([fg_name1], <....>, [fp_nameN])

页(data page)

SQL Server 中数据存储的基本单位是页。 为数据库中的数据文件(.mdf 或 .ndf)分配的磁盘空间可以从逻辑上划分成页(从 0 到 n 连续编号)。 磁盘 I/O 操作在页级执行。 也就是说,页也是也是读写数据的单位。
  页是区段的分配单元。每一个区段包含8个页,每个页的大小固定为8KB,不能修改,这与Oracle数据库在创建表空间时可以指定数据库大小不同。

图片 5

  上图展示了数据是如何存放在页中的。对于插入的每一行,为了表明特定行的数据开始于页中的何处,每一页的末尾都用一小块空间记录的每一行相对于页头位置的偏移量。
  SQL Server 数据文件中的页按顺序编号,文件的首页以 0 开始。数据库中的每个文件都有一个唯一的文件 ID 号。若要唯一标识数据库中的页,需要同时使用文件 ID 和页码。

通常情况下我们构造的数据库都只有两个文件,mdf文件和ldf文件.但是这样有两个缺点:

  服务器和客户端网络协议

四,数据文件大小增长导致的问题

当数据文件爆满,没有空间存储数据时,此时执行insert命令,这会导致数据文件的增长。如果filegrowth选项设置的过大,会导致SQL Server耗费较长时间来实现文件的增长,在数据文件增长时,该文件是不能访问的,因此,即使用户仅插入一条数据,也要等待很长时间才能完成查询,对用户来说,体验不友好。

数据文件增长是非常耗费系统资源和影响性能,如果设置SQL Server 自动增长,可能会导致系统性能不够稳定,所以,应该预测可能的空间使用需求,并提前做好规划。尽量避免空间用尽而使得SQL Server不得不自动增长的现象发生。同时也要确保每一次自动增长都能够在可接受的时间内完成,及时满足客户端应用的需求。

管理SQL Server文件组及文件组

ALTER DATABASE database_name   
{  
    <add_or_modify_files>  
  | <add_or_modify_filegroups>  
}  
[;]  

<add_or_modify_files>::=  
{  
    ADD FILE <filespec> [ ,...n ]   
        [ TO FILEGROUP { filegroup_name } ]  
  | ADD LOG FILE <filespec> [ ,...n ]   
  | REMOVE FILE logical_file_name   
  | MODIFY FILE <filespec>  
}  

<filespec>::=   
(  
  NAME = logical_file_name
  [ , NEWNAME = new_logical_name ]
  [ , FILENAME ={'os_file_name'|'filestream_path'|'memory_optimized_data_path'}]
  [ , SIZE = size [ KB | MB | GB | TB ] ]
  [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
  [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
  [ , OFFLINE ]
)

<add_or_modify_filegroups>::=  
{  
    | ADD FILEGROUP filegroup_name   
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]  
    | REMOVE FILEGROUP filegroup_name   
    | MODIFY FILEGROUP filegroup_name  
        { <filegroup_updatability_option>  
        | DEFAULT  
        | NAME = new_filegroup_name   
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }  
        }  
}  
<filegroup_updatability_option>::=  
{  
    { READONLY | READWRITE } | { READ_ONLY | READ_WRITE }  
}  
  • <add_or_modify_files>::=</add_or_modify_files>:指定要添加、删除或修改的文件。
  • database_name:要修改的数据库的名称。
  • ADD FILE:向数据库中添加文件。
  • TO FILEGROUP { filegroup_name }:指定要将指定文件添加到的文件组。
  • ADD LOG FILE:将要添加的日志文件添加到指定的数据库。
  • REMOVE FILE logical_file_name:从 SQL Server 的实例中删除逻辑文件说明并删除物理文件。 除非文件为空,否则无法删除文件。
  • logical_file_name:在 SQL Server 中引用文件时所用的逻辑名称。
  • MODIFY FILE:指定应修改的文件。 如果指定了 SIZE,那么新大小必须比文件当前大小要大。
    若要修改数据文件或日志文件的逻辑名称,请在 NAME 子句中指定要重命名的逻辑文件名称,并在 NEWNAME 子句中指定文件的新逻辑名称。 例如:
MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 

若要将数据文件或日志文件移至新位置,请在 NAME 子句中指定当前的逻辑文件名称,并在 FILENAME 子句中指定新路径和操作系统(物理)文件名称。 例如:

MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ')
  • { 'os_file_name' | 'filestream_path' | 'memory_optimized_data_path'}
  • os_file_name:对于标准 (ROWS) 文件组,这是在创建文件时操作系统所使用的路径和文件名。
  • ' filestream_path ':对于 FILESTREAM 文件组,FILENAME 指向将存储 FILESTREAM 数据的路径。
  • memory_optimized_data_path:对于内存优化文件组,FILENAME 会引用将存储内存优化数据的路径。SIZE、MAXSIZE 和 FILEGROWTH 属性不适用于内存优化文件组。
  • FILEGROWTH:用于指定每次文件增长大小,如果未指定确定的值,则默认为1MB,如果指定为0,则数据文件不能自动增长。可以使用MB、KB、GB、TB或百分比(%)为单位,默认值为MB。如果指定%,则增量大小为发生增长时文件大小的指定百分比。指定的大小舍入为最接近64KB的倍数。
  • OFFLINE:将文件设置为脱机并使文件组中的所有对象都不可访问。
  • <add_or_modify_filegroups>::=</add_or_modify_filegroups>:在数据库中添加、修改或删除文件组。
  • CONTAINS FILESTREAM:指定文件组在文件系统中存储 FILESTREAM 二进制大型对象 (BLOB)。
  • CONTAINS MEMORY_OPTIMIZED_DATA:指定文件组在文件系统中存储内存优化数据。每个数据库只能有一个 MEMORY_OPTIMIZED_DATA 文件组。 在创建内存优化表时,文件组不能为空,其中必须至少包含一个文件。
  • REMOVE FILEGROUP filegroup_name:删除文件组filegroup_name从数据库中删除文件组。 除非文件组为空,否则无法将其删除。 首先从文件组中删除所有文件。
  • MODIFY FILEGROUP filegroup_name:修改文件组。
  • DEFAULT:更改默认的数据库文件组到filegroup_name。 数据库中只能有一个文件组作为默认文件组。
  • AUTOGROW_SINGLE_FILE:在文件组中的文件符合自动增长阈值时,仅该文件是增长。 这是默认设置。
  • AUTOGROW_ALL_FILES:如果文件组中的文件达到了自动增长阈值,文件组中的所有文件都增长。
  • <filegroup_updatability_option>:对文件组设置只读或读/写属性。
  • READ_ONLY | READONLY:指定文件组为只读。 不允许更新其中的对象。 主文件组不能设置为只读。 若要更改此状态,您必须对数据库有独占访问权限。
  • 因为只读数据库不允许数据修改,所以将发生以下情况:
    系统启动时,将跳过自动恢复。
    不能收缩数据库。
    在只读数据库中不会进行锁定。 这可以加快查询速度。

【示例】

A. 向数据库中添加由两个文件组成的文件组
  以下示例在 AdventureWorks2012 数据库中创建文件组 Test1FG1,然后将两个 5 MB 的文件添加到该文件组。

USE master  
ALTER DATABASE AdventureWorks2012  
ADD FILEGROUP Test1FG1;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = test1dat3,  
    FILENAME = 'D:Microsoft SQL ServerMSSQLDATAt1dat3.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1dat4,  
    FILENAME = 'D:Microsoft SQL ServerMSSQLDATAt1dat4.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP Test1FG1;  
GO  

B.向数据库中添加两个日志文件

USE master;  
ALTER DATABASE AdventureWorks2012   
ADD LOG FILE   
(  
    NAME = test1log2,  
    FILENAME = 'D:Microsoft SQL ServerMSSQLDATAtest2log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1log3,  
    FILENAME = 'D:Microsoft SQL ServerDATAtest3log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
);  
GO  

C.从数据库中删除文件

USE master;  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE test1dat4;  
GO  

D.修改文件
  以下示例添加的一个文件的大小。ALTER DATABASE MODIFY FILE 命令与可以使文件大小更大,因此如果你需要使文件大小更小你需要使用 DBCC SHRINKFILE。

USE master;  
ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO  

此示例中收缩数据文件的大小为 100 MB,然后指定在该数量的大小。

USE AdventureWorks2012;
DBCC SHRINKFILE (AdventureWorks2012_data, 100);
GO

USE master;  
ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO

E.将文件移至新位置
  下面以把AdventureWorks数据中的数据文件E:t1dat2.ndf移动到C:t1dat2.ndf为例,说明移动数据文件的过程。
首先把数据库脱机:

alter database AdventureWorks set offline

在操作系统中把E:t1dat2.ndf移动到C:t1dat2.ndf:

!! move E:t1dat2.ndf C:t1dat2.ndf

修改数据库中对此文件路径的记载:

ALTER DATABASE AdventureWorks 
MODIFY FILE  
(  
    NAME = Test1dat2,  
    FILENAME = N'C:t1dat2.ndf'  
);  
GO  

最后再把数据库重新联机:

alter database AdventureWorks set online

然后查询t1dat2的物理文件路径:

select name,physical_name from sys.database_files where name ='C:t1dat2.ndf'

F.使文件组成为默认文件组
  下面的示例使Test1FG1成为默认文件组。 然后,默认文件组被重置为 PRIMARY 文件组。 请注意,必须使用括号或引号分隔 PRIMARY。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP Test1FG1 DEFAULT;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP [PRIMARY] DEFAULT;  
GO  

(一)容易导致文件过大

 

五,仅使用MDF和LDF文件还原数据库

在测试服务器上,有一个DB无法访问,原因是服务器上安装的是SQL Server的评估版本,在试用期过期之后,SQL Server实例无法打开,导致DB无法访问。该数据,只有MDF和LDF文件,没有做数据库的还原和分离操作,要还原数据库必须使用特殊的方法:重建一个名字相同DB,然后替换数据库的文件。

  • Step1,新建一个同名的数据库,该DB和过期的DB有相同文件;
  • Step2,将新DB脱机,删除其MDF和LDF文件;
  • Step3,将过期DB的MDF和LDF文件复制到相应的路径下;
  • Step4,将新DB联机,查看DB的数据,及时备份和还原。

 

 

推荐文档:

文件自动增长和自动收缩sql server

查询指定表被分配的extent信息

在SQL Server可以使用dbcc extentinfo命令查询表被分配的extent信息。

dbcc extentinfo(数据库名,表名)

我们知道,mdf文件是数据库文件,这样的话也就意味着随着数据库的增大mdf就会相应的增大,显然在现在的应用中数据膨胀是太常见的事情了,当你的应用变大后,mdf文件也会变大,然而windows对文件的大小是有要求的,这样的话很容易导致mdf文件达到windows所允许的文件大小的界限(于是数据库就崩溃了)。

2.SQLSMS

(二)没有利用到磁盘阵列

  简介:SQLSMS是一个集成环境,用于访问、配置、管理和开发SQL Server的所有组件。

大型的服务器好多都有磁盘阵列,你可以把磁盘阵列简单的假象成n个一块转动的磁盘,磁盘阵列的设计是希望通过多个磁盘的串联来得到更大的读写效率.但是如果你的数据库只有一个mdf文件(ldf文件暂时不考虑),那么你总是只能够利用这个磁盘阵列里面的一个磁盘而已.那样的话昂贵的磁盘阵列的效率就由并联变成串联了.试想如果我们能够让mdf分散成多个文件,比如说磁盘阵列上的每个磁盘中都分配一个文件,然后把mdf中的数据分散到各个文件中,我在读取的时候就是串联的读取了,这样就充分的利用了磁盘阵的存取效能.

  注册服务器:为客户机确定一台SQL Server数据库所在的机器,及服务器。

这两个问题平常我们没有遇到过(条件不具备),但是做大型的服务开发的时候这几乎是致命的.

 

3、查询文件组和文件语句

4.Sqlcmd工具

这在2005下,运行

  sqlcmd通过OLE DB与服务器进行通信,使用sqlcmd工具可以在命令提示符窗口中输入T-SQL语句,调用系统过程和脚本文件。

SELECT df.[name],
df.physical_name,
df.[size],
df.growth,
f.[name][filegroup],
f.is_default
FROM sys.database_files df JOIN sys.filegroups f
ON df.data_space_id = f.data_space_id

  T-SQL脚本文件是一个文本文件,可以包含T-SQL语句、sqlcmd命令以及脚本变量的组合。

4、MSDN官方解释

 

了解文件和文件组

5.其它管理工具

每个 SQL Server 数据库至少具有两个操作系统文件:一个数据文件和一个日志文件。数据文件包含数据和对象,例如表、索引、存储过程和视图。日志文件包含恢复数据库中的所有事务所需的信息。为了便于分配和管理,可以将数据文件集合起来,放到文件组中。

  business intelligence development studio

à数据库文件
SQL Server 数据库具有三种类型的文件,如下所示

  数据库引擎优化顾问

主要數據文件

  Sql server profiler

主要数据文件包含数据库的启动信息,并指向数据库中的其他文件。用户数据和对象可存储在此文件中,也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf.
次要数据文件

  Reporting services

是可选的,由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上,次要文件可用于将数据分散到多个磁盘上。另外,如果数据库超过了单个 Windows 文件的最大大小,可以使用次要数据文件,这样数据库就能继续增长。次要数据文件的建议文件扩展名是 .ndf。
事务日志文件
事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。事务日志的建议文件扩展名是 .ldf.

 

à文件组
每个数据库有一个主要文件组。此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组,用于将数据文件集合起来,以便于管理、数据分配和放置。

二、系统数据库

例如,可以分别在三个磁盘驱动器上创建三个文件 Data1.ndf、Data2.ndf 和 Data3.ndf,然后将它们分配给文件组 fgroup1。然后,可以明确地在文件组 fgroup1 上创建一个表。对表中数据的查询将分散到三个磁盘上,从而提高了性能。通过使用在 RAID(独立磁盘冗余阵列)条带集上创建的单个文件也能获得同样的性能提高。但是,文件和文件组使您能够轻松地在新磁盘上添加新文件。

 

以下列出了存储在文件组中的所有数据文件。

1. master数据库

主文件组
主要包含主要文件的文件组。所有系统表都被分配到主要文件组中。
用户定义文件組

 

用户首次创建数据库或以后修改数据库时明确创建的任何文件组

master数据库是SQL Server 2008中最重要的数据库。记录了SQL Server实例的所有系统级信息。如: SQL Server初始化信息、登录帐户和系统配置设置、所有数据库文件的位置。

默认文件组
如果在数据库中创建对象时没有指定对象所属的文件组,对象将被分配给默认文件组。不管何时,只能将一个文件组指定为默认文件组。默认文件组中的文件必须足够大,能够容纳未分配给其他文件组的所有新对象。

如果master数据库不可用,则SQL Server无法启动。master数据库始终有一个可用的最新的master数据库备份。

PRIMARY 文件组是默认文件组,除非使用 ALTER DATABASE 语句进行了更改。但系统对象和表仍然分配给 PRIMARY 文件组,而不是新的默认文件组。

 

5、操作實例

2. tempdb数据库

--(5.1)创建数据库
--切换到 master 数据库
USE master
GO
create database db_Study
on primary --主文件组和主要数据文件
(name='db_study_Primary',
filename='D:studydb_study_pri.mdf'),
filegroup db_Study_filegroup1 --用户定义文件组1
(name='db_study_fg_data1',
filename='D:studydb_study_fg_data1_1.ndf'),--次要数据文件1
(name='db_study_fg_data2' ,
filename='D:studydb_study_fg_data2_2.ndf'),--次要数据文件2
filegroup db_Study_filegroup2
(name='db_study_fg_data3',
filename='D:studydb_study_fg_data3_1.ndf')
log on
(name='db_study_log',
filename='D:studydb_study.ldf')
go
--5.2查询文件组和文件
SELECT df.[name],
df.physical_name,
df.[size],
df.growth,
f.[name][filegroup],
f.is_default
FROM sys.database_files df JOIN sys.filegroups f
ON df.data_space_id = f.data_space_id
/*
name physical_name size growth filegroup is_default
db_study_Primary D:studydb_study_pri.mdf 280 128 PRIMARY 1
db_study_fg_data1 D:studydb_study_fg_data1_1.ndf 128 128 db_Study_filegroup1 0
db_study_fg_data2 D:studydb_study_fg_data2_2.ndf 128 128 db_Study_filegroup1 0
db_study_fg_data3 D:studydb_study_fg_data3_1.ndf 128 128 db_Study_filegroup2 0
*/

 

--5.3修改默认数据文件组
alter database db_study
modify filegroup db_study_filegroup1 default
--5.4
--在默认文件组db_study_filegroup1创建表,
--并且指定图像数据保存在用户定义文件组db_study_filegroup1
create table my_test
(
ID int primary key,
[Name] varchar(10),
PIC image
)textimage_on db_study_filegroup2
--在用户定义文件组db_study_filegroup2上创建索引
create index ix_my_test on my_test(ID) on db_study_filegroup2
GO
--5.5将要删除数据文件db_study_fg_data1的数据转移到其他数据文件中,
--并且清空数据文件db_study_fg_data1
DBCC SHRINKFILE(db_study_fg_data1,EMPTYFILE)
go
--删除数据文件db_study_fg_data1
ALTER DATABASE DB_study
REMOVE FILE db_study_fg_data1
go

tempdb数据库是一个临时数据库,用于保存临时对象或中间结果集,满足临时存储要求。

作者:wufeng4552 博客:

tempdb数据库在SQL Server每次启动时都重新创建。临时表和临时存储过程在连接断开时自动除去,而且当系统关闭后将没有任何连接处于活动状态。

每次启动数据库引擎时, tempdb数据库会重置为其初始大小,在SQL Server运行时,该库会根据需要自动增长。

 

3. model数据库

 

model数据库是创建所有数据库的模板。当创建数据库时,新数据库的第一部分通过复制model数据库中的内容创建,剩余部分由空白页填充。

如果修改model数据库,之后创建的所有数据库都将继承这些修改,即model数据库中的所有用户定义的对象都将复制到所有新创建的数据库中。可以向model数据库中添加任何对象如:表、视图、存储过程等),以便将这些对象包含到所有新创建的数据库中。

model数据库一直存储在SQL Server系统中。

 

4. msdb数据库

 

msdb数据库供   SQL   Server   代理程序调度警报和作业以及记录操作员时使用。 

 

三、数据库的组成

 

1. 数据库对象

  表

  视图

  索引

  存储过程和触发器

  用户和角色

 

2. 数据库文件

 SQL Server 2005数据库有3种类型的文件:

 主数据文件

 次要数据文件

 日志文件

 

①主数据文件

主数据文件用来存放数据,它还包含其它数据库文件的指针,它是数据库的起点。   它包含一些系统表,这些表记录数据库对象及其他文件的位置信息。

一个数据库必须有且只有一个主数据文件,主数据文件的扩展名为.MDF。

在SQL Server 2008中,数据库所有文件的位置都记录在数据库的主文件中和master数据库中。

 

②次要数据文件 

次要数据文件也用来存放数据。如果主数据文件可包括数据库中的所有数据就不需要次数据文件,如果主数据文件太大或要扩展到多个磁盘,则需次要数据文件。

一个数据库可有多个或没有次要数据文件,次要数据文件的扩展名为.NDF

 

③事务日志文件 

事务是用户定义的一个数据库操作序列。这些操作要么全做要么全不做。

事务日志文件用来存放事务日志。即存储所有事务和由这些事务引起的数据库的变化,用于恢复数据库。

一个数据库有一个或多个事务日志文件,日志文件的扩展名为.LDF

 

3. 文件组

文件组用于文件的分配和管理,有两种类型:

 

主文件组:

主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。

用户定义文件组 :

用户定义文件组是通过在CREATE DATABASE或ALTER DATABASE语句中使用FILEGROUP关键字指定的任何文件组。 

 

数据库文件应遵循的原则:

 

一个数据库均有一个文件组被指定为默认文件组。未指定则主文件组为默认文件组。

如果创建表或索引时未指定文件组,则将假定所有页都从默认文件组分配。一次只能有一个文组为默认文件组。

一个数据文件只能属于一个文件组。

日志文件不能属于文件组。

 

 

四、创建和删除数据库

 

1. 创建数据库:

 使用SQLSMS工具创建

 使用T-SQL创建

 

2. 删除数据库:

 使用SQLSMS工具删除

 使用T-SQL删除

 

五、分离和附加数据库

 

1.分离数据库

 将数据库从实例中删除,但是其数据库文件和事务日志文件保持不变。

 

2. 附加数据库

将分离后的数据库添加到SQL SERVER实例中。

 

六、SQL脚本

使用SQL脚本可以创建数据库结构、重建数据库,或将它作为移动数据库的工具。

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:基础知识,2005中的文件和文件组

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。