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.
No comments:
Post a Comment