Sql Server来龙去脉系列之四 数据库和文件
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Sql Server来龙去脉系列之四 数据库和文件,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含14838字,纯文字阅读大概需要22分钟。
内容图文
type
|
File type:
0 = Rows (includes full-text catalogs upgraded to or created in
SQL Server 2008)
1 = Log
2 = FILESTREAM
3 = Reserved for future use
4 = Full-text (includes full-text catalogs from versions earlier than
SQL Server 2008)
|
name
|
The logical name of the fi le
|
physical_name
|
Operating-system fi le name
|
size
|
Current size of the fi le, in 8-KB pages.
0 = Not applicable
For a database snapshot, size refl ects the maximum space that the snapshot
can ever use for the fi le.
|
max_size
|
Maximum fi le size, in 8-KB pages:
0 = No growth is allowed.
–1 = File will grow until the disk is full.
268435456 = Log fi le will grow to a maximum size of 2 terabytes.
|
growth
|
0 = File is a fi xed size and will not grow.
>0 = File will grow automatically.
If is_percent_growth = 0, growth increment is in units of 8-KB pages,
rounded to the nearest 64 KB.
If is_percent_growth = 1, growth increment is expressed as a whole number
percentage.
|
CREATE DATABASE Archive
ON
PRIMARY
( NAME = Arch1,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\archdat1.mdf‘,
SIZE = 100MB,
MAXSIZE = 200MB,
FILEGROWTH = 20MB),
( NAME = Arch2,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\archdat2.ndf‘,
SIZE = 10GB,
MAXSIZE = 50GB,
FILEGROWTH = 250MB)
LOG ON
( NAME = Archlog1,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\archlog1.ldf‘,
SIZE = 2GB,
MAXSIZE = 10GB,
FILEGROWTH = 100MB);
|
CREATE DATABASE Sales
ON PRIMARY
( NAME = salesPrimary1,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\salesPrimary1.mdf‘,
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 100 ),
( NAME = salesPrimary2,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\salesPrimary2.ndf‘,
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 100 ),
FILEGROUP SalesGroup1
( NAME = salesGrp1Fi1e1,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\salesGrp1Fi1e1.ndf‘,
SIZE = 500,
MAXSIZE = 3000,
FILEGROWTH = 500 ),
( NAME = salesGrp1Fi1e2,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\salesGrp1Fi1e2.ndf‘,
SIZE = 500,
MAXSIZE = 3000,
FILEGROWTH = 500 ),
FILEGROUP SalesGroup2
( NAME = salesGrp2Fi1e1,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\salesGrp2Fi1e1.ndf‘,
SIZE = 100,
MAXSIZE = 5000,
FILEGROWTH = 500 ),
( NAME = salesGrp2Fi1e2,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\salesGrp2Fi1e2.ndf‘,
SIZE = 100,
MAXSIZE = 5000,
FILEGROWTH = 500 )
LOG ON
( NAME = ‘Sales_log‘,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\saleslog.ldf‘,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
|
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE Test1
ADD FILE
( NAME = ‘test1dat4‘,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\t1dat4.ndf‘,
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB),
( NAME = ‘test1dat5‘,
FILENAME =
‘c:\program files\microsoft sql server\mssql.1\mssql\data\t1dat5.ndf‘,
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB)
TO FILEGROUP Test1FG1;
GO
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
|
a. SINGLE_USER | RESTRICTED_USER | MULTI_USER |
用来描述用户访问数据库的属性。
例如:
ALTER DATABASE Sample SET SINGLE_USER; SINGLE_USER表示某一时刻之允许有一个链接;
RESTRICTED_USER表示用户仅仅拥有dbcreator、sysadmin server角色或者是数据库db_owner,才可以访问数据库;
MULTI_USER表示任意用户连接。你可以通过SELECT USER_ACCESS_DESC FROM sys.databases WHERE name = ‘<name of database>‘查看数据库用户访问属性。
|
b. OFFLINE | ONLINE | EMERGENCY |
数据库设置成OFFLINE后该数据库就不能被修改;如果当前有任何用户链接,数据库也不能设置成OFFLINE。
修改语句如下:
ALTER DATABASE Sample SET OFFLINE;
SELECT state_desc from sys.databases WHERE name = ‘Sample‘;
|
c. READ_ONLY | READ_WRITE |
该组属性设置数据库读写模式,默认是READ_WRITE可读可写,READ_ONLY表示只读,没有INSERT, UPDATE, or DELETE操作。
例如:
ALTER DATABASE Sample SET READ_ONLY;
SELECT name, is_read_only FROM sys.databases WHERE name = ‘Sample ‘;
|
a. ANSI_NULLS | 如果设置为ON,任何和NULL比较返回UNKNOW;如果设置为OFF,如果两个都是NULL比较结果为TRUE |
■ database_id (even though the DBID 2 is the only one used)
■ file_id
■ unallocated_extent_page_count
■ version_store_reserved_page_count
■ user_object_reserved_page_count
■ internal_object_reserved_page_count
■ mixed_extent_page_count
|
Sql Server来龙去脉系列目录结构:
Sql Server来龙去脉系列之五 日志以及恢复
Sql Server来龙去脉系列之六 表
Sql Server来龙去脉系列之七 索引
Sql Server来龙去脉系列之八 比较特殊的存储
Sql Server来龙去脉系列之九 查询优化
Sql Server来龙去脉系列之十 计划缓存
Sql Server来龙去脉系列之十一 事务和并发
原文:http://www.cnblogs.com/w-wanglei/p/ssql-series4.html
内容总结
以上是互联网集市为您收集整理的Sql Server来龙去脉系列之四 数据库和文件全部内容,希望文章能够帮你解决Sql Server来龙去脉系列之四 数据库和文件所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。