Get Information on All Jobs running on a SQL server Instance.

The SQL server Jobs which are running on a Instance are very Important. As a DBA you need to take care of these Jobs and have all information about the Job. Your will get information on Jobs is SysJobs
but again not in the way you want. So just thought of pulling the Job information and below is the stored 
Procedure for the same.
This procedure give information on 

[job_id],

[Jobs],

[Category],

[Owner],

[Schedule Name],

[enabled],

[Frequecy],

[DateCreated],

[StartDate],

[NextRunDate],

[NextRunTime]

USE YourDatabaseName
GO
IF OBJECT_ID('dbo.spGetJobInfo') IS NOT NULL
DROP PROCEDURE dbo.spGetJobInfo
GO
CREATE PROCEDURE dbo.spGetJobInfo
AS
SET NOCOUNT ON
DECLARE @jobid VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
CREATE TABLE #Jobs_Schedule(
            [schedule_id] [INT],
            [name] [SYSNAME],
            [enabled] [INT],
            [freq_type] [INT],
            [freq_interval] [INT],
            [freq_subday_type] [INT],
            [freq_subday_interval] [INT],
            [freq_relative_interval] [INT],
            [freq_recurrence_factor] [INT],
            [active_start_date] [INT],
            [active_end_date] [INT],
            [active_start_time] [INT],
            [active_end_time] [INT],
            [date_created] [DATETIME],
            [desc] VARCHAR(1000),
            [next_run_date] [INT],
            [next_run_time] [INT],
            [schedule_uid]    VARCHAR(1000),
            [job_count] [INT]
            ) ON [PRIMARY]
DECLARE c1 CURSOR FOR SELECT job_id FROM msdb..sysjobs
OPEN c1
    FETCH NEXT FROM c1 INTO @jobid
    WHILE @@fetch_status=0
        BEGIN
            INSERT #Jobs_Schedule EXEC ('msdb..sp_help_jobschedule @job_id='''+ @jobid+''',@include_description=1')
            FETCH NEXT FROM c1 INTO @jobid
        END
CLOSE c1
DEALLOCATE c1

CREATE TABLE #TempJoin
(
    job_id UNIQUEIDENTIFIER,
    Jobs VARCHAR(250),
    Category VARCHAR(250),
    OwnerName  VARCHAR(100),
    ScheduleName  VARCHAR(250),
    ISenabled  CHAR(4),
    Frequency VARCHAR(250),
    Date_Created  DATETIME,
    Active_Start_Date INT,
    Next_Run_Date  INT,
    Next_Run_Time INT
)
INSERT INTO #TempJoin
(
    job_id,
    Jobs,
    Category,
    OwnerName,
    ScheduleName,
    ISenabled,
    Frequency,
    Date_Created,
    Active_Start_Date,
    Next_Run_Date,
    Next_Run_Time
)
(
SELECT 
a.[job_id],
a.[Jobs],
a.[Category],
l.[name] AS [Owner],
a.[Schedule Name],
a.[enabled],
temp.[desc] AS [Frequecy],
temp.date_created AS [DateCreated],
temp.active_start_date AS [StartDate],
temp.next_run_date AS [NextRunDate],
temp.next_run_time AS [NextRunTime]
FROM #Jobs_Schedule AS temp 
RIGHT OUTER JOIN 
(SELECT
    j.job_id,
    j.[name] AS [Jobs], 
    j.owner_sid, 
    c.[name] AS [Category], 
    js.schedule_id, 
    s.[name] AS [Schedule Name], 
    CASE j.enabled WHEN 1 THEN 'YES' ELSE 'NO' END AS [enabled] 
    FROM msdb..syscategories AS c 
    RIGHT OUTER JOIN 
    msdb..sysjobs AS j ON c.category_id = j.category_id
    LEFT OUTER JOIN 
    msdb..sysjobschedules AS js ON j.job_id = js.job_id
    LEFT OUTER JOIN 
    msdb..sysschedules AS s ON js.schedule_id = s.schedule_id)
AS a ON temp.schedule_id = a.schedule_id 
LEFT OUTER JOIN 
sys.server_principals AS l ON a.owner_sid = l.sid)

CREATE TABLE #TempJoin1
(
    Name VARCHAR(250),
    job_id UNIQUEIDENTIFIER,
    LastRunStatus VARCHAR(25),
    LastRunDate INT,
    LastRunTIme INT,
)
INSERT INTO #TempJoin1
(
    Name,
    job_id,
    LastRunStatus,
    LastRunDate,
    LastRunTIme
)
(
SELECT  
   j.[name] AS [JobName],
   j.[job_id],  
   run_status = CASE h.run_status  
   WHEN 0 THEN 'Failed' 
   WHEN 1 THEN 'Succeeded' 
   WHEN 2 THEN 'Retry' 
   WHEN 3 THEN 'Canceled' 
   WHEN 4 THEN 'In progress' 
   END, 
   h.run_date AS LastRunDate,   
   h.run_time AS LastRunTime 
   FROM msdb..sysjobhistory h  
   INNER JOIN
   msdb..sysjobs j ON h.job_id = j.job_id  
   WHERE h.instance_id IN  
   (SELECT MAX(h.instance_id)  
        FROM msdb..sysjobhistory h GROUP BY (h.job_id))
)

