sqlserver内存管理

sqlserver对系统内存的管理原则是：按需分配，且贪婪(用完不还)。

​ 它不会自动释放内存，因此执行结果集大的sql语句时，数据取出后，会一直占用内存，直到占满机器内存(并不会撑满，还是有个最大限制，比机器内存稍小)，在重启服务前，sqlserver不会释放该内存，也没有任何办法可人为释放。

​ 以下命令虽然可释放缓存，但sqlserver并不会因此释放已占用的内存。

DBCC FREEPROCCACHE     --清除存储过程相关的缓存
DBCC FREESESSIONCACHE     --清除会话缓存
DBCC FREESYSTEMCACHE('All')     --清除系统缓存
DBCC DROPCLEANBUFFERS     --清除所有缓存

修改最大可占用物理内存进行限制。

修改办法如下：

​ 管理员用户登录数据库客户端（Microsoft SQL Server Management Studio），在实例名（树形菜单根节点，显示登录名那栏）上右键，选择属性-->内存，设置“最大服务器内存”，单位为MB，可根据实际内存调整。

如此调整后，机器内存便不会被sqlserver耗尽了。

SQLServer数据库备份的使用

推荐使用方法二

方法一：使用SQL语句添加语句

1. 数据库备份

选择想要备份的数据库，”右键“→”任务“→”生成脚本“。

直接“下一步”。

选择好具体的数据库对象，然后点击“下一步”。

选择要保存的脚本选项，然后点击“下一步”。

默认选择“下一步”。

2. 数据库还原

使用刚才生成的SQL语句，点击执行。即可在数据库中创建数据表。

注意： 此处要注意的是，在新的数据库中创建一个数据库的名字，必须是跟之前的数据库名字保持一致。比如之前是PerformanceAppraisalDb，就要创建一个数据库名为PerformanceAppraisalDb的数据库。

注意： 此方法只会还原数据表，并不会对数据库进行添加数据项。

方法二：使用备份还原方式

1. 数据库备份

选择要备份的数据库，“右键”→“任务”→“备份”。

留意好备份路径，点击“确定”进行备份。

2. 数据库还原

在新数据库中，添加一个名字与之前备份的数据库名字相同的数据库。例如：PerformanceAppraisalDb

在新建的数据库中，“右键”→“任务”→“还原”→“数据库”。

选择“设备”，点击三个小点的按钮，打开资源管理器。在新弹出的窗口中，点击“添加”。

选择好备份的数据库脚本，点击确定。

此处不要着急点击"确定"。

在“选项”中，选中“覆盖现有数据库”，取消掉“日志相关的”。最后点击“确定”。

注意： 此方法的好处是可以备份到数据表内的数据项。

SSAS数据库备份与还原

1. SSAS备份

选择想要备份的数据库，”右键“→”备份“。点击浏览可以选择备份路径

选择好路径直接点击OK。

2. SSAS还原

直接右键任意数据库，”右键“→”还原“。通过浏览选择备份文件

通过还原数据库选项选择需要还原的数据库，现有一般是覆盖，新的就是新增

存储位置设置还原数据库的路径

上述设置完后 点击确定 等待还原结束

sqlserver用户权限设置

数据库

新建一个用户test，拥有浏览数据库POC_test中表数据的权限，但没有增删改的权限

1.新建用户test

安全性-->登录名，右键登录名 选择新建登录名

2.服务器角色

除非是管理员账户，一般默认public即可

bulkadmin:允许用户执行 BULK INSERT 任务。
dbcreator:允许用户创建、更改、删除数据库。
diskadmin:允许用户管理磁盘文件。
processadmin:允许用户终止 SQL Server 进程。
public:所有登录用户都是 public 角色的成员，但它没有显式权限。
securityadmin:允许用户管理登录、服务器角色和数据库角色。
serveradmin:允许用户配置服务器范围的配置选项。
setupadmin:允许用户添加和删除 SQL Server 实例。
sysadmin:具有服务器上的最高权限，可以执行任何操作。

3.用户映射

选择映射的库给test用户，角色身份按需授予

db_owner:db_owner 是数据库的所有者，拥有数据库中所有对象的完全控制权限，包括创建、删除、更改和查询。
db_accessadmin:允许成员添加或删除 Windows 用户、Windows 组或 SQL Server 登录作为数据库用户。
db_securityadmin:允许成员管理数据库角色、权限和用户。
db_ddladmin:允许成员运行 CREATE, ALTER, DROP 和其他 DDL 语句。
db_datawriter:允许成员在所有用户表中插入、更新或删除数据。
db_datareader:允许成员在所有用户表中选择数据。
db_denydatawriter:阻止成员在所有用户表中插入、更新或删除数据。
db_denydatareader:阻止成员在所有用户表中选择数据。
public:所有数据库用户都是 public 角色的成员，但它没有显式权限。

