Thursday 4 September 2014

Script to get the all jobs info in a server.


SET NOCOUNT ON

--Checking for SQL Server verion

IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8

BEGIN

---This is for SQL 2k5 and SQL2k8 servers

SET NOCOUNT ON

SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,

j.name AS job_name,

CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,

CASE jh.run_status WHEN 0 THEN 'Error Failed'

                WHEN 1 THEN 'Succeeded'

                WHEN 2 THEN 'Retry'

                WHEN 3 THEN 'Cancelled'

                WHEN 4 THEN 'In Progress' ELSE

                'Status Unknown' END AS 'last_run_status',

ja.run_requested_date as last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,

ja.next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message) AS step_description

FROM

(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status

END

ELSE

BEGIN

--This is for SQL2k servers

SET NOCOUNT ON

DECLARE @SQL VARCHAR(5000)

--Getting information from sp_help_job to a temp table

SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,

CASE last_run_outcome WHEN 0 THEN ''Error Failed''

                WHEN 1 THEN ''Succeeded''

                WHEN 2 THEN ''Retry''

                WHEN 3 THEN ''Cancelled''

                WHEN 4 THEN ''In Progress'' ELSE

                ''Status Unknown'' END AS  last_run_status,

CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,

CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time, 

CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date, 

CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,

last_run_date AS lrd, last_run_time AS lrt

INTO ##jobdetails

FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'

exec (@SQL)

--Merging run date & time format, adding run duration and adding step description

select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status,

CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,

CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message) AS step_description

from (##jobdetails jd  LEFT JOIN  msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null

order by jd.job_name,jd.job_status

--dropping the temp table

drop table ###jobdetails

END

--- Note : we got the script from below URL ----
--- Read more: http://sql-articles.com/scripts/script-to-find-sql-job-activity-details/#ixzz24IQ3asc2---

Process to delete the logins which are not mapping to any database in sql server 2000

1   --- 1)      Run the below script
             ----Sp_helplogins
----a)      Copy the first table output in excel sheet and then apply filter on Auser with “no”.
2    ----2)      And then run the below script

select @@SERVERNAME
,sl.name
,isnull(DB_Roles.Roles,'Public')
from syslogins sl
left join
(
select 'sysadmin' as Roles
union all
select 'securityadmin'
union all
select 'serveradmin'
union all
select 'setupadmin'
union all
select 'processadmin'
union all
select 'diskadmin'
union all
select 'dbcreator'
union all
select 'bulkadmin'
union all
select 'No serverRole'
union all
select 'public'
) DB_Roles
on
Case When sl.sysadmin=1 and DB_Roles.Roles='sysadmin' then 'sysadmin'
      When sl.securityadmin=1 and DB_Roles.Roles='securityadmin' then 'securityadmin'
      When sl.serveradmin=1 and DB_Roles.Roles='serveradmin' then 'serveradmin'
      When sl.setupadmin=1 and DB_Roles.Roles='setupadmin' then 'setupadmin'
      When sl.processadmin=1 and DB_Roles.Roles='processadmin' then 'processadmin'
      When sl.diskadmin=1 and DB_Roles.Roles='diskadmin' then 'diskadmin'
      When sl.dbcreator=1 and DB_Roles.Roles='dbcreator' then 'dbcreator' 
      When sl.bulkadmin=1 and DB_Roles.Roles='bulkadmin' then 'bulkadmin'
      else null end=DB_Roles.Roles
     
order by 2

a)      Copy the result into excel sheet and apply the filter on third column with “sysadmin”
b)      Compare the both excel sheets and delete the logins in first excel sheet which are in second excel sheet.
c)       After that create a test database on the server with droplogin table with login_id as column name and entre the logins name which are in first excel sheet into the droplogin table.
d)      And run the below script on test database to delete the logins which are not map to any database.

declare @cursor cursor, @login_id sysname
set @cursor = cursor for (select login_id from droplogin)
open @cursor
while 1=1
 begin
 fetch next from @cursor into @login_id
 if @@FETCH_STATUS <> 0
  break

 EXEC ('sp_droplogin ' + @login_id)
 
 End




Note: Maintain the logins list which are we delete and perform the full backup on master database.

SP to find the Lead Blocker


With help of the below SP we can find the Lead blocker details. it will be useful to resolve the blocking issue quickly.

----Text 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

CREATE PROCEDURE [dbo].[sp_leadblocker] as 
begin 
set nocount on 

IF EXISTS (SELECT * FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked)) BEGIN 
DECLARE @spid int 

SELECT @spid=p1.spid -- Get the _last_ prime offender 
FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked) 
WHERE p1.blocked=0 

SELECT p1.spid, 
p1.status, 
loginame=LEFT(p1.loginame,20), 
hostname=substring(p1.hostname,1,20), 
blk=CONVERT(char(3),p1.blocked), 
db=LEFT(db_name(p1.dbid),10), 
p1.cmd, 
p1.waittype 
FROM master..sysprocesses p1 
JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked) 
WHERE p1.blocked=0 
select @spid -- Return the last lead blocker 
END ELSE BEGIN 
PRINT 'No processes are currently blocking others.' 
select GetDate() 
END 
end

------ Note : we got this code from the web, Please test it and then use on Production servers. ------