Job History Collection

--TABLE
 USE [DBACollection]
GO

/****** Object:  Table [dbo].[JobHistoryCollection]    Script Date: 11/26/2013 10:30:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[JobHistoryCollection](
    [ServerName] [nvarchar](128) NULL,
    [JobName] [sysname] NULL,
    [Enabled] [tinyint] NULL,
    [CurrentStatus] [varchar](9) NULL,
    [CurrentStepNbr] [int] NULL,
    [LastRunTime] [datetime] NULL,
    [LastRunOutcome] [varchar](11) NULL,
    [LastRunDuration] [int] NULL,
    [CollectionTime] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[JobHistoryCollection] ADD  CONSTRAINT [DF_JobHistoryCollection_CollectionTime]  DEFAULT (getdate()) FOR [CollectionTime]
GO


--PROC
USE [DBACollection]
GO
/*

exec pr_JobHistoryCollection

select * from JobHistoryCollection order by collectiontime desc


select jobname, COUNT(*) as cc, MAX(lastrunduration) mx,Min(lastrunduration) mn,Avg(lastrunduration) av
from JobHistoryCollection
where collectiontime < GETDATE() and collectiontime > DATEADD(dd, -7, GETDATE())
group by jobname
order by mx desc

*/
alter proc pr_JobHistoryCollection
as
begin

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

if(object_id('tempdb..#tmp')>1)
    drop table #tmp

create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL,    running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);

insert into #tmp  EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLA4SW1';


UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time    = right ('000000' + next_run_time, 6);

--select * from #tmp


insert into JobHistoryCollection
(ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration)

select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from
(
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else  NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
h.run_duration
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where x.running != 1
) a
except
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from JobHistoryCollection



end
--JOB
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'AUDIT - JobHistoryCollection',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @delete_level=0,
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'AUDIT - JobHistoryCollection', @server_name = @@servername
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AUDIT - JobHistoryCollection', @step_name=N'exec sp',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_fail_action=2,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec sp_JobHistoryCollection',
        @database_name=N'DBACollection',
        @flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AUDIT - JobHistoryCollection',
        @enabled=1,
        @start_step_id=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'',
        @notify_netsend_operator_name=N'',
        @notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'AUDIT - JobHistoryCollection', @name=N'Every 15 min',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=15,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20131126,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

Retrieve job status

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_job_status]    Script Date: 09/25/2013 11:49:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec [sp_job_status] --EVERYTHING
exec [sp_job_status] @jobstatus = 0 --FAIL
exec [sp_job_status] @jobname = 'CSOD Service Queue Collector'
exec [sp_job_status] @jobname = 'CSOD Service Queue Collector' , @jobstatus = 1 --Success
*/
ALTER procedure [dbo].[sp_job_status]
(
    @isrunning bit = 0 ,
    @jobname nvarchar(255) = null ,
    @jobstatus  int = null
)
as
begin

/*
@jobstatus
WHEN NULL THEN 'ALL'
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
*/

    --set @jobname = 'CSOD Service Queue Collector'
    --set @isrunning = 0 -- 0:all 1:running
   
    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]'))
        DROP TABLE [tempdb].[dbo].[Temp1]


    CREATE TABLE [tempdb].[dbo].[Temp1]
    (
    job_id uniqueidentifier NOT NULL,
    last_run_date nvarchar (20) NOT NULL,
    last_run_time nvarchar (20) NOT NULL,
    next_run_date nvarchar (20) NOT NULL,
    next_run_time nvarchar (20) NOT NULL,
    next_run_schedule_id INT NOT NULL,
    requested_to_run INT NOT NULL,
    request_source INT NOT NULL,
    request_source_id sysname
    COLLATE database_default NULL,
    running INT NOT NULL,
    current_step INT NOT NULL,
    current_retry_attempt INT NOT NULL,
    job_state INT NOT NULL)
   
    DECLARE @job_owner   sysname
    DECLARE @is_sysadmin   INT
    SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
    SET @job_owner   = suser_sname ()

    INSERT INTO [tempdb].[dbo].[Temp1]
    --EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
    UPDATE [tempdb].[dbo].[Temp1]
    SET last_run_time    = right ('000000' + last_run_time, 6),
    next_run_time    = right ('000000' + next_run_time, 6);
    -----
    SELECT j.name AS JobName,
    j.enabled AS Enabled,
    CASE x.running
    WHEN 1
    THEN
    'Running'
    ELSE
    CASE h.run_status
    WHEN 2 THEN 'Inactive'
    WHEN 4 THEN 'Inactive'
    ELSE 'Completed'
    END
    END
    AS CurrentStatus,
    coalesce (x.current_step, 0) AS CurrentStepNbr,
    CASE
    WHEN x.last_run_date > 0
    THEN
    convert (datetime,
    substring (x.last_run_date, 1, 4)
    + '-'
    + substring (x.last_run_date, 5, 2)
    + '-'
    + substring (x.last_run_date, 7, 2)
    + ' '
    + substring (x.last_run_time, 1, 2)
    + ':'
    + substring (x.last_run_time, 3, 2)
    + ':'
    + substring (x.last_run_time, 5, 2)
    + '.000',
    121
    )
    ELSE
    NULL
    END
    AS LastRunTime,
    CASE h.run_status
    WHEN 0 THEN 'Fail'
    WHEN 1 THEN 'Success'
    WHEN 2 THEN 'Retry'
    WHEN 3 THEN 'Cancel'
    WHEN 4 THEN 'In progress'
    END
    AS LastRunOutcome,
    CASE
    WHEN h.run_duration > 0
    THEN
    (h.run_duration / 1000000) * (3600 * 24)
    + (h.run_duration / 10000 % 100) * 3600
    + (h.run_duration / 100 % 100) * 60
    + (h.run_duration % 100)
    ELSE
    NULL
    END
    AS LastRunDuration
    FROM          [tempdb].[dbo].[Temp1] x
    LEFT JOIN
    msdb.dbo.sysjobs j
    ON x.job_id = j.job_id
    LEFT OUTER JOIN
    msdb.dbo.syscategories c
    ON j.category_id = c.category_id
    LEFT OUTER JOIN
    msdb.dbo.sysjobhistory h
    ON     x.job_id = h.job_id
    AND x.last_run_date = h.run_date
    AND x.last_run_time = h.run_time
    AND h.step_id = 0
    where (@jobstatus is null or h.run_status = @jobstatus)
    and (@jobname is null or j.name = @jobname )
    --x.running = 1 -- to see running jobs

   
       
