1.服务器磁盘监控
（1）总链接服务上开启所有链接服务器的RPC：
1----------------------总链接服务器上面，开启每个服务器的RPC 2--exec sp_serveroption @server='S32' , @optname= 'rpc', @optvalue ='TRUE' 3 4--exec sp_serveroption @server='S32' , @optname= 'rpc out', @optvalue='TRUE' 5 6SELECT 'exec sp_serveroption @server= '''+ name+''' , @optname= ''rpc'',@optvalue =''TRUE'' ' FROM sys.servers 7ORDER BY name 8SELECT 'exec sp_serveroption @server= '''+ name+''' , @optname= ''rpc out'',@optvalue =''TRUE'' ' FROM sys.servers 9ORDER BY name
（2）各个服务上创建监测（根据本地情况，需要改总链接服务器IP和本机IP）：
1------添加链接服务器 2IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name like '%100.80.10.30%') 3BEGIN 4 EXEC master.dbo.sp_addlinkedserver 5 --服务器名称 6 @server = '100.80.10.30', --36-48,12-15, 7 @srvproduct = N'', @provider = 'SQLOLEDB', 8 --IP 9 @datasrc = '100.80.10.30'; 10 EXEC master.dbo.sp_addlinkedsrvlogin --IP 11 '100.80.10.30', false, NULL, --账号 12 'sa', --密码 13 'hnsjt_lwsj@2018'; 14END; 15GO 16 17 18CREATE DATABASE [monitor]; 19GO 20USE [monitor]; 21GO 22SET ANSI_NULLS ON; 23GO 24SET QUOTED_IDENTIFIER ON; 25GO 26-- ============================================= 27-- Author: <Author,,Name> k 28-- Create date: <Create Date,,>20190218 29-- Description: <Description,,>监控磁盘使用情况 30-- ============================================= 31 32CREATE PROCEDURE disk_monitor 33AS 34 BEGIN 35 36 37 SET NOCOUNT ON; 38 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 39 40 --创建相关表 41 IF OBJECT_ID('tempdb..#server_disk_usage') IS NULL 42 BEGIN 43 CREATE TABLE #server_disk_usage 44 ( 45 [ip] [NVARCHAR](400) , 46 [disk_num] [NVARCHAR](10) NOT NULL , 47 [total_size_mb] [BIGINT] 48 NOT NULL 49 CONSTRAINT [DF_#server_disk_usage_total_size_mb] 50 DEFAULT ( (0) ) , 51 [free_siez_mb] [BIGINT] 52 NOT NULL 53 CONSTRAINT [DF_#server_disk_usage_free_siez_mb] 54 DEFAULT ( (0) ) , 55 [disk_info] [NVARCHAR](400) 56 NOT NULL 57 CONSTRAINT [DF_#server_disk_usage_disk_info] 58 DEFAULT ( '' ) , 59 [iswarning] [NVARCHAR](400) , 60 [check_time] [DATETIME] 61 NOT NULL 62 CONSTRAINT [DF_#server_disk_usage_check_time] 63 DEFAULT ( GETDATE() ) , 64 CONSTRAINT [PK_#server_disk_usage] PRIMARY KEY CLUSTERED 65 ( [disk_num] ASC ) 66 ) 67 ON [PRIMARY]; 68 END; 69 70 71 --========================================== 72 --查看所有数据库使用到的磁盘剩余空间 73 DECLARE @disk TABLE 74 ( 75 [disk_num] VARCHAR(50) , 76 [free_siez_mb] INT 77 ); 78 INSERT INTO @disk 79 EXEC xp_fixeddrives; 80 81 --更新当前磁盘的剩余空间信息 82 UPDATE M 83 SET M.[free_siez_mb] = D.[free_siez_mb] 84 FROM #server_disk_usage AS M 85 INNER JOIN @disk AS D ON M.[disk_num] = D.[disk_num]; 86 87 --插入新增磁盘的剩余空间信息 88 INSERT INTO #server_disk_usage 89 ( [disk_num] , 90 [free_siez_mb] 91 ) 92 SELECT [disk_num] , 93 [free_siez_mb] 94 FROM @disk AS D 95 WHERE NOT EXISTS ( SELECT 1 96 FROM #server_disk_usage AS M 97 WHERE M.[disk_num] = D.[disk_num] ); 98 99 100 101 -------------------------------------------------------2 102 103 104 105 106 107 --开启CMDShell 108 EXEC sp_configure 'show advanced options', 1; 109 110 RECONFIGURE WITH OVERRIDE; 111 112 EXEC sp_configure 'xp_cmdshell', 1; 113 114 RECONFIGURE WITH OVERRIDE; 115 116 --======================================== 117 --创建临时表用来存放每个盘符的数据 118 CREATE TABLE #tempDisks 119 ( 120 ID INT IDENTITY(1, 1) , 121 DiskSpace NVARCHAR(200) 122 ); 123 --============================================ 124 --将需要检查的磁盘放入临时表#checkDisks 125 SELECT ROW_NUMBER() OVER ( ORDER BY [disk_num] ) AS RID , 126 [disk_num] 127 INTO #checkDisks 128 FROM #server_disk_usage 129 WHERE [total_size_mb] = 0; 130 131 --============================================ 132 --循环临时表#checkDisks检查每个磁盘的总量 133 134 DECLARE @disk_num NVARCHAR(20); 135 DECLARE @total_size_mb INT; 136 DECLARE @sql NVARCHAR(200); 137 DECLARE @max INT; 138 DECLARE @min INT; 139 SELECT @max = MAX(RID) , 140 @min = MIN(RID) 141 FROM #checkDisks; 142 143 WHILE ( @min <= @max ) 144 BEGIN 145 SELECT @disk_num = [disk_num] 146 FROM #checkDisks 147 WHERE RID = @min; 148 149 SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree ' 150 + @disk_num + ':' + ''''; 151 PRINT @sql; 152 153 INSERT INTO #tempDisks 154 EXEC ( @sql 155 ); 156 157 SELECT @total_size_mb = CAST(( RIGHT(DiskSpace, 158 LEN(DiskSpace) 159 - CHARINDEX(': ', 160 DiskSpace) - 1) ) AS BIGINT) 161 / 1024 / 1024 162 FROM #tempDisks 163 WHERE ID = 2; 164 165 --SELECT @total_size_mb,@disk_num 166 167 UPDATE #server_disk_usage 168 SET [total_size_mb] = @total_size_mb 169 WHERE [disk_num] = @disk_num; 170 171 --SELECT * FROM #tempDisks 172 173 TRUNCATE TABLE #tempDisks; 174 175 SET @min = @min + 1; 176 177 END; 178 179 180 --========================================== 181 --CMDShell 182 183 EXEC sp_configure 'xp_cmdshell', 0; 184 185 EXEC sp_configure 'show advanced options', 1; 186 187 RECONFIGURE WITH OVERRIDE; 188 189 190 191 DELETE FROM [100.80.10.30].[monitor].[dbo].[server_disk_usage] 192 WHERE CONVERT(VARCHAR, check_time, 23) = CONVERT(VARCHAR, GETDATE(), 23) 193 AND ip = '100.80.10.48'; 194 INSERT INTO [100.80.10.30].[monitor].[dbo].[server_disk_usage] 195 SELECT '100.80.10.48' AS IP , 196 [disk_num] AS Drive_Name , 197 CAST([total_size_mb] / 1024.0 AS NUMERIC(18, 2)) AS Total_Space_GB , 198 CAST(( [total_size_mb] - [free_siez_mb] ) / 1024.0 AS NUMERIC(18, 199 2)) AS Used_Space_GB , 200 CAST([free_siez_mb] / 1024.0 AS NUMERIC(18, 2)) AS Free_Space_GB , 201 CAST([free_siez_mb] * 100 / [total_size_mb] AS NUMERIC(18, 202 2)) AS Free_Space_Percent , 203 [disk_info] , 204 [check_time] 205 FROM #server_disk_usage; 206 207 208 DROP TABLE #server_disk_usage; 209 210 211 END; 212GO
（3）总链接服务器创建监测：
1CREATE DATABASE [monitor]; 2GO 3 4USE [monitor]; 5GO 6/****** Object: StoredProcedure [dbo].[get_disk_use_info] Script Date: 02/19/2019 09:46:23 ******/ 7SET ANSI_NULLS ON; 8GO 9SET QUOTED_IDENTIFIER ON; 10GO 11-- ============================================= 12-- Author: <Author,,Name> k 13-- Create date: <Create Date,,>20190218 14-- Description: <Description,,>监控磁盘使用情况 15-- ============================================= 16CREATE PROCEDURE [dbo].[get_disk_use_info] 17AS 18 BEGIN 19 20 21 22 23 24 SET NOCOUNT ON; 25 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 26 --****************************************************** 27 --****************************************************** 28 IF OBJECT_ID('server_disk_usage') IS NOT NULL 29 BEGIN 30 DROP TABLE server_disk_usage; 31 END; 32 33 34 --========================================== 35 --创建相关表 36 IF OBJECT_ID('tempdb..#server_disk_usage') IS NULL 37 BEGIN 38 CREATE TABLE #server_disk_usage 39 ( 40 [disk_num] [NVARCHAR](10) NOT NULL , 41 [total_size_mb] [BIGINT] 42 NOT NULL 43 CONSTRAINT [DF_#server_disk_usage_total_size_mb] 44 DEFAULT ( (0) ) , 45 [free_siez_mb] [BIGINT] 46 NOT NULL 47 CONSTRAINT [DF_#server_disk_usage_free_siez_mb] 48 DEFAULT ( (0) ) , 49 [disk_info] [NVARCHAR](400) 50 NOT NULL 51 CONSTRAINT [DF_#server_disk_usage_disk_info] 52 DEFAULT ( '' ) , 53 [check_time] [DATETIME] 54 NOT NULL 55 CONSTRAINT [DF_#server_disk_usage_check_time] 56 DEFAULT ( GETDATE() ) , 57 CONSTRAINT [PK_#server_disk_usage] PRIMARY KEY CLUSTERED 58 ( [disk_num] ASC ) 59 ) 60 ON [PRIMARY]; 61 END; 62 63 64 --========================================== 65 --查看所有数据库使用到的磁盘剩余空间 66 DECLARE @disk TABLE 67 ( 68 [disk_num] VARCHAR(50) , 69 [free_siez_mb] INT 70 ); 71 INSERT INTO @disk 72 EXEC xp_fixeddrives; 73 74 --更新当前磁盘的剩余空间信息 75 UPDATE M 76 SET M.[free_siez_mb] = D.[free_siez_mb] 77 FROM #server_disk_usage AS M 78 INNER JOIN @disk AS D ON M.[disk_num] = D.[disk_num]; 79 80 --插入新增磁盘的剩余空间信息 81 INSERT INTO #server_disk_usage 82 ( [disk_num] , 83 [free_siez_mb] 84 ) 85 SELECT [disk_num] , 86 [free_siez_mb] 87 FROM @disk AS D 88 WHERE NOT EXISTS ( SELECT 1 89 FROM #server_disk_usage AS M 90 WHERE M.[disk_num] = D.[disk_num] ); 91 92 93 94 -------------------------------------------------------2 95 96 97 98 99 100 --开启CMDShell 101 EXEC sp_configure 'show advanced options', 1; 102 103 RECONFIGURE WITH OVERRIDE; 104 105 EXEC sp_configure 'xp_cmdshell', 1; 106 107 RECONFIGURE WITH OVERRIDE; 108 109 --======================================== 110 --创建临时表用来存放每个盘符的数据 111 CREATE TABLE #tempDisks 112 ( 113 ID INT IDENTITY(1, 1) , 114 DiskSpace NVARCHAR(200) 115 ); 116 --============================================ 117 --将需要检查的磁盘放入临时表#checkDisks 118 SELECT ROW_NUMBER() OVER ( ORDER BY [disk_num] ) AS RID , 119 [disk_num] 120 INTO #checkDisks 121 FROM #server_disk_usage 122 WHERE [total_size_mb] = 0; 123 124 --============================================ 125 --循环临时表#checkDisks检查每个磁盘的总量 126 127 DECLARE @disk_num NVARCHAR(20); 128 DECLARE @total_size_mb INT; 129 DECLARE @sql NVARCHAR(200); 130 DECLARE @max INT; 131 DECLARE @min INT; 132 SELECT @max = MAX(RID) , 133 @min = MIN(RID) 134 FROM #checkDisks; 135 136 WHILE ( @min <= @max ) 137 BEGIN 138 SELECT @disk_num = [disk_num] 139 FROM #checkDisks 140 WHERE RID = @min; 141 142 SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree ' 143 + @disk_num + ':' + ''''; 144 PRINT @sql; 145 146 INSERT INTO #tempDisks 147 EXEC ( @sql 148 ); 149 150 SELECT @total_size_mb = CAST(( RIGHT(DiskSpace, 151 LEN(DiskSpace) 152 - CHARINDEX(': ', 153 DiskSpace) - 1) ) AS BIGINT) 154 / 1024 / 1024 155 FROM #tempDisks 156 WHERE ID = 2; 157 158 --SELECT @total_size_mb,@disk_num 159 160 UPDATE #server_disk_usage 161 SET [total_size_mb] = @total_size_mb 162 WHERE [disk_num] = @disk_num; 163 164 --SELECT * FROM #tempDisks 165 166 TRUNCATE TABLE #tempDisks; 167 168 SET @min = @min + 1; 169 170 END; 171 172 173 --========================================== 174 --CMDShell 175 176 EXEC sp_configure 'xp_cmdshell', 0; 177 178 EXEC sp_configure 'show advanced options', 1; 179 180 RECONFIGURE WITH OVERRIDE; 181 182 183 184 IF OBJECT_ID('server_disk_usage') IS NULL 185 BEGIN 186 CREATE TABLE server_disk_usage 187 ( 188 ip VARCHAR(200) , 189 Drive_Name VARCHAR(200) , 190 Total_Space_GB VARCHAR(200) , 191 Used_Space_GB VARCHAR(200) , 192 Free_Space_GB VARCHAR(200) , 193 Free_Space_Percent VARCHAR(200) , 194 [disk_info] VARCHAR(200) , 195 [check_time] DATETIME 196 ); 197 END; 198 199 200 INSERT INTO [server_disk_usage] 201 SELECT '100.80.10.30' AS IP , 202 [disk_num] AS Drive_Name , 203 CAST([total_size_mb] / 1024.0 AS NUMERIC(18, 2)) AS Total_Space_GB , 204 CAST(( [total_size_mb] - [free_siez_mb] ) / 1024.0 AS NUMERIC(18, 205 2)) AS Used_Space_GB , 206 CAST([free_siez_mb] / 1024.0 AS NUMERIC(18, 2)) AS Free_Space_GB , 207 CAST([free_siez_mb] * 100 / [total_size_mb] AS NUMERIC(18, 208 2)) AS Free_Space_Percent , 209 [disk_info] , 210 [check_time] 211 FROM #server_disk_usage; 212 213 214 DROP TABLE #server_disk_usage; 215 216 217 END;
（4）在总链接服务器上创建调用的存储过程，调用各个服务器上的监控,更新各个服务器磁盘使用情况，存放至 [NETWORKING_AUDIT] 库的T_DISK_MONITOR表
1-- ============================================= 2-- Author: k 3-- Create date: 20190219 4-- Description: 更新各个服务器磁盘使用情况，存放至T_DISK_MONITOR表 5-- ============================================= 6ALTER PROCEDURE [dbo].[updateDiskMonitorInfo] 7 8AS 9BEGIN 10 -------------调用各个服务器上的监控 11 EXEC [monitor].[dbo].[get_disk_use_info] 12 DECLARE @ServerName VARCHAR(255); 13 DECLARE @sql VARCHAR(8000); 14 DECLARE cur CURSOR 15 FOR 16 SELECT name FROM sys.servers 17 WHERE name !='WIN-4AAGSGNR81A' 18 OPEN cur; 19 FETCH NEXT FROM cur INTO @ServerName; 20 WHILE @@FETCH_STATUS = 0 21 BEGIN 22 SET @sql = 'EXEC ['+@ServerName+'].[monitor]..[disk_monitor];' 23 EXEC (@sql); 24 FETCH NEXT FROM cur INTO @ServerName; 25 26 END; 27 CLOSE cur; 28 DEALLOCATE cur; 29 30 31 -------------将磁盘使用情况存放至系统库[NETWORKING_AUDIT] 32 IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T_DISK_MONITOR') 33 BEGIN 34 DROP TABLE [NETWORKING_AUDIT].[dbo].[T_DISK_MONITOR]; 35 END 36 SELECT * INTO [NETWORKING_AUDIT].[dbo].[T_DISK_MONITOR] 37 from [monitor].[dbo].[server_disk_usage] 38 ORDER BY ip 39 40 41END
（5）创建SQLSERVER定时作业，每天自动更新即可
如图：
2.创建管理员账号分配权限
（1）基本语法：
1--查看数据库schema, user 的存储过程 2select * from sys.database_principals 3select * from sys.schemas 4select * from sys.server_principals 5select * from sysusers 6select * from sys.syslogins 7 8--创建登陆帐户（create login） 9create login dba with password='abcd1234@', default_database=mydb 10 11--为登陆账户创建数据库用户（create user）,在mydb数据库中的security中的user下可以找到新创建的dba 12create user dba for login dba with default_schema=dbo 13 14--通过加入数据库角色，赋予数据库用户“db_owner”权限 15exec sp_addrolemember 'db_owner', 'dba' 16 17--让 SQL Server 登陆帐户“dba”访问多个数据库 18use mydb2 19go create user dba for login dba with default_schema=dbo 20go exec sp_addrolemember 'db_owner', 'dba'go 21 22--禁用登陆帐户 23alter login dba disable 24--启用登陆帐户 25alter login dba enable 26--登陆帐户改名 27alter login dba with name=dba_tom 28--登陆帐户改密码： 29alter login dba with password='aabb@ccdd' 30--数据库用户改名： 31alter user dba with name=dba_tom 32--更改数据库用户 defult_schema： 33alter user dba with default_schema=sales 34--删除数据库用户： 35drop user dba 36--删除 SQL Server登陆帐户： 37drop login dba 38 39 40 41--创建角色 r_test 42EXEC sp_addrole 'r_test' 43 44--添加登录 l_test,设置密码为pwd,默认数据库为pubs 45EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008' 46 47--为登录 l_test 在数据库 pubs 中添加安全账户 u_test 48EXEC sp_grantdbaccess 'l_test','u_test' 49 50--添加 u_test 为角色 r_test 的成员 51EXEC sp_addrolemember 'r_test','u_test' 52 53 54--用l_test登陆,发现在SSMS中找不到仍和表，因此执行下述两条语句出错。 55select * from Sales.Orders 56select * from HR.Employees 57 58--授予角色 r_test 对 HR.Employees 表的所有权限 59GRANT ALL ON HR.Employees TO r_test 60--The ALL permission is deprecated and maintained only for compatibility. 61--It DOES NOT imply ALL permissions defined on the entity. 62--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。 63 64--测试可以查询表HR.Employees，但是Sales.Orders无法查询 65select * from HR.Employees 66 67 68--如果要收回权限，可以使用如下语句。（可选择执行） 69revoke all on HR.Employees from r_test 70--ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。 71 72 73--授予角色 r_test 对 Sales.Orders 表的 SELECT 权限 74GRANT SELECT ON Sales.Orders TO r_test 75 76--用l_test登陆,发现可以查询Sales.Orders和HR.Employees两张表 77select * from Sales.Orders 78select * from HR.Employees 79 80--拒绝安全账户 u_test 对 HR.Employees 表的 SELECT 权限 81DENY SELECT ON HR.Employees TO u_test 82 83--再次执行查询HR.Employees表的语句，提示：拒绝了对对象 'Employees' (数据库 'InsideTSQL2008'，架构 'HR')的 SELECT 权限。 84select * from HR.Employees 85 86--重新授权 87GRANT SELECT ON HR.Employees TO u_test 88 89--再次查询，可以查询出结果。 90select * from HR.Employees 91 92 93USE InsideTSQL2008 94--从数据库中删除安全账户,failed 95EXEC sp_revokedbaccess 'u_test' 96--删除角色 r_test,failed 97EXEC sp_droprole 'r_test' 98--删除登录 l_test,success 99EXEC sp_droplogin 'l_test'
（2）创建管理员用户和创建只读用户
1--******************创建管理员账号******************-- 2--**************************************************-- 3USE master 4Go 5--创建登陆帐户（create login） 6create login lwsjbackgr with password='hnsjt_lwsj@2019', default_database=master 7 8--为登陆账户创建数据库用户（create user）,在mydb数据库中的security中的user下可以找到新创建的dba 9create user lwsjbackgr for login lwsjbackgr with default_schema=dbo 10 11--添加服务器角色 12EXEC master.dbo.sp_addsrvrolemember @loginame = N'lwsjbackgr', @rolename = N'sysadmin' 13 14--添加数据库角色 15--use [国土资源厅] 16--create user sread for login sread with default_schema=dbo 17 --此处分配只读 18--exec sp_addrolemember 'db_datareader', 'sread' 19 20--禁用sa账号 21alter login sa DISABLE 22 23 24 25--******************创建只读权限账号****************-- 26--**************************************************-- 27 28--------------------------强制中断连接中的数据库 29CREATE TABLE #temt(spid NVARCHAR(max), 30ecid NVARCHAR(max), 31status NVARCHAR(max), 32logname NVARCHAR(max), 33hostname NVARCHAR(max), 34blk NVARCHAR(max), 35dbname NVARCHAR(max), 36cmd NVARCHAR(max), 37request_id NVARCHAR(max) 38) 39INSERT INTO #temt 40exec sp_who 41 42IF EXISTS(SELECT 1 FROM #temt WHERE logname = 'sread') 43BEGIN 44 DECLARE @spid NVARCHAR(200) 45 DECLARE @ksql NVARCHAR(200) 46 DECLARE cur CURSOR 47 FOR 48 SELECT spid FROM #temt WHERE logname = 'sread' 49 OPEN cur; 50 FETCH NEXT FROM cur INTO @spid; 51 WHILE @@FETCH_STATUS = 0 52 BEGIN 53 SET @ksql = 'KILL '+@spid 54 EXEC (@ksql) 55 FETCH NEXT FROM cur INTO @spid; 56 END; 57 CLOSE cur; 58 DEALLOCATE cur; 59END 60DROP TABLE #temt 61 62--------------------------删除只读用户 63IF EXISTS(Select 1 FROM sys.sysusers WHERE name ='sread') 64BEGIN 65 DROP USER sread 66END 67IF EXISTS(Select 1 FROM sys.syslogins WHERE name ='sread') 68BEGIN 69 DROP LOGIN sread 70END 71 72 73 74 75 76--创建只读用户（create login） 77create login sread with password='hnsjt_123456', default_database=master 78 79--为只读用户创建数据库用户（create user）,在mydb数据库中的security中的user下可以找到新创建的dba 80create user sread for login sread with default_schema=dbo 81 82 83--------------------------每个数据库添加只读权限角色 84 85 DECLARE @DatabaseName VARCHAR(255); 86 DECLARE @user VARCHAR(255); 87 SET @user = 'sread' 88 DECLARE @sql VARCHAR(8000); 89 DECLARE cur CURSOR 90 FOR 91 SELECT name FROM sys.databases 92 93 OPEN cur; 94 FETCH NEXT FROM cur INTO @DatabaseName; 95 WHILE @@FETCH_STATUS = 0 96 BEGIN 97 SET @sql = 'USE ['+@DatabaseName+'] 98 IF EXISTS(Select 1 FROM sys.sysusers WHERE name = '''+@user+''') 99 BEGIN 100 EXEC sp_revokedbaccess '''+@user+''' 101 END 102 create user '+@user+' for login '+@user+' with default_schema=dbo 103 EXEC sp_addrolemember ''db_datareader'','''+@user+''' 104 ' 105 EXEC (@sql); 106 FETCH NEXT FROM cur INTO @DatabaseName; 107 108 END; 109 CLOSE cur; 110 DEALLOCATE cur; 111 112
3.添加链接服务器
1------查询现有链接服务器状态 2 SELECT * FROM SYS.servers 3 4--创删除远程链接服务器 5 6--删除运行本地与远程之间的用户映射 7execute sys.sp_droplinkedsrvlogin @rmtsrvname='100.80.10.30',@locallogin=null 8--删除链接服务器 9execute sys.sp_dropserver @server='100.80.10.30',@droplogins='droplogins' 10 11 12--添加远程链接服务器 13 14--创建远程链接服务器 15execute sys.sp_addlinkedserver @server='100.80.10.30', --被访问的服务器别名 16 @srvproduct='', 17 @provider='SQLOLEDB', 18 @datasrc='100.80.10.30' --被访问的服务器地址(IP地址,端口号\服务器名称) 19--创建本地用户与远程服务器中用户之间的映射 20execute sys.sp_addlinkedsrvlogin @rmtsrvname='100.80.10.30', --被访问的服务器别名 21 @useself='false', --是否通过模拟本地登录名或显式提交登录名和密码来连接到远程服务器 22 @locallogin=null, --本地登录 23 @rmtuser='lwsjbackgr', --用户名 24 @rmtpassword='hnsjt_lwsjdb@2019' --密码 25go
4.查询CPU
1DECLARE @ts_now bigint 2set @ts_now = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) 3FROM master.sys.dm_os_sys_info WITH (NOLOCK)); 4SELECT 5DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] , 6SQLProcessUtilization AS [SQL Server Process CPU Utilization], 7SystemIdle AS [System Idle Process], 8(100 - SystemIdle - SQLProcessUtilization) AS [Other Process CPU Utilization] 9FROM ( 10 SELECT 11 record.value('(./Record/@id)[1]', 'int') AS record_id, 12 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], 13 record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization], 14 [timestamp] 15 FROM ( 16 SELECT [timestamp], CONVERT(xml, record) AS [record] 17 FROM master.sys.dm_os_ring_buffers 18 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 19 AND record LIKE N'%%') AS x 20 ) AS y 21ORDER BY record_id DESC;
5.查询内存
1SELECT total_physical_memory_kb / 1024 AS [物理内存(MB)] , 2 available_physical_memory_kb / 1024 AS [可用物理内存(MB)] , 3 system_cache_kb / 1024 AS [系统缓存内存总量(MB)] , 4 ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS [内核池内存总量(MB)] , 5 total_page_file_kb / 1024 AS [操作系统报告的提交限制的大小(MB)] , 6 available_page_file_kb / 1024 AS [未使用的页文件的总量(MB)] , 7 system_memory_state_desc AS [内存状态说明] 8FROM sys.dm_os_sys_memory