也可以通过命令行分配角色

-- 要将用户添加到 db_datareader 角色，可以使用以下 T-SQL 命令：
USE YourDatabaseName;  -- YourDatabaseName数据库名
ALTER ROLE db_datareader ADD MEMBER YourUserName;  -- YourUserName用户名

4.安全对象

分配连接SQL的权限即可

5.状态

存储过程

对于POC_test库下的存储过程，新建用户test没有查看的权限

POC_test-->可编程性-->存储过程-->选择分配权限的函数右键点击属性-->搜索用户并选择-->分配权限

也可以通过命令行分配权限

-- GRANT EXECUTE 语句将授予用户对指定函数的执行权限
USE YourDatabaseName; -- YourDatabaseName数据库名
GRANT EXECUTE ON YourFunctionName TO YourUserName; -- YourFunctionName函数名、YourUserName用户名

-- 授予 VIEW DEFINITION 权限时，用户仅能够查看对象的定义，而不具备执行该对象的权限。
-- 如果用户需要执行函数，你还需要使用 GRANT EXECUTE 授予相应的执行权限
USE YourDatabaseName;
GRANT VIEW DEFINITION ON OBJECT::YourFunctionName TO YourUserName;

sqlserver邮件功能配置

在配置之前请先把邮件的POP3之类的设置一下：

简图：

其他邮箱都是类似的，下面我们开始SQL Server数据库的演示

注：服务器对应端口号可以直接百度查，比如上述SMTP服务器，它的默认端口号是25

图形化演示

第一步：找到数据库【管理】下的【数据库邮件】，如下图：

第二步：在弹出的配置向导中我们继续

第三步：按向导提示，我们选择第一个

配置名字随意取，可以用项目名。显示名称建议用版本号+服务器ip，这样出问题可以定位跟踪

第四步：添加邮件账户，账号具体信息如下图

第五步：配置完后点击下一步

微软图形化的东西一般有个特点，一路下一步基本上能解决所有基础问题

勾选一下（貌似不勾选也没事）

这样我们就配置完成了。

测试一下

发一封邮件到“我为NET狂”的官方邮件去

去看看：

命令演示

命令演示：（不需要记，你又不是DBA，会用即可）

脚本如下：

--开启发邮件功能
exec sp_configure 'show advanced options',1
reconfigure with override
go
exec sp_configure 'database mail xps',1
reconfigure with override
go

--创建邮件帐户信息
exec msdb.dbo.sysmail_add_account_sp
 @account_name ='dunitian1',                   -- 邮件帐户名称  
 @email_address ='xxxx@yeah.net',              -- 发件人邮件地址    
 @display_name ='SQLServer2014_192.168.1.20',  -- 发件人姓名
 @MAILSERVER_NAME = 'smtp.yeah.net',           -- 邮件服务器地址
 @PORT =25,                                    -- 邮件服务器端口
 @USERNAME = 'xxxx@yeah.net',                  -- 用户名
 @PASSWORD = '邮件密码'                            -- 密码  
GO
--数据库配置文件
exec msdb.dbo.sysmail_add_profile_sp
 @profile_name = 'SQLServer_DotNetCrazy1',     -- 配置名称
 @description = '数据库邮件配置文件'            -- 配置描述
go

--用户和邮件配置文件相关联
exec msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = 'SQLServer_DotNetCrazy1',     -- 配置名称
 @account_name = 'dunitian1',                  -- 邮件帐户名称    
 @sequence_number = 1                          -- account 在 profile 中顺序（默认是1）
go

发送邮件脚本：

exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServer_DotNetCrazy1',   --配置名称
@recipients = 'dotnetcrazy@foxmail.com',    --收件名称
@body_format = 'HTML',                      --内容格式
@subject = '文章标题',
@body = '邮件内容'

结果：20的ip也发过来了

相关查询

select * from msdb.dbo.sysmail_allitems
select * from msdb.dbo.sysmail_faileditems --失败状态的消息
select * from msdb.dbo.sysmail_unsentitems --看未发送的消息
select * from msdb.dbo.sysmail_sentitems --查看已发送的消息
select * from msdb.dbo.sysmail_event_log  --记录日记

SQL Server创建链接服务器的几种方式

当我们需要从别人的数据库服务器上将数据复制到本地时，除了编写程序来实现之外，作为数据库管理员或者普通的数据库用户来说，如果不会编写外挂程序，那怎么办呢？冷静，链接服务器来帮你解决。有了链接服务器，我们不需要写任何的程序，就可以实现将一个数据库的数据复制到另一个数据库。

