SQL SERVER DBA Monitoring Scripts

SQL SERVER DBA Monitoring Scripts

General SQL SERVER DBA Monitoring Scripts
Tsql Script to know Failed JOBS & DISK SPACE MONITORING SCRIPT

 use msdb      
 go      
 select  j.[name]as 'Failed Job' from dbo.sysjobs as j (NoLOCK)        
 inner join dbo.sysjobservers as s (NOLOCK)
 on s.job_id = j.job_id
 where enabled=1 and s.last_run_outcome = 0
 order by j.[name]        
 go
 exec  master..xp_fixeddrives

********************************************************************************

Tsql script to get Sql job owner info

select name as Job_Name, SUSER_SNAME(owner_sid) as Job_Owner
from sysjobs
*********************************************************************************
TSQL SCRIPT TO KNOW SQL JOBS ENABLED OR DISABLED

SELECT job_id, [name] FROM msdb.dbo.sysjobs

**********************************************************************************

List of all the jobs currently running on server

 SELECT server as ServerName,
 database_name as DBName,
 name as Job_Name,enabled,
 description as JobDescription,
 step_name,command
 FROM msdb.dbo.sysjobs job JOIN
 msdb.dbo.sysjobsteps steps      
 ON job.job_id = steps.job_id
 WHERE job.enabled = 1 -- remove this if you wish to return all jobs

*********************************************************************************

Tsql script to get Job Name, Category, Job Description

SELECT  sysjobs.name 'Job Name',
        syscategories.name 'Category',
        CASE [description]
          WHEN 'No Description available.' THEN ''
          ELSE [description]
        END AS 'Description'
FROM    msdb.dbo.sysjobs
        INNER JOIN msdb.dbo.syscategories
ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
WHERE   syscategories.name <> 'Report Server'
ORDER BY sysjobs.name

*********************************************************************************

TSQL SCRIPT TO GET ALL DATABASE PROPERTIES

select name, compatibility_level,
user_access_desc, state_desc,
recovery_model_desc,log_reuse_wait_desc
from sys.databases

TSQL SCRIPT TO GET LINKED SERVER INFORMATION IN SQL SERVER

--get a list of linked servers
-- with remote logins
SELECT s.srvname as linked_servername,
 u.rmtloginame as lnk_svr_loginname,
 '----' as sep, s.* , u.*
FROM sysservers s, sysoledbusers u
where srvid = rmtsrvid
order by srvname
OR
select s.srvname as linked_servername,
u.rmtloginame as lnk_svr_loginname,
u.rmtloginame as lnk_svr_loginname
FROM sysservers s, sysoledbusers u
where srvid = rmtsrvid
order by srvname
**************************************************************************************
Find Service Pack patch status, CPU, memory and more
Here's a quick query you can run across all your servers (2005+) to find a wealth of information like service pack, edition, number of CPUs and RAM. Even more is available if you want to add additional SERVERPROPERTY attributes or fields from one of the DMVs.
SELECT SERVERPROPERTY('ServerName') AS [SQLServer],
               --@@microsoftversion/0x01000000 AS [MajorVersion],
               SERVERPROPERTY('ProductVersion') AS [VersionBuild],
               SERVERPROPERTY('ProductLevel') AS [Product],
               SERVERPROPERTY ('Edition') AS [Edition],
               --SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly],
               --SERVERPROPERTY('IsClustered') AS [IsClustered],
               [cpu_count] AS [CPUs],
               [physical_memory_in_bytes]/1048576 AS [RAM (MB)]
FROM    [sys].[dm_os_sys_info]

Note, if you're trying to run this against a 2000 instance, just eliminate the last 3 lines and final comma and run the SELECT portion only.