1

SQL Server数据库日常检查 - kingster

 1 year ago
source link: https://www.cnblogs.com/kingster/p/17140585.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

SQL Server数据库日常检查

1.1代码检查从昨天到现在，SQL代理Job有没有运行失败的，会把运行失败的Job名字，步骤，运行时间，错误等级，错误原因罗列出来，方便查看。

----1.1  Check Job Fail List From Last Day To Now
SELECT  j.[name],  
        h.step_id,  
        h.step_name,  
        h.run_date,  
        h.run_time,  
        h.sql_severity,  
        h.message,   
        h.server  
FROM    msdb.dbo.sysjobhistory h  
        INNER JOIN msdb.dbo.sysjobs j  
            ON h.job_id = j.job_id  
        INNER JOIN msdb.dbo.sysjobsteps s  
            ON j.job_id = s.job_id 
                AND h.step_id = s.step_id  
WHERE    h.run_status = 0 -- Failure  
         AND h.run_date > CONVERT(int,CONVERT(varchar(10), DATEADD(DAY, -1, GETDATE()), 112))
ORDER BY h.instance_id DESC;

1.2 检查两天内，运行时间超过30分钟的Job，并按执行时间长短排序，时间2天和运行时间30分钟，都是可以调整的，可以调整为自己需要的检查范围。代码会把执行Job的名称，运行时间，平均执行时间列出来。看是否有突然变化的运行情况。

----1.2  Check Jobs With Long Duration:30minutes(can modify to other value) From Last 2 Day To Now
SELECT sj.name
   , sja.start_execution_date,DATEDIFF (minute ,sja.start_execution_date,sja.stop_execution_date ) AS ExecutedMin,ja.AvgRuntimeOnSucceed/60 as AvgRuntimeOnSucceedMin
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id INNER
join
(
    SELECT job_id,
    AVG
    ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)
    +
    NULLIF(0,STDEV
    ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)) AS 'AvgRuntimeOnSucceed'
     FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0 AND run_status = 1
    GROUP BY job_id) ja 
    ON sj.job_id = ja.job_id
WHERE sja.start_execution_date IS NOT NULL --作业有开始
   AND sja.stop_execution_date IS not NULL --作业结束
   AND sja.start_execution_date>DATEADD(DAY,-2,GETDATE()) --作业2天内开始
and DATEDIFF (minute ,sja.start_execution_date,sja.stop_execution_date )>30
order by ExecutedMin desc

1.3 检查数据库错误日志，默认读取的是当前log，筛选的是Error开头的错误日志，可以根据需要筛选其他关键字。

----1.3  Check SQL Error Log
DROP TABLE IF EXISTS #errorLog;  -- this is new syntax in SQL 2016 and later


CREATE TABLE #errorLog (LogDate DATETIME, ProcessInfo VARCHAR(64), [Text] VARCHAR(MAX));


INSERT INTO #errorLog
EXEC sp_readerrorlog 


SELECT * 
FROM #errorLog a
WHERE EXISTS (SELECT * 
              FROM #errorLog b
              WHERE [Text] like 'Error:%'
                AND a.LogDate = b.LogDate
                AND a.ProcessInfo = b.ProcessInfo)

1.4 检查含有数据库文件的磁盘的空间大小，可以看到剩余百分比，实际大小，使用大小等。

----1.4  Check HDD Free Space
select distinct 
convert(varchar(512), b.volume_mount_point) as [volume_mount_point]
, convert(varchar(512), b.logical_volume_name) as [logical_volume_name]
, convert(decimal(18,1), round(((convert(float, b.available_bytes) / convert(float, b.total_bytes)) * 100),1)) as [Drive_Free_Percent]
, convert(bigint, round(((b.available_bytes / 1024.0)/1024.0),0)) as [Drive_Free_MB]
, convert(bigint, round(((b.total_bytes / 1024.0)/1024.0),0)) as [Drive_Total_MB]
, convert(bigint, round((((b.total_bytes - b.available_bytes) / 1024.0)/1024.0),0)) as [Drive_Used_MB]
from sys.master_files as [a]
CROSS APPLY sys.dm_os_volume_stats(a.database_id, a.[file_id]) as [b]
order by volume_mount_point

1.5 检查数据库数据文件和日志文件的大小，默认排除了id为4以下的系统数据库，可以根据需要调整，只观察自己需要的数据库。

----1.5  Check DB Data And Log Size
select distinct db_name(a.database_id) as [DatabaseName],database_id
, a.name as [Logical_Name]
, convert(decimal(28,2), round(((a.size * 8.0) / 1024.00),2)) as [SizeMB] 
from sys.master_files as [a]
where database_id>4
order by  DatabaseName,Logical_Name