SQL Server链接服务器不仅可以帮助你链接SQL Server，还可以链接Oracle、Access、MySQL、ODBC数据源。

1. SQL Server链接SQL Server

我这里两台SQL的服务器分别为SQL1.abc.com和SQL2.abc.com。

在SQL1上新建链接服务器：

接下来我们左边选择“安全性”，配置远程链接的账号密码：
这里一定注意配置登录映射的时候，“本地登录”的账号必须与你本地数据库登录的账号一致，即图中1与2处的账号要相同。如果不相同，会出现后面“访问遭拒绝，不存在登录映射的问题”。一旦配置出错，建议关闭，退出重新来过，重新创建链接服务器，否则会出现各种奇奇怪怪的报错，比如，明明输入了账号sa，提示输入的账号是s，登录失败。

下面是错误的示范：

正确的示范：

注意这个选项：

这个选项是专门针对有多个数据库登录用户的情况，如果没有在上面进行登录映射定义，他们就无法建立连接。还有一个“使用此安全上下文建立连接”，这个选项可以不配置前面的登录映射，直接在这里配置远程的登录名和密码，全部使用这个账号密码建立连接。

建立链接服务器完成之后（如下图），那怎么使用呢？

[链接服务器名].数据库名.架构名.表名

select * from [SQL2.abc.com].test.dbo.test

注意：这里的链接服务器名一定要用中括号"[]"括起来，不然又会报错：

2. 使用ODBC链接SQL Server

安全性设置和之前一样就不说了。

3. 使用ODBC链接MySQL

# 查询：
SELECT * FROM OPENQUERY (LINK_ODBC_MYSQL, 'select * from test')
# 添加：
Insert openquery(LINKDB_MYSQL, 'select * from test') (Address) values('云南')
# 删除：
delete openquery(LINKDB_MYSQL, 'SELECT * FROM test where id=2')
# 或者：
delete openquery(LINKDB_MYSQL, 'SELECT * FROM test ')where id=5
# 更新：
update openquery(LINKDB_MYSQL, 'SELECT * FROM test') SET Address = '北京' WHERE id = 1

如果出现链接好之后，查询提示“Source character set not supported by client”说明数据库驱动版本不够，像上面我使用的是mysql8.0.13，但是我的odbc驱动是5.3，就会出现这个问题，更换odbc驱动为8.0.18之后正常访问。

4. 使用ODBC链接Oracle

基本跟链接MySQL差不多，驱动不同而已。
首先需要安装Oracle驱动instant client，点我这个链接直接去Oracle官网找。根据自己的需要下载相关的驱动及工具包。我这里只需要basic（这是必须的）和odbc驱动（这也是必须的，因为需要创建ODBC数据源连接Oracle数据库），因为我是64位的，所以我需要下64位的包，如下图：

这个安装教程其实很简单，我有一个文档在我的下载资源里，这里也简单写写吧，方便没有积分的朋友。
将两个压缩包解压，将里面的内容合并到同一个文件夹中。否则后面安装ODBC时会提示找不到instant client。我合并后的文件夹instantclient_11_2放在路径: C:\Program Files\Oracle\instantclient_11_2，在C:\Program Files\Oracle\instantclient_11_2目录下创建子文件夹network\admin，绝对路径也就是：C:\Program Files\Oracle\instantclient_11_2\network\admin，在该目录下新建文件tnsnames.ora和sqlnet.ora。文件中的详细内容配置可自行百度，关于sqlnet和tnsnames文件的作用也自行百度。
sqlnet.ora文件内容如下：

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames.ora文件内容如下：

ORCL_TEST=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 你的Oracle主机名)(PORT = 1521))  
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 服务名，我这里是默认的orcl)
    )
  )

设置系统环境变量：

Path	      C:\Program Files\Oracle\instantclient_11_2
在Path后面追加instantclient路径
TNS_ADMIN	  C:\Program Files\Oracle\instantclient_11_2\network\admin
tnsnames.ora所在路径
NLS_LANG	SIMPLIFIED CHINESE_CHINA.ZHS16GBK
设置Oracle字符语言集，此字符集支持中文

进入到instantclient_11_2目录，运行刚合并进来的odbc_install.exe安装ODBC驱动。直接双击运行一闪而过，看不到是否安装成功，可以通过cmd运行查看运行结果。
至此，配置全部完成。可以通过ODBC创建数据源连接Oracle数据库了，如果在程序中调用DSN时出现驱动程序与应用程序体系结构不匹配，则说明所安装的版本不正确（若安装的64位则更换32位即可解决）。

