DBA script for automation

　
/**************************************************************/
/* get instance name */
/* check drive space */
/* list failed sql agent jobs */
/* check sql server error log */
/* Most Recent Database Backup for Each Database - Detailed */
/**************************************************************/
/* Enabling Ole Automation Procedures */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
/*******************************************************/
--
-- Initial Script Details Instance name, Drive Letter,
-- Total space, Used space and Free space in Megabytes
--
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT @@Servername as Instance
SELECT cpu_count AS logical_cpu_count,
cpu_count / hyperthread_ratio AS physical_cpu_count,
CAST(physical_memory_kb / 1024. AS int) AS physical_memory_mb,
sqlserver_start_time
FROM sys.dm_os_sys_info;
SELECT
drive as 'Drive', TotalSize as 'Total(MB)',TotalSize - FreeSpace as 'Used(MB)' , FreeSpace as 'Free(MB)',CONVERT(VARCHAR,CONVERT(DECIMAL(15,1),100.0 * FreeSpace/ TotalSize)) + '%' as 'Percent Free' FROM #drives
ORDER BY drive
DROP TABLE #drives
GO
--
-- Detailed Physical Database files and Transaction files Information
--
SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
mf.physical_name PhysicalFileLocation,
dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO
--
-- It should be noted that the query below only lists jobs that failed the last time that it executed,
-- and only lists the last failure. If it subsequently succeeded then it is not shown at all by this script.
-- Which may or may not be what you want.
--
SELECT Job.instance_id
,SysJobs.job_id
,SysJobs.name as 'JOB_NAME'
,SysJobSteps.step_name as 'STEP_NAME'
,Job.run_status
,Job.sql_message_id
,Job.sql_severity
,Job.message
,Job.exec_date
,Job.run_duration
,Job.server
,SysJobSteps.output_file_name
FROM (SELECT Instance.instance_id
,DBSysJobHistory.job_id
,DBSysJobHistory.step_id
,DBSysJobHistory.sql_message_id
,DBSysJobHistory.sql_severity
,DBSysJobHistory.message
,(CASE DBSysJobHistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END) as run_status
,((SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 7, 2) + '/'
+ SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 1, 4) + ' '
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time AS varchar)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 1, 2) + ':'
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time AS VARCHAR)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 3, 2) + ':'
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time as varchar)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 5, 2))) AS 'exec_date'
,DBSysJobHistory.run_duration
,DBSysJobHistory.retries_attempted
,DBSysJobHistory.server
FROM msdb.dbo.sysjobhistory DBSysJobHistory
JOIN (SELECT DBSysJobHistory.job_id
,DBSysJobHistory.step_id
,MAX(DBSysJobHistory.instance_id) as instance_id
FROM msdb.dbo.sysjobhistory DBSysJobHistory
GROUP BY DBSysJobHistory.job_id
,DBSysJobHistory.step_id
) AS Instance ON DBSysJobHistory.instance_id = Instance.instance_id
WHERE DBSysJobHistory.run_status <> 1
) AS Job
JOIN msdb.dbo.sysjobs SysJobs
ON (Job.job_id = SysJobs.job_id)
JOIN msdb.dbo.sysjobsteps SysJobSteps
ON (Job.job_id = SysJobSteps.job_id AND Job.step_id = SysJobSteps.step_id)
GO
--
-- command below has
-- This procedure takes four parameters:
--1.Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
--2.Log file type: 1 or NULL = error log, 2 = SQL Agent log
--3.Search string 1: String one you want to search for
--4.Search string 2: String two you want to search for to further refine the results
--
EXEC sys.xp_readerrorlog 0,1
--
-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
--
SELECT
A.[Server],
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A
LEFT JOIN
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name