end

T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives

T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives

http://www.sanssql.com/2011/02/t-sql-queries-to-find-sql-server.html

Identify when a SQL Server database was restored, the source and backup date

Database Mirroring between a Server in Domain and Server outside domain (workgroup)

http://madebysql.blogspot.com/2011/06/database-mirroring-between-server-in.html

Script User and Role Object Permissions in SQL Server

DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'CSODMGMT\SQLREOnly'
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END

/*
credit : http://www.sql-server-performance.com/2002/object-permission-scripts/2/
*/

sp_kill_user

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_kill_user]    Script Date: 04/24/2013 09:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_kill_user 'dw_user'
*/
ALTER proc [dbo].[sp_kill_user]
(
    @username nvarchar(1000) = 'dw_user'
)
as
begin

    SET NOCOUNT ON

    DECLARE @spid INT,
        @cnt INT,
        @sql VARCHAR(255)

    SELECT @spid = MIN(spid), @cnt = COUNT(*)
        FROM master..sysprocesses
        WHERE loginame = @username
        AND spid != @@SPID

    PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
   
    WHILE @spid IS NOT NULL
    BEGIN
        PRINT 'About to KILL '+RTRIM(@spid) 
        SET @sql = 'KILL '+RTRIM(@spid)
        EXEC(@sql) 
        SELECT @spid = MIN(spid), @cnt = COUNT(*)
            FROM master..sysprocesses
            WHERE loginame = @username
            AND spid != @@SPID 
        PRINT RTRIM(@cnt)+' processes remain.'
    END
END

SQL Server DB file Size

http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/

Truncate Mirrored Database Log File

http://sqlserverimpressions.blogspot.com/2009/02/truncate-mirrored-database-log-file.html

Table Partitioning on a varchar field

exec sp_who3
exec sp_whoisactive

drop partition scheme CoreCollectionPartitionScheme
drop PARTITION FUNCTION [SwimlaneRangePartitionFunction]


CREATE PARTITION FUNCTION [SwimlaneRangePartitionFunction] (nvarchar(100))
AS RANGE RIGHT FOR VALUES ('LA4PRDCLT101', 'LA4PRDCLT201', 'LA4PRDCLT301',
               'LA4PRDCLT401', 'LD4PRDCLT101', 'LD4PRDCLT102', 'LD4PRDCLT201');
              
CREATE PARTITION SCHEME CoreCollectionPartitionScheme
AS PARTITION SwimlaneRangePartitionFunction
TO ([primary],sl1,sl2,sl3,sl4,sl5,sl51,sl6);
GO              


drop table [user_demographics_partition_by_swimlane]
go
CREATE TABLE [dbo].[user_demographics_partition_by_swimlane](
    [user_id] [int] NULL,
    [swimlane] [nvarchar](100) NULL,
    [portal] [nvarchar](200) NULL,
    [status_id] [int] NULL,
    [user_type_id] [int] NULL,
    [gender] [varchar](100) NULL,
    [user_country] [varchar](100) NULL,
    [user_state] [nvarchar](50) NULL,
    [user_zipcode] [nvarchar](50) NULL,
    [birth_dt] [datetime] NULL,
    [hire_dt] [datetime] NULL,
    [term_dt] [datetime] NULL,
    [timezone] [int] NULL,
    [date_stamp] [datetime] NULL,
    [user_country_profile_base] [varchar](100) NULL,
    [user_state_profile_base] [nvarchar](50) NULL,
    [user_zipcode_profile_base] [nvarchar](50) NULL
) ON CoreCollectionPartitionScheme ([swimlane])

GO