on PRIMARY
(
NAME = test1,
FILENAME='F:\test\test1.mdf', --此路径必须存在才能建成功
SIZE = 10,
MAXSIZE = UNLIMITED, --不限制增长
FILEGROWTH = 5
)
LOG ON
(
NAME='test1_dat',
FILENAME='F:\test\test1.ldf', --此路径必须存在才能建成功
SIZE =5MB,
MAXSIZE = 25MB,
FILEGROWTH =5MB
)
GO
![](https://i-blog.csdnimg.cn/blog_migrate/66517ddc3196e4fe67947424b49d58df.png)
![](https://i-blog.csdnimg.cn/blog_migrate/687aaad9da9582338d79c920bf3c6721.png)
![](https://i-blog.csdnimg.cn/blog_migrate/291e188f5d2ebeb913058d3525548a1c.png)
昨晚遇到的这个问题,也知道Notifications service依赖底层的Service broker的。本以为只需要执行以下脚本对数据库启用Service broker即可。
alter database DBNAME set enable_broker
但是,执行后,脚本一直处于执行状态,不以为然,正好在忙其它事情就没有查看运行结果,结果到今早一看,居然运行还没有结束。虽然是在一个生产数据库上执行的,数据库也只有30G的样子,但也不至于执行一个晚上也未结束,只好终止执行,使用
SELECT is_broker_enabled FROM sys.databases WHERE name = 'DBNAME'
查看is_broker-enabled为0,依然未启用Service broker
后google下,在一个国外论坛里面找到了解决办法:
ALTER DATABASE DatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Databasename SET ENABLE_BROKER;
执行以上2条语句,未做任何等待,就提示命令执行完成。
SELECT is_broker_enabled FROM sys.databases WHERE name = 'DBNAME'
查看is_broker-enabled为1
NEW_BROKER选项,SQL SERVER联机丛书上的解释:
每个数据库都包含一个 Service Broker 标识符。sys.databases 目录视图的 service_broker_guid 列显示该实例中每个数据库的 Service Broker 标识符。Service Broker 路由使用 Service Broker 标识符来保证一个会话的所有消息都传递到同一个数据库。因此,Service Broker 标识符在同一网络上的所有实例中应是唯一的。否则,消息可能被误传。
SQL Server 为每个新数据库生成新的 Service Broker 标识符。由于标识符是新的,因此 SQL Server 可以安全地激活新数据库中的 Service Broker 消息传递。网络上的其他数据库应该不会有与之相同的 Service Broker 标识符。
NEW_BROKER。此选项用于激活 Service Broker 消息传递,同时为数据库创建新的 Service Broker 标识符。由于该数据库中所有的现有会话都未使用新的实例标识符,因此,此选项将结束这些会话并返回一个错误。
ROLLBACK IMMEDIATE将立即回滚未完成的事务。
有四个选项用于管理标识符和消息传递:
-
ENABLE_BROKER。此选项用于激活 Service Broker 消息传递,并且保留数据库的现有 Service Broker 标识符。
注意
在任何数据库中启用 SQL Server Service Broker 都需要数据库锁。若要在 msdb 数据库中启用 Service Broker,请首先停止 SQL Server 代理。然后,Service Broker 才可获得必要的锁。
-
DISABLE_BROKER。此选项用于停用 Service Broker 消息传递,并且保留数据库的现有 Service Broker 标识符。
-
NEW_BROKER。 此选项用于激活 Service Broker 消息传递,并为数据库创建一个新的 Service Broker 标识符。选择此选项将结束数据库中的所有现有会话,并对每个会话返回一个错误。这是因为这些会话不使用新的标识符。必须使用新标识符重新创建任何引用旧 Service Broker 标识符的路由。
-
ERROR_BROKER_CONVERSATIONS。此选项用于激活 Service Broker 消息传递,并且保留数据库的现有 Service Broker 标识符。Service Broker 将结束数据库中的所有会话,并对每个会话返回一个错误。通常,如果某个数据库与其他数据库之间具有打开的会话,并且您要将该数据库还原到一个不同于与之对 话的数据库的时间点,则需要使用此选项。还原的数据库中的所有会话必须以错误结束,因为这些会话现在与其他数据库已不同步。Service Broker 标识符保留,以便引用该标识符的所有路由仍有效。
无论选择指定选项中的哪一个,SQL Server 都不允许具有相同 Service Broker 标识符的两个数据库在 SQL Server 的同一实例中都激活消息传递。如果附加一个数据库,该数据库的 Service Broker 标识符与现有数据库的标识符相同,SQL Server 将停用所附加数据库中的 Service Broker 消息传递。
2)手写脚本创建的数据库未指定PRIMARY
-
PRIMARY
-
指定关联的 <filespec> 列表定义主文件。 在主文件组的 <filespec> 项中指定的第一个文件将成为主文件。 一个数据库只能有一个主文件。 有关详细信息,请参阅数据库文件和文件组。
如果没有指定 PRIMARY,那么 CREATE DATABASE 语句中列出的第一个文件将成为主文件。
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [tttt1] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
3)手动创建的生成脚本里面有TRUSTWORTHY OFF 但是自动的没有这个选项
默认情况下,此设置为“OFF”
4)手动创建的生成脚本里面有SET ALLOW_SNAPSHOT_ISOLATION OFF 但是自动的没有这个选项
默认情况下,此设置为“OFF”
SnapshotIsolationState 属性可为以下值之一:
-
On 为数据库启用快照隔离。
-
Off 为数据库禁用快照隔离。
-
in_transition_to_on 一旦当前事务完成,就将启用快照隔离。
-
in_transition_to_off 一旦当前事务完成,就将禁用快照隔离。
SET OPTION PUBLIC.allow_snapshot_isolation = 'On';
结论
- 使用 sys.databases 目录视图可以确定两个行版本控制数据库选项的状态。
- 对用户表和存储在 master 和 msdb 中的某些系统表的任何更新都会生成行版本。
- 在 master 和 msdb 数据库中,ALLOW_SNAPSHOT_ISOLATION 选项自动设置为 ON,并且不能禁用。
- 在 master 数据库、tempdb 数据库或 msdb 数据库中,用户不能将 READ_COMMITTED_SNAPSHOT 选项设置为 ON。
- 从上面的测试可以看到,原先会发生阻塞的两个会话在使用行版本控制的隔离级别后,都不会遇到阻塞了。但是两种行版本控制的结果又有不同
SELECT name,snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on
FROM sys.databases
转自http://www.cnblogs.com/xwdreamer/archive/2012/07/30/2615357.html
简单地说, 在这种隔离级别下, 读取的数据如果在更新中, 那么读取到的是更新前的快照(条版本), 修改前的COMMIT数据, 所以这个不产生脏读
NOLOCK提示读取的是更新中的数据(没有 COMMIT), 是脏数据
而为了给数据读取提供可用的行版本, 对于数据更新而言, 它在更新数据前, 就需要为要更新的数据生成行版本(快照), 这是一个额外的开销, 在单个事务中更新的数据量越大, 这个开销越大
SQL有两种行版本控制:
--(1)行版本控制的已提交读隔离(read_committed_snapshot) --(2)直接使用snapshot事务隔离级别 --(1)(read_committed_snapshot):read_committed_snapshot数据库选项为ON时,read_committed事务通过使用行 --版本控制提供语句级读取一致性 --(2)(snapshot事务隔离级别)allow_snapshot_isolation数据库选项为ON时,snapshot事务通过使用行版本 --控制提供事务级读取一致性 --下列示例可以说明使用普通已提交读事务,行版本控制的快照隔离事务和行版本 --控制的已提交读事务的行为差异 --示例: --A 普通已提交事务 --在此示例中,一个普通read committed事务将读取数据,然后由另一事务修改此数据。执行 --完的读操作不阻塞由其他事务执行的更新操作。但是,在其他事务已经做了更新操作后,读 --操作会被阻塞住,直到更新操作事务提交为止 --在会话1上: USE [AdventureWorks] GO BEGIN TRAN --查询1 --这个查询将返回员工有48小时休假时间 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 ----------------------------------------------------------------------------------------------- --在会话2上: USE [AdventureWorks] GO BEGIN TRAN --修改1 --休假时间减去8 --修改不会被阻塞,因为会话1不会持有S锁不放 UPDATE [HumanResources].[Employee] SET [VacationHours]=[VacationHours]-8 WHERE [EmployeeID]=4 --查询1 --现在休假时间只有40小时 SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 -------------------------------------------------------------------------------------------------- --在会话1上: --重新运行查询语句,会被会话2阻塞 --查询2 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 ----------------------------------------------------------------------------------------------- --在会话2上: --提交事务 COMMIT TRAN GO ----------------------------------------------------------------------------------------------- --在会话1上: --此时先前被阻塞的查询结束,返回会话2修改好的新数据:40 --查询3 --这里返回40,因为会话2已经提交了事务 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 --修改2 --这里会成功 UPDATE [HumanResources].[Employee] SET [SickLeaveHours]=[SickLeaveHours]-8 WHERE [EmployeeID]=4 SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 --可以回滚会话1的修改 --会话2的修改不会受影响 ROLLBACK TRAN GO SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 ------------------------------------------------------------------------------------------- --B 使用快照隔离 --此示例中,在快照隔离下运行的事务将读取数据,然后由另一个事务修改此数据。快照事务 --不阻塞由其他事务执行的更新操作,他忽略数据的修改继续从版本化的行读取数据。也就是 --说,读取到的是数据修改前的版本。但是,当快照事务尝试修改已由其他事务修改的数据时, --他将生成错误并终止 --在会话1上: USE [AdventureWorks] GO --启用快照隔离 ALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION ON GO --设置使用快照隔离级别 SET TRANSACTION ISOLATION LEVEL SNAPSHOT GO BEGIN TRAN --查询1 --查询返回员工有48小时假期 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 --------------------------------------------------------------------------------------------- --在会话2上: USE [AdventureWorks] GO BEGIN TRAN --修改1 --假期时间减8 --修改不会被会话1阻塞 UPDATE [HumanResources].[Employee] SET [VacationHours]=[VacationHours]-8 WHERE [EmployeeID]=4 --查询1 --确认值已经被改成40 SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 ----------------------------------------------------------------------------------------------- --在会话1上: --查询2 --再次运行查询语句 --还是返回48(修改前的值),因为会话1是从版本化的行读取数据 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 ------------------------------------------------------------------------------------------------ --在会话2上: --提交事务 COMMIT TRAN GO ------------------------------------------------------------------------------------------------- --在会话1上: --查询3 --再次运行查询语句 --还是返回48(修改前的值),因为会话1还是从版本化的行读取数据 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 --修改2 --因为数据已经被会话2修改过,会话1想做任何修改时 --会遇到3960错误 --事务会自动回滚 UPDATE [HumanResources].[Employee] SET [SickLeaveHours]=[SickLeaveHours]-8 WHERE [EmployeeID]=4 --会话1的修改会回滚 --会话2的修改不会回滚 ROLLBACK TRAN GO ---------------------------------------------------------------------------------------------- --C 使用行版本控制的已提交读 --在此示例中,使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为 --有所不同。与快照事务相同的是,即使其他事务修改了数据,已提交读事务也将读取版本化的行。 --与快照事务不同的是,已提交读将执行下列操作: --(1)在其他事务提交数据更改之后,读取修改的数据 --(2)能够更新由其他事务修改的数据,而快照事务不能 --在会话1上: USE [AdventureWorks] GO --启用行版本控制的已提交读 --注意运行这句话的时候,不可以有其他连接同时使用[AdventureWorks] ALTER DATABASE [AdventureWorks] SET READ_COMMITTED_SNAPSHOT ON GO --设置使用已提交读隔离级别 SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO BEGIN TRAN --查询1 --这里将返回初始值48 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 ---------------------------------------------------------------------------------------------------- --在会话2上: USE [AdventureWorks] GO BEGIN TRAN --修改1 --假期时间减8 --修改不会被会话1阻塞 UPDATE [HumanResources].[Employee] SET [VacationHours]=[VacationHours]-8 WHERE [EmployeeID]=4 --查询1 --确认值已经被修改为40 SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 ------------------------------------------------------------------------------------------------ --在会话1上: --查询2 --再次运行查询语句 --还是返回48(修改前的值),因为会话2还没有提交 --会话1是从版本化的行读取数据 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 ------------------------------------------------------------------------------------------------- --在会话2上: --提交事务 COMMIT TRAN GO -------------------------------------------------------------------------------------------------- --在会话1上: --查询3 --这里和范例B不同,会话1始终返回已提交的值 --这里返回40,因为会话2已经提交了事务 SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4 --修改2 --这里会成功 UPDATE [HumanResources].[Employee] SET [SickLeaveHours]=[SickLeaveHours]-8 WHERE [EmployeeID]=4 --可以回滚会话1的修改 --会话2的修改不会受影响 ROLLBACK TRAN GO ------------------------------------------结论----------------------------------------------------------- --从上面的测试结果可以看到,原先会发生阻塞的两个会话在使用行版本控制的隔离级别后,都不会遇到阻塞了。 --但是两种行版本控制的结果又有不同 --但是行版本控制并不是消除阻塞和死锁的万灵药。在决定使用之前,必须考虑下面两个问题 --(1)最终用户是否接受行版本控制下的运行结果? 行版本控制:数据库级别 --上面的3个测试返回的结果都各有不同。在不同的事务阶段,有的被阻塞住,有的读到的是旧版本值, --有的读到新版本值。用户期望的行为是什么?他是希望哪怕被阻塞住也要读到最新版本数据,还是 --能容忍读到旧版本数据呢?某些应用程序依赖于读隔离的锁定和阻塞行为,例如生成一个串行的流水号 --之类的操作。改成行版本控制,原先的处理逻辑就不能正常工作了。所以在采用新的隔离级别之前, --一定要做好测试,确保应用按预期的逻辑运行 --(2)SQL是否能支持行版本控制带来的额外负荷? --开启了行版本控制之后,SQL会把行版本存放在tempdb里。修改的数据越多,需要存储的信息越多 --对SQL额外的负载就越大。所以如果一个应用要从其他隔离级别转向使用行版本控制,需要做特别 --的测试,以确保现有的软硬件配置能支持额外的负荷,应用程序能够达到相似的响应速度
如何启用跨数据库的所有权链
使用 cross db ownership chaining 选项可以为 Microsoft SQL Server 实例配置跨数据库所有权链接。
此服务器选项使您能够在数据库级别控制跨数据库所有权链接,或者允许在所有数据库中启用跨数据库所有权链接:
- 如果实例的 cross db ownership chaining 关闭(设置为 0),将禁用所有数据库的跨数据库所有权链接。
- 如果实例的 cross db ownership chaining 打开(设置为 1),将启用所有数据库的跨数据库所有权链接。
- 可以使用 ALTER DATABASE 语句的 SET 子句为各个数据库设置跨数据库所有权链接。如果正在创建新的数据库,则可以使用 CREATE DATABASE 语句设置新数据库的跨数据库所有权链接选项。
建议不要将 cross db ownership chaining 设置为 1,除非 SQL Server 实例所驻留的所有数据库都必须参与跨数据库所有权链接,并且您了解此设置隐含的安全问题。有关详细信息,请参阅所有权链。
在打开或关闭跨数据库所有权链接之前,请注意下列事项:
- 只有 sysadmin 固定服务器角色成员能够打开或关闭跨数据库所有权链接。
- 关闭生产服务器的跨数据库所有权链接之前,应全面测试所有应用程序(包括第三方应用程序)以确保更改不会影响应用程序功能。
- 如果使用 sp_configure 指定 RECONFIGURE,则在服务器运行时可以更改 cross db ownership chaining 选项。
- 如果有数据库需要跨数据库所有权链接,建议使用 sp_configure 为实例关闭 cross db ownership chaining 选项;然后使用 ALTER DATABASE 语句打开需要此功能的各个数据库的跨数据库所有权链接。