There are a number of tables available in msdb for tracking job execution statuses.

Here are some:

1. sysjobs – main table

2. sysjobsteps – detail info about each step

3. sysjobschedules – info about the job scheduling

4. sysjobservers – remote/local servers where the job executes

5. sysjobhistory – historical records of SQL jobs execution

6. sysjobactivity – current job status, next run time

7. sysjobstepslogs – job step log info.

To find out the statuses of all jobs executed within the past 7 days

[cc lang=”SQL”]

-- Variable Declarations 
DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT 

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days  
SET @Year = DATEPART(yyyy, @PreviousDate)  
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) 
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) 
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) 
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS INT) 

-- Final Logic 
SELECT   j.[name], 
         s.step_name, 
         h.step_id, 
         h.step_name, 
         h.run_date, 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server 
FROM     msdb.dbo.sysjobhistory h 
         INNER JOIN msdb.dbo.sysjobs j 
           ON h.job_id = j.job_id 
         INNER JOIN msdb.dbo.sysjobsteps s 
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_status = 0 -- Failure 
         AND h.run_date > @FinalDate 
ORDER BY h.instance_id DESC

[/cc]

Most of the times for DW loading purposes when you get technical specifications sheet from BA, it will include the tables meta-data. From the spreadsheet, you can use excel concatenate to build a SQL script for the DDL statements.

Here is sample file with formulas sourced from mssqltips.com TableBuilder

1. search for computer management
2. navigate to local users and groups
3. modify the group SQLServerMSASUser$ComputerName$MSSQLSERVER
4. Then add login

Default Instance – Machine name or server name                                                                                      Named instance – servername$instancename : WIN2008$EARTH

SQL Server can have 1 default and 32 named instances.

We have specific setting in SQL Server using which we can specify the use of CPUs. Default configuration is to use all the available CPUs and queries are executed in parallel. But the requirements vary depending on OLTP or OLAP systems.

MAXDOP : Maximum degree of Parallelism.

The values of all SQL Server related configuration properties can be seen from the query

[cc lang=”SQL”]

select * from sys.configurations

— How to use MAXDOP?

SELECT * from dbo.Authors
OPTION (MAXDOP 1)

[/cc]

A good amount of testing is needed after the configuration changes.