SQL server 存储过程+作业 定时抓取数据库服务器 状态 实现巡检（一台服务器查看多台服务器）

已于 2022-05-17 11:31:11 修改
阅读量3.3k 收藏 5
点赞数
分类专栏： SQL server 文章标签： 数据库 服务器 sqlserver
于 2018-07-10 17:34:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhangyongze_z/article/details/80989403
版权

数据库服务器巡检得到结果如下
这里写图片描述

创建存储过程获取服务器状态

 
create   PROCEDURE [dbo].[proc_Server_inspection]
(
	@server_ip VARCHAR(20),
	@db_name VARCHAR(20)

)
AS
BEGIN
	SET NOCOUNT ON;

	--变量定义
	--计算过程执行时长
	DECLARE @starttime DATETIME
	DECLARE @totaltime INT
	--执行影响行数
	DECLARE @ROW INT
	--错误日志
	DECLARE @ErrorMessage VARCHAR(1000)

	--变量赋值
	SET @starttime = GETDATE()

	--创建临时表
create TABLE #tmp_inspection 
  (
       [cntr_time]  DATETIME   NULL ,
      [counter_name] VARCHAR(100) NULL ,
      [cntr_value] NUMERIC(27, 6) NULL ,
       )
ON  [PRIMARY];

IF @db_name ='本机'
BEGIN 

INSERT INTO    #tmp_inspection(cntr_time,counter_name,cntr_value)
EXEC  ( 'SELECT    GETDATE() AS cntr_time ,
                    counter_name ,
                    ( CASE WHEN object_name = ''SQLServer:Databases''
                           THEN cntr_value * 1.0 / 1024
                           ELSE cntr_value
                      END ) AS cntr_value
          FROM        sys.dm_os_performance_counters
          WHERE     counter_name IN ( ''Active Temp Tables'',
                                      ''Logical Connections'',
                                      ''User Connections'', ''Processes blocked'',
                                      ''Data File(s) Size (KB)'',
                                      ''Log File(s) Size (KB)'',
                                      ''Log File(s) Used Size (KB)'',
                                      ''Buffer cache hit ratio'',
                                      ''Buffer cache hit ratio base'' )
                    AND object_name IN ( ''SQLServer:Buffer Manager'',
                                         ''SQLServer:General Statistics'',
                                         ''SQLServer:Databases'' )
                    AND instance_name = ''''
          UNION
          SELECT    GETDATE() AS cntr_time ,
                    counter_name ,
                    cntr_value
          FROM       sys.dm_os_performance_counters
          WHERE     counter_name IN ( ''Page reads/sec'', ''Page writes/sec'',
                                      ''Latch Waits/sec'', ''Full Scans/sec'',
                                      ''Index Searches/sec'',
                                      ''Batch Requests/sec'', ''Transactions/sec'',
                                      ''Write Transactions/sec'' )
                    AND object_name IN ( ''SQLServer:Buffer Manager'',
                                         ''SQLServer:Databases'',
                                         ''SQLServer:Latches'',
                                         ''SQLServer:Access Methods'',
                                         ''SQLServer:SQL Statistics'' )
                    AND instance_name = ''''
         ;')
INSERT  INTO Server_inspection_day
EXEC  ('SELECT   
				'''+@server_ip+'''  ,
	
                *
        FROM    #tmp_inspection PIVOT  ( MAX(cntr_value) FOR [counter_name] IN ( [Active Temp Tables],
                                                              [Batch Requests/sec],
                                                              [Buffer cache hit ratio],
                                                              [Buffer cache hit ratio base],
                                                              [Full Scans/sec],
                                                              [Index Searches/sec],
                                                              [Latch Waits/sec],
                                                              [Logical Connections],
                                                              [Page reads/sec],
                                                              [Page writes/sec],
                                                              [Processes blocked],
                                                              [User Connections] ) )      AS b;')

END

ELSE 
BEGIN

INSERT INTO    #tmp_inspection(cntr_time,counter_name,cntr_value)
EXEC  ('SELECT    GETDATE() AS cntr_time ,
                    counter_name ,
                    ( CASE WHEN object_name = ''SQLServer:Databases''
                           THEN cntr_value * 1.0 / 1024
                           ELSE cntr_value
                      END ) AS cntr_value
          FROM      ['+@server_ip+'].['+@db_name+'].sys.dm_os_performance_counters
          WHERE     counter_name IN ( ''Active Temp Tables'',
                                      ''Logical Connections'',
                                      ''User Connections'', ''Processes blocked'',
                                      ''Data File(s) Size (KB)'',
                                      ''Log File(s) Size (KB)'',
                                      ''Log File(s) Used Size (KB)'',
                                      ''Buffer cache hit ratio'',
                                      ''Buffer cache hit ratio base'' )
                    AND object_name IN ( ''SQLServer:Buffer Manager'',
                                         ''SQLServer:General Statistics'',
                                         ''SQLServer:Databases'' )
                    AND instance_name = ''''
          UNION
          SELECT    GETDATE() AS cntr_time ,
                    counter_name ,
                    cntr_value
          FROM      ['+@server_ip+'].['+@db_name+'].sys.dm_os_performance_counters
          WHERE     counter_name IN ( ''Page reads/sec'', ''Page writes/sec'',
                                      ''Latch Waits/sec'', ''Full Scans/sec'',
                                      ''Index Searches/sec'',
                                      ''Batch Requests/sec'', ''Transactions/sec'',
                                      ''Write Transactions/sec'' )
                    AND object_name IN ( ''SQLServer:Buffer Manager'',
                                         ''SQLServer:Databases'',
                                         ''SQLServer:Latches'',
                                         ''SQLServer:Access Methods'',
                                         ''SQLServer:SQL Statistics'' )
                    AND instance_name = ''''
         ;')

 INSERT  INTO Server_inspection_day
EXEC( 'SELECT   
				  '''+@server_ip+''' ,
	
                *
        FROM    #tmp_inspection PIVOT  ( MAX(cntr_value) FOR [counter_name] IN ( [Active Temp Tables],
                                                              [Batch Requests/sec],
                                                              [Buffer cache hit ratio],
                                                              [Buffer cache hit ratio base],
                                                              [Full Scans/sec],
                                                              [Index Searches/sec],
                                                              [Latch Waits/sec],
                                                              [Logical Connections],
                                                              [Page reads/sec],
                                                              [Page writes/sec],
                                                              [Processes blocked],
                                                              [User Connections] ) )      AS b;')