SELECT 
    t1.Jobs,
    t1.OwnerName,
    t1.Category,
    t1.Date_Created,
    t1.Active_Start_Date,
    t1.ISenabled,
    t2.LastRunDate,
    t2.LastRunTIme,
    t2.LastRunStatus
    FROM #TempJoin t1
    INNER JOIN #TempJoin1 t2
    ON t1.job_id = t2.job_id

DROP Table #Jobs_Schedule
DROP Table #TempJoin
DROP Table #TempJoin1
GO

T-SQL to Get Default Audit Level of SQL server Instance

I have come across situations where you need to go and look for Default Audit Level for Installed Instances or servers. You can do this by getting info in server instances. Now what if you have around 100 server and instance. For this I could find a easy way and that’s a T-SQL function to get the default Audit Level for Instance.

Function:

USE YourDatabaseName
GO
CREATE FUNCTION dbo.fn_get_default_path()
RETURNS int
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path int, @value_name nvarchar(20);

    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty(‘InstanceName’)), ‘MSSQLSERVER’);

    — sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\Microsoft SQL Server\Instance Names\SQL’, @instance_name, @system_instance_name output;
    SET @registry_key = N’Software\Microsoft\Microsoft SQL Server\’ + @system_instance_name + ‘\MSSQLServer’;

    SET @value_name = N’AuditLevel’
    EXEC master.dbo.xp_regread N’HKEY_LOCAL_MACHINE’, @registry_key, @value_name, @path output;   
    RETURN @path;
END
GO

Now to get the value run the below script:

DECLARE @Temp varchar(max),@Temp1 varchar(max)
SET @Temp = dbo.fn_get_default_path()
If @Temp = 0
Begin
Set @Temp1 = ‘NONE’
END
IF @Temp = 1
BEGIN
SET @Temp1 = ‘Successful Logins Only’
END
If @Temp = 2
BEGIN
SET @Temp1 = ‘Failed Logins Only’
END
IF @Temp = 3
BEGIN
SET @Temp1 = ‘Both Successful and Failed Logins’
END
SELECT @Temp1
GO

Get Free Disk Space for SQL server 2000

As a DBA you always need to monitor various metrics of a Database server. Disk space is one such metric you gotta watch out for. Monitoring Disk space for SQL server 2000 is bit difficult through script. Just happened to write a Script for SQL server 2000 which gives you free disk space in GB for all existing drives on database server.

SET NOCOUNT ON
DECLARE @TEMP AS VARCHAR(4000)
DECLARE @MBfree int
DECLARE @CMD1 varchar(1000)
SET @MBfree = 0
SET @CMD1 =
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[FreeSpace] int NOT NULL)
INSERT INTO #tbl_xp_fixeddrives(Drive, [FreeSpace])
EXEC master.dbo.xp_fixeddrives
SELECT @TEMP =
CREATE FUNCTION dbo.GetDriveSize (@driveletter CHAR(1))
 RETURNS NUMERIC(20)
 BEGIN
  DECLARE  @rs INTEGER, @fso INTEGER, @getdrive VARCHAR(13), @drv INTEGER,
@drivesize VARCHAR(20)
  SET @getdrive = ”GetDrive(“” + @driveletter + ””)”
  EXEC @rs = sp_OACreate ”Scripting.FileSystemObject”, @fso OUTPUT
  IF @rs = 0
   EXEC @rs = sp_OAMethod @fso, @getdrive, @drv OUTPUT
  IF @rs = 0
   EXEC @rs = sp_OAGetProperty @drv,”TotalSize”, @drivesize OUTPUT
  IF @rs<> 0
   SET @drivesize = NULL
  EXEC sp_OADestroy @drv
  EXEC sp_OADestroy @fso
  RETURN @drivesize
 END’
EXEC(@TEMP)
SELECT drive+‘:’+‘ ‘+
CAST(CAST((FreeSpace/(((dbo.GetDriveSize(drive))/1024)/1024))*100.0 as int) as VARCHAR(4000))+‘%’
from #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives
SELECT @TEMP = ‘USE ‘
SELECT @TEMP = @TEMP+‘master ‘+‘DROP FUNCTION dbo.GetDriveSize’
EXEC (@TEMP)
SET NOCOUNT OFF
GO