[MSSQL]磁盘空间&SQL日志&作业执行情况

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/david_520042/article/details/49356637

---查看每个磁盘剩余空间大小(M)
Exec master.dbo.xp_fixeddrives 
--或者
declare @Fixed_tb table(Drive_NO char(1),Remainder_M bigint)
INSERT INTO @Fixed_tb exec master.dbo.xp_fixeddrives
select Drive_NO '驱动盘符',Remainder_M'剩余M',cast(((Remainder_M/1024)+0.001*(Remainder_M%1024))as dec(18,2))'剩余G' from @Fixed_tb
GO


-----SQL SERVER 日志 

declare @tmp table  (LogDate datetime,ProcessInfo varchar(32),Text nvarchar(max))

insert into @tmp
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc'---读取SQL Server 日志

select * from @tmp where 1=1
/*
 一共有7个参数: 

1. 存档编号
2. 日志类型(1SQL Server日志,2SQL Agent日志)
3. 查询包含的字符串
4. 查询包含的字符串
5. LogDate开始时间
6. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc")
7. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc") 

在输入第5和第6个参数的时候,使用时间里包含有秒、毫秒时候,有时候查询速度非常慢,而且导致CPU占用率为100%。

*/



--作业活动监视器 (不显示成功的JOB)详细内容
 SELECT c.job_id,a.name,case when a.enabled =1 then '是' else '否' end '是否启用',
a.date_created '创建时间',a.date_modified '修改时间',
d.run_date as '最近运行日期',
 case when substring(b.last_outcome_message,1,CHARINDEX('。', b.last_outcome_message)) is NULL then
 '未知' else substring(b.last_outcome_message,1,CHARINDEX('。', b.last_outcome_message)) end '上次运行结果',
 d.message,
left(b.last_run_date,4)+'/'+SUBSTRING(convert(varchar(8),b.last_run_date),5,2)+'/'+right(b.last_run_date,2)+' '+ 
case when b.last_run_time=0 then '0:00:00' 
 when LEN(b.last_run_time)=3 then '0:0'+SUBSTRING(convert(varchar(6),b.last_run_time),1,1)+':'+RIGHT(b.last_run_time,2)
 when LEN(b.last_run_time)=4 then '0:'+LEFT(b.last_run_time,2)+':'+RIGHT(b.last_run_time,2)
 when len(b.last_run_time)=5 then left(b.last_run_time,1)+':'+SUBSTRING(convert(varchar(6),b.last_run_time),2,2)+':'+right(b.last_run_time,2)
 else left(b.last_run_time,2)+':'+SUBSTRING(convert(varchar(6),b.last_run_time),3,2)+':'+right(b.last_run_time,2)end'上次运行时间',
left(c.next_run_date,4)+'/'+SUBSTRING(convert(varchar(8),c.next_run_date),5,2)+'/'+right(c.next_run_date,2)+' '+ 
case when c.next_run_time=0 then '0:00:00' 
 when LEN(c.next_run_time)=3 then '0:0'+SUBSTRING(convert(varchar(6),c.next_run_time),1,1)+':'+RIGHT(c.next_run_time,2)
 when LEN(c.next_run_time)=4 then '0:'+LEFT(c.next_run_time,2)+':'+RIGHT(c.next_run_time,2)
 when len(c.next_run_time)=5 then left(c.next_run_time,1)+':'+SUBSTRING(convert(varchar(6),c.next_run_time),2,2)+':'+right(c.next_run_time,2)
 else left(c.next_run_time,2)+':'+SUBSTRING(convert(varchar(6),c.next_run_time),3,2)+':'+right(c.next_run_time,2)end '下次运行时间'
FROM
[msdb].[dbo].[sysjobs_view] a 
join [msdb].[dbo].[sysjobservers] b
on a.job_id =b.job_id 
join [msdb].[dbo].[sysjobschedules] c
on a.job_id =c.job_id 
join (select job_id,max(run_date) run_date,message 
from [msdb].[dbo].[sysjobhistory] 
where run_status<>1 and sql_message_id>0 group by job_id,message) d
on b.job_id =d.job_id and b.last_run_date=d.run_date 
where a.enabled=1 and (a.category_id =0 or a.category_id =3)  
----每个作业详细运行步骤及结果

select a.name ,a.description,a.date_created,a.date_modified,
  b.message,  
  left(b.run_date,4)+'/'+SUBSTRING(convert(varchar(8),b.run_date),5,2)+'/'+right(b.run_date,2)+'  '+
   case when b.run_time=0 then '0:00:00' 
    when LEN(b.run_time)=3 then '0:0'+SUBSTRING(convert(varchar(6),b.run_time),1,1)+':'+RIGHT(b.run_time,2)
    when LEN(b.run_time)=4 then '0:'+LEFT(b.run_time,2)+':'+RIGHT(b.run_time,2)
    when len(b.run_time)=5 then left(b.run_time,1)+':'+SUBSTRING(convert(varchar(6),b.run_time),2,2)+':'+right(b.run_time,2)
    else left(b.run_time,2)+':'+SUBSTRING(convert(varchar(6),b.run_time),3,2)+':'+right(b.run_time,2)end'运行时间',
    case when b.run_status=1 then '成功' else '失败' end '状态'

 FROM [msdb].[dbo].[sysjobs_view] a ,[msdb].[dbo].[sysjobhistory] b
 where a.job_id =b.job_id and (a.category_id =0 or a.category_id =3)

展开阅读全文

没有更多推荐了,返回首页