END




	 --计算总时长
	 SET @totaltime=datediff(second,@starttime,getdate());

 

	--删除临时表
	IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmp_inspection'))
	BEGIN
	DROP TABLE #tmp_inspection
	END

	--记录日志
	INSERT INTO tb_SYSLOGS(Logger,Logtime,LogIP,Logname,LogTotalTime,LevelCode,Message,LogSQL)
	SELECT 'Templete',GETDATE(),'::1','sa',@totaltime,'Server_inspection','','影响行数' + CAST(@ROW AS VARCHAR(10)) + ',' + @ErrorMessage;

	END

创建job

创建作业每天上午10点调用一次存储过程proc_Server_inspection


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2018/7/10 15:16:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'sqlsever数据库服务器巡检', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'每天十点定时执行', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [循环调用存储过程proc_Server_inspection]    Script Date: 2018/7/10 15:16:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'循环调用存储过程proc_Server_inspection', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @server_ip Nvarchar(50)
----游标实现循环传入参数
DECLARE @db_name Nvarchar(50)
DECLARE inspection_cur CURSOR FOR
  SELECT server_ip,db_name FROM database_sever_link
--打开游标
OPEN inspection_cur
--获取数据，游标下移一行
FETCH NEXT FROM inspection_cur INTO @server_ip, @db_name
--检测获取数据是否成功
WHILE @@fetch_status=0
BEGIN
  --显示通过游标赋值的变量
  exec proc_Server_inspection @server_ip, @db_name --执行
  --游标继续下移
  FETCH NEXT FROM inspection_cur INTO @server_ip, @db_name
END
--关闭游标
CLOSE inspection_cur 
deallocate inspection_cur', 
		@database_name=N'MonitorCenter', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'每天10：00执行', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=1, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20180710, 
		@active_end_date=99991231, 
		@active_start_time=100000, 
		@active_end_time=235959, 
		@schedule_uid=N'4a4fbbae-3fb1-4f37-94dd-1443562fa333'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

创建需要用到的表

-------创建表用于存放巡检数据
CREATE TABLE [dbo].[Server_inspection_day]
    (
      [服务器地址] VARCHAR(20) NULL ,
      [巡检时间] [DATETIME] NOT NULL ,
      [活动临时表] [NUMERIC](27, 6) NULL ,
      [批量请求/秒] [NUMERIC](27, 6) NULL ,
      [缓冲区缓存命中数] [NUMERIC](27, 6) NULL ,
      [缓冲区缓存命中基数] [NUMERIC](27, 6) NULL ,
      [全扫描/秒] [NUMERIC](27, 6) NULL ,
      [索引扫描/秒] [NUMERIC](27, 6) NULL ,
      [锁定等待/秒] [NUMERIC](27, 6) NULL ,
      [逻辑连接] [NUMERIC](27, 6) NULL ,
      [数据页读/秒] [NUMERIC](27, 6) NULL ,
      [数据页写/秒] [NUMERIC](27, 6) NULL ,
      [阻塞进程] [NUMERIC](27, 6) NULL ,
      [用户连接数] [NUMERIC](27, 6) NULL
    )
ON  [PRIMARY];

----创建表用于存放数据库服务器地址和数据库名
CREATE TABLE [dbo].[database_sever_link]
    (
       [server_ip] VARCHAR(20),
	   [db_name] VARCHAR(20)  
    )
ON  [PRIMARY];

将需要巡检的数据库服务器维护到database_sever_link表中

INSERT INTO database_sever_link
VALUES ('10.37.53.1','master' );
INSERT INTO database_sever_link
VALUES ('10.33.36.2','master' );
INSERT INTO database_sever_link
VALUES ('122.22.33.153','本机' );

备注：
本机 db_name写本机、

创建数据库连接

SELECT  
'exec sp_addlinkedserver   '''+server_ip+''', '' '', ''SQLOLEDB '', '''+server_ip+''';'  FROM  database_sever_link
UNION
SELECT 
'exec sp_addlinkedsrvlogin '''+server_ip+''', ''false '',null, ''sa '', ''123456'';'  FROM  database_sever_link

执行脚本创建数据库连接

为什么不问问神奇的海螺呢丶
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

为什么不问问神奇的海螺呢丶

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值