Read SQL server logs and Agent logs

I read SQL server logs and Agent logs, but I am idle recently. In order to improve the efficiency of my work in the future, I don't want to put my eyes and hands too tired. So I wrote the following script to solve the problem:

Reading SQL Server logs and Agent logs is idle recently. To improve efficiency in future work, I don't want to put my eyes and hands too tired. So I wrote the following script to solve the problem.

I am idle recently. I don't want to put my eyes and hands too tired to improve my work efficiency in the future. So I wrote the following script to free myself.

--- View the remaining space of each disk (MB)
Exec master. dbo. xp_fixeddrives
-- Or
Declare @ Fixed_tb table (Drive_NO char (1), Remainder_M bigint)
Insert into @ Fixed_tb exec master. dbo. xp_fixeddrives
Select Drive_NO 'Drive letter ', Remainder_M 'remaining m', cast (Remainder_M/1024) + 0.001 * (Remainder_M % 1024) as dec )) 'remaining G' from @ Fixed_tb
GO

----- SQL SERVER Log

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

Insert into @ tmp
EXEC master. dbo. xp_readerrorlog 0, 1, NULL, N 'desc' --- read SQL Server logs

Select * from @ tmp where 1 = 1
/*
There are a total of 7 parameters:

1. archive number
2. Log Type (1 is SQL Server log, 2 is SQL Agent log)
3. query the contained strings
4. query the contained strings
5. LogDate Start Time
6. Sort the results by LogDate (it can be in descending order "Desc" Or Ascending Order "Asc ")
7. Sort the results by LogDate (it can be in descending order "Desc" Or Ascending Order "Asc ")

When you enter 5th and 6th parameters, the query speed is sometimes very slow and the CPU usage is 100% in seconds or milliseconds.

*/


-- Job activity monitor details

SELECT c. job_id, a. name, case when a. enabled = 1 then 'is 'else' no 'end' enabled ',
A. date_created 'creation time', a. date_modified 'modification time ',
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 'last runtime ',
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 'Next runningtime ',
Case when substring (B. last_outcome_message, 1, CHARINDEX ('. ', B. last_outcome_message) is NULL then
'Unknown 'else substring (B. last_outcome_message, 1, CHARINDEX ('. ', B. last_outcome_message) end 'last run result'

FROM
[Msdb]. [dbo]. [sysjobs_view]
Join [msdb]. [dbo]. [sysjobservers] B
On a. job_id = B. job_id
Join [msdb]. [dbo]. [sysjobschedules] c
On a. job_id = c. job_id
Where a. category_id = 0 or a. category_id = 3

---- Detailed operation steps and results of each job

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 'runtime ',
Case when B. run_status = 1 then 'success 'else' failure 'end' status'

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)