SELECT * FROM OPENQUERY (ORCL, 'select * from test')
1

增删改查写法参考MySQL部分

5. 使用Oracle OLEDB驱动提供程序链接Oracle数据库

Oracle Data Access Components (ODAC)
这种方法与第四种instant client创建odbc驱动链接的方式存在冲突，OLEDB驱动安装之后也会安装instant client的basic组件。所以需要删除之前的Path环境变量中instant client的路径，修改为稍后要创建的新路径。或者将之前的ODBC驱动重新安装到这个instant client目录中，不再使用之前的instant client。
首先需要下载OLEDB驱动，同样，我下载64位版本：https://www.oracle.com/database/technologies/odac-downloads.html

解压：

这个压缩包是包含了安装说明的文档的，里面有readme.htm。文档里也有讲，可以全部安装，也可以选择需要的组件进行安装。我这里就只安装我需要的就行了。在当前目录打开命令窗口，输入：

install.bat oledb c:\oracleOLEDB ODAC

其中oledb代表要安装的组件，c:\oracleOLEDB为安装目录，ODAC为注册表项Oracle Home的名称，卸载的时候使用uninstall.bat（需要到安装目录下使用这个命令，而不是刚刚的解压目录里），带这个名字或者目录都行。卸载命令：

uninstall.bat all ODAC

安装完成之后，就会出现OraOLEDB.Oracle这个访问接口了


接口的属性里有一些设置选项，自己酌情勾选，建议勾选"允许进程内"。
别着急去创建链接服务器，在系统环境变量中，为Path添加“C:\oracleOLEDB;C:\oracleOLEDB\bin;”(计算机属性-高级系统设置-高级-环境变量-系统环境变量-Path),如果之前安装过instant client，需要删除其路径，否则在SQL Server管理工具创建链接服务器时会卡死。
如果是供外部应用程序访问，还需要配置TNS_ADMIN和NLS_LANG环境变量，配置方式参考第四步。
最后创建链接服务器：

由于OLEDB驱动支持EZCONNECT，所以不配置tnsnames.ora也是可以的，使用IP:端口号/实例名也可以：

安全性设置不用说了，一样。
这种驱动创建的链接服务器可以使用下面这种方式进行查询，也可以使用前面的openquery方式查询。

select * from ORACLEOLEDB..SYSTEM.TEST

SYSTEM是我TEST表创建时选择的方案，注意方案前面试两个点。注意Oracle区分大小写，否则会无法查询。

以下引用内容出自：https://blog.csdn.net/jack_33/article/details/7620100

通过OleDB连接Oracle数据库，一般有两种provider
A：provider=MSDAORA.1
B：provider=OraOLEDB.Oracle
第一种为微软公司的oracle组件，第二种为oracle的访问组件。
Oracle数据库经常使用的两种字符集
C：英文：SIMPLIFIED CHINESE_CHINA.US7ASCII
D：中文：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
A与C、D搭配，数据读写没有乱码问题，如果出现乱码，则修改系统级（非用户级）的环境变量nls_lang为数据库所使用的字符集，重启电脑即可。
缺点：不支持long/Clob/Blob大字段。
B支持大字段，与D搭配最为完美。如果BC搭配使用，读出无乱码，写入为乱码，做为where条件传进去的中文参数为乱码。无法使用。如果数据库字符集为C，则只能使用A。如果要读取大字段，只得使用B新建连接

MSDAORA仅支持32位，64位机器上可能已经没有带该驱动了。
下载链接：https://www.microsoft.com/zh-CN/download/details.aspx?id=5793

sqlserver数据库如何清理日志文件

很多时候SQLSERVER的日志文件是不看的，但时间久了，够把磁盘撑爆，这时候就需要清理日志文件。使用以下方法，在实际环境中经过测试，400G的日志文件1秒就被清理

1. 将恢复模式改成“简单”

右键数据库 - 属性，切换到选项，将恢复模式修改为简单。

2. 收缩日志

右键数据库 - 任务 - 收缩 - 文件

确定后会发现，日志文件被迅速清理。

3. 命令操作

