最近閑的沒事，為了以後的工作提高效率，其實是不想讓自己的眼睛和手 太累。于是寫了如下腳本 來解放自己。
---檢視每個磁盤剩餘空間大小（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. 日志類型(1為SQL Server日志,2為SQL 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)
