SQL Server 数据库部分常用语句小结（四）

42.统计APP应用的DB连接及IP情况

select b.hostname ,a.client_net_address, b.program_name ,count(1) as Qty
from sys.dm_exec_connections a(nolock) inner join sys.sysprocesses b(nolock)
on a.session_id=b.spid
where b.spid>50
--and a.client_net_address like'XXX.XXX.XXX.%'--限定某一类IP
group by b.hostname,a.client_net_address,b.program_name
order by 4 desc

43.查看及收缩数据库log

--查看日志大小
select DB_NAME(database_id),name,size/128
from sys.master_files
where type_desc='LOG' and size/128>1000
order by size desc
--拼收缩脚本
select 'use '+DB_NAME(database_id)+char(10)+'GO'+char(10)+'DBCC SHRINKFILE (N'''+name+''', 0, TRUNCATEONLY)'+CHAR(10)+'GO'
from sys.master_files
where type_desc='LOG' and size/128>1000
order by size desc

44.权限授权，例如将某表的select 权限授予指定用户

use [DB]
GO
GRANT SELECT ON [表] TO [用户]
GO

45.查看现在正在SQL Server执行的命令

首先查看sysprocesses系统表的数据(存放有关在 Microsoft SQL Server 实例中运行的进程的信息,这些进程可以是客户端进程或系统进程)。

查询示例：
select * from master..sysprocesses

在等到所有执行命令的进程ID了，如果要查看该进程的具体的SQL语句，可以利用以下语句查看：
dbcc inputbuffer(进程号)
例如：
dbcc inputbuffer(56)

46.将数据库设置为单用户模式

EXEC sp_dboption '数据库名字', 'Single User', 'TRUE'
EXEC sp_renamedb '数据库名字', '更新后的数据库名字'
EXEC sp_dboption '更新后的数据库名字', 'Single User', 'FALSE'

47.查看SQL Server当前正在执行的查询语句

select   db_name(r.database_id) as db_name
        ,s.group_id
        ,r.session_id
        ,r.blocking_session_id as blocking
        ,s.login_name
        ,r.wait_type as current_wait_type
        ,r.wait_resource
        ,r.last_wait_type
        ,r.wait_time/1000 as wait_s
        ,r.status as request_status
        ,r.command
        ,r.cpu_time
        ,r.reads
        ,r.writes
        ,r.logical_reads
        ,r.total_elapsed_time
        ,r.start_time
        ,s.status as session_status
        ,substring( st.text, 
                    r.statement_start_offset/2+1,
                    ( case when r.statement_end_offset = -1 
                                then len(convert(nvarchar(max), st.text))
                           else (r.statement_end_offset - r.statement_start_offset)/2
                      end 
                    )
                ) as individual_query
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s 
    on r.session_id=s.session_id
outer APPLY sys.dm_exec_sql_text(r.sql_handle) as st
where ((r.wait_type<>'MISCELLANEOUS' and r.wait_type <> 'DISPATCHER_QUEUE_SEMAPHORE' ) or r.wait_type is null)
    and r.session_id>50
    and r.session_id<>@@spid
order by r.session_id asc

48.导出所有Job的创建脚本

step1 .在Microsoft SQL Server Management Studio中，选择SQL Server Agent->Jobs，按F7打开Object Explorer Details窗口，选中所有Job；

Step2.点击鼠标右键，选择Script Job as->CREATE To->New Query Editor Windows，然后所有Job会以SQL脚本的方式显示在查询窗口中。

49 安装SQL Server实例过程报错

错误信息

TITLE: Microsoft SQL Server 2008 R2 Setup
------------------------------

The following error has occurred:

The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.

Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0x88792597%25400xD69AB41D

------------------------------
BUTTONS:

&Retry
Cancel
------------------------------

原因

此行为是设计使然。除了添加以本地管理员身份运行安装程序的用户帐户之外，安装用户帐户还需要以下默认用户权限才能成功完成安装程序。

本地策略对象显示名称	用户权限
管理审核和安全日志	SeSecurityPrivilege

解决方案：

以具有管理凭据的用户身份登录到计算机。
单击 " 开始"，单击 " 运行"，键入 Control Admintools，然后单击 "确定"。
双击 " 本地安全策略"。
在 " 本地安全设置 " 对话框中，单击 " 本地策略"，双击 " 用户权限分配"，然后双击 " 管理审核和安全日志"。
在 " 管理审核和安全日志 " 对话框中，单击 " 添加用户或组"。
在 " 选择用户或组 " 对话框中，键入用于安装程序的用户帐户，然后单击 "确定" 两次。
单击 " 退出 " 以关闭 " 本地安全设置 " 对话框。

此问题的详细资料可参照以下网址:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1960bc81-d48e-462f-933a-c51cd9f94651/install-sql-server-2008-rtm-on-windows-server-2008-r2-cluster?forum=sqlsetupandupgrade

https://docs.microsoft.com/zh-cn/troubleshoot/sql/install/installation-fails-if-remove-user-right

在安装SQL Server 2016时，会提前检查，例如检查不通过，报错如下，处理方法一样的

修改后，需要重启电脑才能生效

50.通过scripts管理复制订阅的表（增加/删除）

--查看复制订阅属性

select * from distribution..MSdistribution_agents


--添加复制分发对象
exec configdb.dbo.uxpm_repl_addarticle @publisher_db ='XXXXDB',@publication ='????',@article ='表名'

--删除复制分发对象

exec configdb.dbo.uxpm_repl_droparticle @publisher_db ='XXXXDB',@publication ='????',@article ='表名'


--启动快照，初始化数据
select 
'EXEC msdb.dbo.sp_start_job '''+name+''''
from msdb..sysjobs
where name like '%????%'------publication的名字
and category_id=15

51.SQL Server 密码策略调整

我们知道SQL Server 账号的密码策略（复杂度和生命期等）是基于Windows的，那么怎么查看和修改呢？相信有些同学还不是很清楚。

其实，策略如下:

复杂度策略

lockout策略（重试拒绝策略）

52 刷新表的统计信息

update statistics  表名

更新统计信息时，会对表加只读锁，所以会阻塞对表的修改操作。sql server会按照一定的百分比，扫描表中的数据，更新统计信息，而不会扫描整个表的所有数据。由于扫描数据的百分比很小，这个锁定过程持续的时间会很短，所以影响不大。另外，更新统计信息后，会导致相关表的执行计划无效，之前编译缓存的存储过程如果再次执行，会被重新编译。