Jobs Running on Server

To find out jobs running on a server through T-Sql

DECLARE @job_owner VARCHAR(100);

SELECT @job_owner = SUSER_SNAME()

IF EXISTS
(
    SELECT 1    FROM tempdb.dbo.sysobjects
    WHERE ID = OBJECT_ID(N'tempdb..#JobStats')
)
BEGIN
    DROP TABLE #JobStats
END


CREATE TABLE
    #JobStats
    (
        job_id UNIQUEIDENTIFIER
    ,    last_run_date INT
    ,    last_run_time INT
    ,    next_run_date INT
    ,    next_run_time INT
    ,    next_run_schedule_id  INT
    ,    requested_to_run INT
    ,    request_source INT
    ,    request_source_id VARCHAR(100)
    ,    running INT
    ,    current_step INT
    ,    current_retry_attempt INT
    ,    job_state INT
    )  


 INSERT INTO #JobStats
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner
          
SELECT
    sysjobs.name
,    JobStats.current_step
,    JobStats.running
,    JobStats.*
FROM
    #JobStats JobStats
    JOIN MSDB..sysjobs sysjobs
    ON JobStats.job_id = sysjobs.job_id

OR 

SELECT

SYSJOBS.Name

, SYSJOBS.Job_Id

, SYSPROCESSES.HostName

, SYSPROCESSES.LogiName

, *

FROM

MSDB.dbo.SYSJOBS

JOIN

MASTER.dbo.SYSPROCESSES

ON

SUBSTRING(SYSPROCESSES.PROGRAM_NAME,30,34)

=MASTER.dbo.fn_varbintohexstr ( SYSJOBS.job_id)

AND

LEFT(PROGRAM_NAME,28) ='SQLAgent - TSQL JobStep (Job'

Delete Duplicates from a Table

WITH DupCTE(Col1,Col2, Ranking)

AS

(

SELECT

Col1

, Col2

, DENSE_RANK() OVER

(PARTITION BY ID ORDER BY NEWID() ASC) AS Ranking

FROM TableName

)

DELETE FROM DupCTE

WHERE Ranking > 1;

GO

Few days back I had a table which had duplicates on the Id columns also, so i had to flip a bit to write a shortest code i can write to delete or remove duplicates from the table.

Below I will demonstrate it by create a temp table, inserting it with some duplicate data and then remove the dups using CTE( Common Table Expressions)

1. Create Table with duplicate data:

--Create Table

CREATE TABLE #Dups

(

Id INT

, Name VARCHAR(50)

);

GO

--Insert data with Duplicates

INSERT INTO #Dups VALUES ( 1, 'Rahul');

INSERT INTO #Dups VALUES ( 1, 'Rahul');

INSERT INTO #Dups VALUES ( 2, 'Divya');

INSERT INTO #Dups VALUES ( 2, 'Divya');

INSERT INTO #Dups VALUES ( 3, 'Jason');

GO

2. CTE to remove Dups:

WITH DupCTE(Id, Name, Ranking)

AS

(

SELECT

ID

, Name

, DENSE_RANK() OVER(PARTITION BY ID ORDER BY NEWID() ASC) AS Ranking

FROM #Dups

)

DELETE FROM DupCTE

WHERE Ranking > 1;

GO

You can download the complete sample code from here.

Cheers!!

Identify Orphan Users on an instance

Many a time when we move our database to new instance, rebuilding server or setting up a backup we need to synchronize user logins with the server

We can use below script to find out the Orphan Users on the Server

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_ShowOrphanUsers]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_ShowOrphanUsers]
GO

CREATE PROC dbo.usp_ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
([Database Name] sysname ,
[Orphaned User] sysname ,
[Type User] sysname )

SET NOCOUNT ON

DECLARE @DBName sysname, @Qry NVARCHAR(4000)

SET @Qry = ''
SET @DBName = ''

WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
('master', 'model', 'tempdb', 'msdb', 'distribution', 'pubs', 'northwind', 'dba_database')
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)

IF @DBName IS NULL BREAK

SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],
[Type User] =
CASE isntuser
WHEN ''0'' THEN ''SQL User''
WHEN ''1'' THEN ''NT User''
ELSE ''Not Available''
END
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name NOT IN (''INFORMATION_SCHEMA'', ''sys'', ''guest'', ''dbo'', ''system_function_schema'')
AND NOT EXISTS (SELECT 1 FROM master..syslogins sl WHERE su.sid = sl.sid)'
INSERT INTO #Results
EXEC master..sp_executesql @Qry
END
SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
IF @@ROWCOUNT = 0
PRINT 'No orphaned users exist in this server.'
END
GO

--Execute the proc
EXEC [dbo].[usp_ShowOrphanUsers]

"This script is taken from SQL Server Central"