1.1代码检查从昨天到现在，SQL代理Job有没有运行失败的，会把运行失败的Job名字，步骤，运行时间，错误等级，错误原因罗列出来，方便查看。

----1.1  Check Job Fail List From Last Day To Now
SELECT  j.[name],  
        h.step_id,  
        h.step_name,  
        h.run_date,  
        h.run_time,  
        h.sql_severity,  
        h.message,   
        h.server  
FROM    msdb.dbo.sysjobhistory h  
        INNER JOIN msdb.dbo.sysjobs j  
            ON h.job_id = j.job_id  
        INNER JOIN msdb.dbo.sysjobsteps s  
            ON j.job_id = s.job_id 
                AND h.step_id = s.step_id  
WHERE    h.run_status = 0 -- Failure  
         AND h.run_date > CONVERT(int,CONVERT(varchar(10), DATEADD(DAY, -1, GETDATE()), 112))
ORDER BY h.instance_id DESC;

1.2 检查两天内，运行时间超过30分钟的Job，并按执行时间长短排序，时间2天和运行时间30分钟，都是可以调整的，可以调整为自己需要的检查范围。代码会把执行Job的名称，运行时间，平均执行时间列出来。看是否有突然变化的运行情况。

----1.2  Check Jobs With Long Duration:30minutes(can modify to other value) From Last 2 Day To Now
SELECT sj.name
   , sja.start_execution_date,DATEDIFF (minute ,sja.start_execution_date,sja.stop_execution_date ) AS ExecutedMin,ja.AvgRuntimeOnSucceed/60 as AvgRuntimeOnSucceedMin
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id INNER
join
(
    SELECT job_id,
    AVG
    ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)
    +
    NULLIF(0,STDEV
    ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)) AS 'AvgRuntimeOnSucceed'
     FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0 AND run_status = 1
    GROUP BY job_id) ja 
    ON sj.job_id = ja.job_id
WHERE sja.start_execution_date IS NOT NULL --作业有开始
   AND sja.stop_execution_date IS not NULL --作业结束
   AND sja.start_execution_date>DATEADD(DAY,-2,GETDATE()) --作业2天内开始
and DATEDIFF (minute ,sja.start_execution_date,sja.stop_execution_date )>30
order by ExecutedMin desc

1.3 检查数据库错误日志，默认读取的是当前log，筛选的是Error开头的错误日志，可以根据需要筛选其他关键字。

----1.3  Check SQL Error Log
DROP TABLE IF EXISTS #errorLog;  -- this is new syntax in SQL 2016 and later


CREATE TABLE #errorLog (LogDate DATETIME, ProcessInfo VARCHAR(64), [Text] VARCHAR(MAX));


INSERT INTO #errorLog
EXEC sp_readerrorlog 


SELECT * 
FROM #errorLog a
WHERE EXISTS (SELECT * 
              FROM #errorLog b
              WHERE [Text] like 'Error:%'
                AND a.LogDate = b.LogDate
                AND a.ProcessInfo = b.ProcessInfo)

1.4 检查含有数据库文件的磁盘的空间大小，可以看到剩余百分比，实际大小，使用大小等。

----1.4  Check HDD Free Space
select distinct 
convert(varchar(512), b.volume_mount_point) as [volume_mount_point]
, convert(varchar(512), b.logical_volume_name) as [logical_volume_name]
, convert(decimal(18,1), round(((convert(float, b.available_bytes) / convert(float, b.total_bytes)) * 100),1)) as [Drive_Free_Percent]
, convert(bigint, round(((b.available_bytes / 1024.0)/1024.0),0)) as [Drive_Free_MB]
, convert(bigint, round(((b.total_bytes / 1024.0)/1024.0),0)) as [Drive_Total_MB]
, convert(bigint, round((((b.total_bytes - b.available_bytes) / 1024.0)/1024.0),0)) as [Drive_Used_MB]
from sys.master_files as [a]
CROSS APPLY sys.dm_os_volume_stats(a.database_id, a.[file_id]) as [b]
order by volume_mount_point

1.5 检查数据库数据文件和日志文件的大小，默认排除了id为4以下的系统数据库，可以根据需要调整，只观察自己需要的数据库。

----1.5  Check DB Data And Log Size
select distinct db_name(a.database_id) as [DatabaseName],database_id
, a.name as [Logical_Name]
, convert(decimal(28,2), round(((a.size * 8.0) / 1024.00),2)) as [SizeMB] 
from sys.master_files as [a]
where database_id>4
order by  DatabaseName,Logical_Name

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK