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

秒速五厘米 2022-05-20 01:55 101阅读 0赞

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

--------------------

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

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

--------------------

执行脚本创建数据库连接

[70]: /images/20220520/4ebc1968f8b64c819ed7f11661b40f8d.png