USE [master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE   --简单模式
GO
USE 要清理的数据库名称
GO
DBCC SHRINKFILE (N'要清理的数据库名称_log' , 2, TRUNCATEONLY)  --设置压缩后的日志大小为2M，可以自行指定
GO
USE [master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL  --还原为完全模式
GO

SQL Server 数据文件收缩和查看收缩进度

原文地址：https://www.cnblogs.com/VicLiu/p/16126013.html

SQL Server在删除数据后，会重新利用这部分空间，所以如果不是空间紧张的情况下，可以不回收。

回收一般先回收日志文件，因为这个回收速度非常快，可以短时间内清理出一部分可用空间。

回收步骤：

1、查看日志文件大小【一般回收比较大的】

--适用于RDS For SQL Server2012
SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [Logical Name],[Physical_Name] AS [Physical Name],((size * 8) / 1024) AS [Size(MB)]
FROM sys.master_files
ORDER BY [Size(MB)] DESC
--适用于RDS For SQL Server2008R2，需要对数据库逐个执行
USE 数据库名
GO
SELECT a.name as 逻辑文件名, size/128 [totalspace文件大小(兆)],
FILEPROPERTY(a.name, 'SpaceUsed')/128 [usedspace已用空间(兆)],
size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128 [未用空间(兆)],
FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size [使用率(%)]
FROM sys.database_files a cross join (select recovery_model_desc, log_reuse_wait,log_reuse_wait_desc,is_auto_shrink_on from sys.databases where name=DB_NAME())b
WHERE type=1

2、查看日志文件空间是否可回收【只有log_reuse_wait_desc是NOTHING状态才可回收】

SELECT [name] ,[log_reuse_wait_desc]
FROM master.sys.databases
WHERE [name]='数据库名【第1步获取】'

3、回收日志文件空间

DBCC SHRINKFILE(logicalName【第1步获取】)

常见的日志等待类型是
LOG_BACKUP，日志还没有备份，所以不能截断
解决方案：
ACTIVE_TRANSACTION，有活跃事务阻塞了日志截断
解决方案：
执行 DBCC OPENTRAN ，获取下长时间的活跃事务的SPID
然后执行 DBCC INPUTBUFFER(SPID) 查看下这个请求SQL，考虑是否可以kill阻塞源，kill后再查下log_reuse_wait，尝试shrink

4、查看数据文件大小

USE 数据库名
GO
SELECT a.name as 逻辑文件名, size/128 [totalspace文件大小(兆)],
    FILEPROPERTY(a.name, 'SpaceUsed')/128 [usedspace已用空间(兆)],
    size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128 [未用空间(兆)],
    FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size [使用率(%)]
FROM sys.database_files a cross join (select recovery_model_desc, log_reuse_wait,log_reuse_wait_desc,is_auto_shrink_on  from sys.databases where name=DB_NAME())b
WHERE type=0

5、收缩数据文件【按照经验，最好每5G循环收缩，如果影响业务，随时中断，不会回滚】

declare @usedspace int ,@totalspace int
select @usedspace= xxx,@totalspace =yyy
while @totalspace> @usedspace
begin
set @totalspace= @totalspace-5 *1024
DBCC SHRINKFILE( 逻辑文件名,@totalspace )
end

注：逻辑文件名，usedspace，totalspace从第4步的结果集获取

6、查看收缩进度【预估值】

SELECT DB_NAME(database_id) as dbname,
session_id, request_id, start_time
, percent_complete
, dateadd(mi ,estimated_completion_time/60000,getdate ()) as ETC
 FROM sys.dm_exec_requests where percent_complete<>0

-- 查询当前数据库备份进度
SELECT   DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]  
FROM sys.dm_exec_requests AS er  
WHERE er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE')  --DB_NAME(er.[database_id]) in ('ky2011') and
 
 
-- 查看数据库收缩进度
SELECT DB_NAME(er.[database_id]) [DatabaseName],er.[command] AS [CommandType],er.[percent_complete]
,er.start_time,CONVERT(DECIMAL(5, 2) , er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m] 
FROM sys.dm_exec_requests as er WHERE command in ('DbccFilesCompact','AUTOSHRINK')

Q&A

收缩数据库可以中途停止吗？
数据库太大了，有100多個G的可以釋放空閒，想要收缩，現在已經收縮15個小時了，還沒完成，请问可以停止嗎？对数据库又有影响吗？
-解决方案
可以停止，没影响的，下次再收缩，建议100M一次来做
-解决方案
如果在确定没有影响正常使用的情况下，100G的可用空间，值得等待下去。
收缩停止，对应数据库没有特别的影响。损失的，就只之前执行所用的这15个小时时间成本。
说不定再等一个小时，收缩就完成了。
目前为止，我碰到最长的等待大概在22小时左右。不停业务的情况下。

在 IIS 8.0 上配置对 Analysis Services 的 HTTP 访问

参考官方文档：https://learn.microsoft.com/zh-cn/analysis-services/instances/configure-http-access-to-analysis-services-on-iis-8-0?view=asallproducts-allversions