--powershell

function   get_logic_core_count(){

    $cpu=get-wmiobject win32_processor

    #$cpu_count=@($cpu).count

    #$cpu.NumberOfLogicalProcessors

    return   @($cpu).count*$cpu[0].NumberOfLogicalProcessors

    }

echo   $(get_logic_core_count)

--powershell

Get-WmiObject   -Class Win32_PhysicalMemory | %{$sum = 0} { $sum += $_.Capacity } {Write-Host   ($sum / 1GB) "GB"}

select   counter_name, ltrim(convert(decimal(18,2),cntr_value*1.0/1024/1024))+'G' as   memoryGB

from   master.sys.dm_os_performance_counters

where   counter_name = 'Total Server Memory (KB)'

union   all select counter_name,   ltrim(convert(decimal(18,2),cntr_value*1.0/1024/1024))+'G' as memoryGB

from   master.sys.dm_os_performance_counters

where   counter_name = 'Target Server Memory (KB)'

union   all select counter_name,   ltrim(convert(decimal(18,2),cntr_value*1.0/1024))+'M' as memoryGB

from   master.sys.dm_os_performance_counters

where   counter_name = 'Optimizer Memory (KB)'

union   all select counter_name,   ltrim(convert(decimal(18,2),cntr_value*1.0/1024))+'M' as memoryGB

from   master.sys.dm_os_performance_counters

where   counter_name = 'SQL Cache Memory (KB)'

union   all select counter_name,   ltrim(convert(decimal(18,2),cntr_value*1.0/1024))+'M' as memoryGB

from   master.sys.dm_os_performance_counters

where   counter_name = 'Connection Memory (KB)'

union   all

SELECT   a.counter_name,ltrim(convert(decimal(18,2),(a.cntr_value * 1.0 /   b.cntr_value)) * 100) +'%'  as memoryGB

FROM

(SELECT   * FROM sys.dm_os_performance_counters

WHERE   counter_name = 'Buffer cache hit ratio'

AND   object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'

THEN   'SQLServer:Buffer Manager'

ELSE   'MSSQL$' + rtrim(@@SERVICENAME) +

':Buffer   Manager' END ) a

CROSS   JOIN

(SELECT   * from sys.dm_os_performance_counters

WHERE   counter_name = 'Buffer cache hit ratio base'

and   object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'

THEN   'SQLServer:Buffer Manager'

ELSE   'MSSQL$' + rtrim(@@SERVICENAME) +

':Buffer   Manager' END ) b;

--powershell

$Size=@{Expression={[int]($_.Size/1GB)}

Name='Size   (GB)'}

$Freespace=@{Expression={[int]($_.Freespace/1GB)}

Name='Free   Space (GB)'}

$PercentFree=@{Expression={[int]($_.Freespace*100/$_.Size)}

Name   = 'Free (%)'}

Get-WmiObject   -Class Win32_LogicalDisk |

Select-Object   -Property DeviceID, $Size, $Freespace, $PercentFree

select   count(total_logical_reads + total_logical_writes) as totalio

,       case when db_name(dbid ) is null then   'Adhoc Queries' else  db_name(dbid) end   as db_name

from   sys.dm_exec_query_stats s1

cross   apply sys.dm_exec_sql_text(sql_handle) as    s2

group   by dbid

会话资源分析 ( 连接数 )

select   (select COUNT(1) from sys.dm_exec_connections) as Cur_connection

union   all

select   (select count(1) from sys.dm_exec_sessions    )  as Cur_connection

-- 会话分析

select   count(1) as num from sys.dm_exec_sessions    where status = 'running'

union   all select    count(1)       

from   sys.dm_exec_sessions

where   is_user_process = 1 and status = 'sleeping' and (DATEDIFF( Minute,   last_request_end_time, getdate())) >30

union   all SELECT count(1)

FROM   sys.dm_exec_sessions es

INNER JOIN sys.dm_tran_session_transactions st -- 系统里还存在的事务

ON   es.session_id = st.session_id

and   es.status='sleeping'

and   DATEDIFF ( MINUTE ,es.last_request_end_time,getdate())>30

--7 、数据文件

-- 数据文件列表

select   db_name(database_id) as db_name,name,physical_name

from   sys.master_files  where type = 0

-- 数据文件使用情况

select   db_name(database_id) as db_name,name, convert(float,size) *   (8192.0/1024.0)/1024. as size,max_size, is_percent_growth,growth,state

from   sys.master_files  where type = 0

-- 数据文件 IO 情况

select   db_name(DbId),cast(convert(decimal(18,2),((IoStallMS/(total*1.0))*100)) as   varchar(20))+'%' as io_stall_pic ,IoStallReadMS,IoStallWriteMS

FROM   (select sum(IoStallMS) as total

from   sys.fn_virtualfilestats(null,null) ) a,sys.fn_virtualfilestats(null,null)

where FileId<>2

declare   @sql_1 varchar(1000),@sql varchar(1000),@cname_1 varchar(50),@cname_2   varchar(50),

@t_name   varchar(50),@D_name varchar(30)

--DECLARE   D_name CURSOR FOR select name from sys.databases where database_id not in   (1,2,3,4)

DECLARE   D_name CURSOR FOR select name from master.sys.databases where database_id not   in(1,2,3,4) and state ='0' and user_access =0

OPEN   D_name

FETCH   FROM D_name INTO @D_name

WHILE   @@FETCH_STATUS=0

begin

set @sql_1='

DECLARE C_size CURSOR FOR

select  top 10  a.name

FROM        '+'['+@D_name+']'+'..sysobjects AS a INNER JOIN

                '+'['+@D_name+']'+'..sysindexes    AS b ON a.id = b.id

WHERE   (a.type =   ''u'') AND (b.indid IN (0, 1))

ORDER BY b.rows DESC'

--select @sql_1

exec (@sql_1)

OPEN C_size

FETCH FROM C_size INTO @cname_2

WHILE   @@FETCH_STATUS=0

 begin

 set @cname_1=@D_name

 set @t_name= @cname_1+'.dbo.'+@cname_2

 set @sql='

 select '''+@cname_1+ '''as DB,

 '''+@cname_2 +'''as t_name,

 convert (char(20), SUM (

   CASE

    WHEN (index_id < 2) THEN row_count

    ELSE 0

   END

   )) as rows,

 LTRIM (convert(float,(STR (SUM (

   CASE

    WHEN (index_id < 2) THEN   (in_row_data_page_count + lob_used_page_count +   row_overflow_used_page_count)  ELSE 0

   END

   ) *    8) ))/1024.) as data

 FROM '+@cname_1+'.sys.dm_db_partition_stats

 WHERE object_id =   object_id('''+@t_name+''')'

 --* (8192.0/1024.0)/1024.)

 exec (@sql)

 --select @sql

 --ltrim(convert(float,size) *   (8192.0/1024.0)/1024.)

 FETCH NEXT FROM C_size INTO @cname_2

 end

CLOSE   C_size

DEALLOCATE   C_size

 FETCH NEXT FROM D_name INTO @D_name

 end

CLOSE   D_name

DEALLOCATE   D_name;

--7 、等待类型

SELECT  wait_type,wait_duration_ms,   cast(convert(decimal(18,2),(wait_duration_ms/(total*1.0))*100) as   varchar(20))+'%' as pct

FROM   (select sum(wait_duration_ms) as total

from   sys.dm_os_waiting_tasks )    a,sys.dm_os_waiting_tasks

ORDER   BY wait_duration_ms DESC

SELECT   top 10

 DB_Name() AS DB_Name,

 object_name(mid.[object_id]) AS Table_Name,

   CONVERT (decimal (28,1),   migs.avg_total_user_cost * migs.avg_user_impact  * (migs.user_seeks + migs.user_scans)) AS   Improvement_measure,

   'CREATE INDEX missing_index_' + CONVERT   (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

   + ' ON ' + mid.statement

   + ' (' + ISNULL (mid.equality_columns,'')

  + CASE WHEN mid.equality_columns IS NOT   NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL   (mid.inequality_columns, '')

   + ')'

   + ISNULL (' INCLUDE (' +   mid.included_columns + ')', '') AS Create_index_statement

 FROM sys.dm_db_missing_index_groups mig

 INNER JOIN   sys.dm_db_missing_index_group_stats migs ON migs.group_handle =   mig.index_group_handle

 INNER JOIN sys.dm_db_missing_index_details   mid ON mig.index_handle = mid.index_handle

 WHERE CONVERT (decimal (28,1),   migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks +   migs.user_scans)) > 10

SELECT

(total_elapsed_time / execution_count)/1000 N' 平均时间 ms'

,total_elapsed_time/1000 N' 总花费时间 ms'

,total_worker_time/1000 N' 所用的 CPU 总时间 ms'

,total_physical_reads N' 物理读取总次数 '

,total_logical_reads/execution_count N' 每次逻辑读次数 '

,total_logical_reads N' 逻辑读取总次数 '

,total_logical_writes N' 逻辑写入总次数 '

,execution_count N' 执行次数 '

,SUBSTRING(st.text,   (qs.statement_start_offset/2) + 1,

((CASE   statement_end_offset

WHEN   -1 THEN DATALENGTH(st.text)

ELSE   qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) N' 执行语句 '

,creation_time N' 语句编译时间 '

,last_execution_time N' 上次执行时间 '

FROM

sys.dm_exec_query_stats   AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

WHERE

(total_elapsed_time / execution_count)/1000 >1000 --( 大于 1s)

and

SUBSTRING(st.text,   (qs.statement_start_offset/2) + 1,

((CASE   statement_end_offset

WHEN   -1 THEN DATALENGTH(st.text)

ELSE   qs.statement_end_offset END

-   qs.statement_start_offset)/2) + 1) not like '?tch%'

ORDER   BY

total_elapsed_time   / execution_count DESC;

select   flag=1,spid,kpid,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,

s1=a.spid,s2=0 ,' 引起数据库死锁的是 : '+ CAST(a.blocked AS VARCHAR(10)) as   descript,waittype,waitresource,waittime,[sql_handle]

from

master..sysprocesses a

join

(select blocked from master..sysprocesses group by blocked   )b

on

a.spid=b.blocked

where a.blocked=0

union all

select   flag=0,spid,kpid,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,

hostprocess,nt_domain,net_address   ,s1=blocked,s2=1,

' 进程号 SPID:'+CAST(spid   AS VARCHAR(10))+' 被 '+' 进程号 SPID:'+ CAST(blocked AS VARCHAR(10))  as descript,

waittype,waitresource,waittime,[sql_handle]

from 

master..sysprocesses a

where blocked<>0

if   object_id('tempdb..#t_name') is not null

begin

 drop table #t_name

end

create   table #t_name(

DB   varchar(50),

t_name   varchar(50)

)

declare   @sql_1 varchar(300)

DECLARE   C_DBname CURSOR FOR

 select name from master.sys.databases where   database_id not in (1,2,3,4) and state_desc='ONLINE'

OPEN   C_DBname

DECLARE   @D_name VARCHAR(30)

FETCH   FROM C_DBname INTO @D_name

WHILE   @@FETCH_STATUS=0

 begin

 set @sql_1='insert into #t_name select   '''+'['+@D_name+']'+''',name from '+'['+@D_name+']'+'.sys.tables'

 exec(@sql_1)

 FETCH NEXT FROM C_DBname INTO @D_name

 end

CLOSE   C_DBname

DEALLOCATE   C_DBname

/***********************************************************/

-- 查询出 #t_name 表中所有表的空间大小

if   object_id('tempdb..#t_space') is not null

begin

 drop table    #t_space

end

 create table #t_space(

 DB varchar(30),  -- 数据库名

 t_name varchar(30), -- 表名

 rows varchar(20), -- 行数

 reserved varchar(40), -- 占用空间大小

 data varchar(40),  -- 数据空间大小

 used varchar(40),  -- 使用空间大小

 index_size varchar(40), -- 索引空间大小

 unused varchar(40)  -- 未使用空间大小

)

declare   @id int   -- The object id that takes   up space

  ,@type character(2) -- The object type.

  ,@pages bigint   -- Working variable for size calc.

  ,@dbname sysname

  ,@dbsize bigint

  ,@logsize bigint

  ,@reservedpages  bigint

  ,@usedpages    bigint

  ,@rowCount bigint

  ,@cname_1 varchar(30)

  ,@cname_2 varchar(30)

  ,@t_name varchar(40)

  ,@sql_2 varchar(max)

DECLARE   C_size CURSOR FOR

 select DB,t_name from #t_name

OPEN   C_size

FETCH   FROM C_size INTO @cname_1,@cname_2

WHILE   @@FETCH_STATUS=0

 begin

 set @t_name= @cname_1+'.dbo.'+@cname_2

 set @sql_2='

 insert into   #t_space(DB,t_name,rows,reserved,data,used)

 select '''+@cname_1+ '''as DB,

 '''+@cname_2 +'''as t_name,

 convert (char(20), SUM (

   CASE

    WHEN (index_id < 2) THEN row_count

    ELSE 0

   END

   )) as rows,

 LTRIM (STR (SUM (reserved_page_count) * 8,   15, 0) ) as reserved,

 LTRIM (STR (SUM (

   CASE

    WHEN (index_id < 2) THEN   (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE 0

   END

   ) * 8, 15, 0) ) as data,

 LTRIM (STR (SUM (used_page_count) * 8, 15,   0) )as used

 FROM '+@cname_1+'.sys.dm_db_partition_stats

 WHERE object_id =   object_id('''+@t_name+''')'

 exec (@sql_2)

 FETCH NEXT FROM C_size INTO   @cname_1,@cname_2

 end

CLOSE   C_size

DEALLOCATE   C_size

select   top 10 DB,t_name,rows,data from #t_space    where rows>100000000 or data>10485760

 drop table #t_name

 -- 数据库对象

 if OBJECT_ID('tempdb..#tb_databaseinfo')is   not null

 drop table #tb_databaseinfo

 create table #tb_databaseinfo

 (

  id int identity(1,1),

  dbname varchar(500)

 )

 insert into #tb_databaseinfo

 select name from master.sys.databases where   state_desc='ONLINE'

if   OBJECT_ID('tempdb..#object_info')is not null

 drop table #object_info

 create table #object_info

 (

  id int identity(1,1),

  dbname varchar(50),

  u_num int,

  v_num int,

  p_num int,

  f_num int

 )

DECLARE   db_Cursor CURSOR FOR SELECT dbname FROM #tb_databaseinfo

OPEN   db_Cursor

 DECLARE @DBName   VARCHAR(50)

FETCH   FROM db_Cursor INTO @DBName

 WHILE @@FETCH_STATUS=0

  BEGIN

  if OBJECT_ID('tempdb..#u_info')is not   null  drop table #u_info

  create table #u_info (num INT)

  if OBJECT_ID('tempdb..#v_info')is not   null  drop table #v_info

  create table #v_info (num INT)

  if OBJECT_ID('tempdb..#p_info')is not   null  drop table #p_info

  create table #p_info (num INT)

  if OBJECT_ID('tempdb..#f_info')is not   null  drop table #f_info

  create table #f_info (num INT)

  DECLARE @sql1 varchar(max),@sql2   varchar(max),@sql3 varchar(max),@sql4 varchar(max),

  @u_num varchar(10),@v_num int,@p_num   int,@f_num int

  set @sql1='select count(1) FROM   '+'['+@DBName+']'+'.sys.objects WHERE type = ''U'''

  set @sql2='select count(1) FROM   '+'['+@DBName+']'+'.sys.objects WHERE type = ''V'''

  set @sql3='select count(1) FROM   '+'['+@DBName+']'+'.sys.objects WHERE type = ''P'''

  set @sql4='select count(1) FROM   '+'['+@DBName+']'+'.sys.objects WHERE type IN (''FN'',''IF'',''TF'')'

  insert into #u_info exec (@sql1) select   @u_num=num from #u_info

  insert into #v_info exec (@sql2) select   @v_num=num from #v_info

  insert into #p_info exec (@sql3) select   @p_num=num from #p_info

  insert into #f_info exec (@sql4) select   @f_num=num from #f_info

  insert into   #object_info(dbname,u_num,v_num,p_num,f_num)

  select @DBName,@u_num,@v_num,@p_num,@f_num

  FETCH NEXT FROM db_Cursor INTO @DBName

  END

CLOSE   db_Cursor;

DEALLOCATE   db_Cursor;

select   dbname,u_num,v_num,p_num,f_num from #object_info

drop   table #tb_databaseinfo

drop   table #object_info

SELECT   TOP 10

[session_id],

[request_id],

[start_time] AS ' 开始时间 ',

[status] AS ' 状态 ',

[command] AS ' 命令 ',

dest.[text] AS 'sql 语句 ',

DB_NAME([database_id]) AS ' 数据库名 ',

[blocking_session_id] AS ' 正在阻塞其他会话的会话 ID',

der.[wait_type] AS ' 等待资源类型 ',

[wait_time] AS ' 等待时间 ',

[wait_resource] AS ' 等待的资源 ',

[dows].[waiting_tasks_count] AS ' 当前正在进行等待的任务数 ',

[reads] AS ' 物理读次数 ',

[writes] AS ' 写次数 ',

[logical_reads] AS ' 逻辑读次数 ',

[row_count] AS ' 返回结果行数 '

FROM   sys.[dm_exec_requests] AS der

INNER   JOIN [sys].[dm_os_wait_stats] AS dows

ON   der.[wait_type]=[dows].[wait_type]

CROSS   APPLY

sys.[dm_exec_sql_text](der.[sql_handle])   AS dest

WHERE   [session_id]>50 

ORDER   BY [cpu_time] DESC

SELECT   name,create_date,is_disabled FROM sys.sql_logins

WHERE   PWDCOMPARE(name, password_hash) = 1

SELECT   name,create_date,is_disabled FROM sys.sql_logins

WHERE   PWDCOMPARE('', password_hash) = 1

select   name,create_date,

case is_disabled when 0 then ' 启用 '

when 1 then ' 禁用 ' end as is_diabled

from   sys.sql_logins where create_date>dateadd(dd,-90,getdate())

if   OBJECT_ID('tempdb..#tb_databaseinfo')is not null

 drop table #tb_databaseinfo

 create table #tb_databaseinfo

 (

  id int identity(1,1),

  dbname varchar(100)

 )

 insert into #tb_databaseinfo

 select name from master.sys.databases where   state_desc='ONLINE'

if   OBJECT_ID('tempdb..#userinfo')is not null

 drop table #userinfo

 create table #userinfo

 (

  id int identity(1,1),

  dbname varchar(100),

  name varchar(100),

  createdate datetime,

  hasdbaccess int

 )

DECLARE   db_Cursor  CURSOR FOR SELECT dbname   FROM #tb_databaseinfo

OPEN   db_Cursor

 DECLARE @DBName   VARCHAR(50)

FETCH   FROM db_Cursor INTO @DBName

 WHILE @@FETCH_STATUS=0

  BEGIN

   declare @sql varchar(max)

   set @sql='select   '''+@DBName+''',name,create_date from '   +'['+@DBName+']'+'.sys.database_principals where  type<>''R'' and type<>''U'' and   create_date>dateadd(dd,-90,getdate())'

   insert into   #userinfo(dbname,name,createdate)

   exec (@sql)

     FETCH NEXT FROM db_Cursor INTO @DBName

  END

CLOSE   db_Cursor;

DEALLOCATE   db_Cursor;

select   dbname,name,createdate from #userinfo

 drop table #tb_databaseinfo

 drop table #userinfo

if   OBJECT_ID('tempdb..#sysadmin_info')is not null

 drop table #sysadmin_info

 create table #sysadmin_info

 (id int identity(1,1),

  ServerRole varchar(100),

  MemberName varchar(100),

  MemberSID VARCHAR(MAX))

 insert into #sysadmin_info

 exec sp_helpsrvrolemember 'sysadmin'

select   MemberName from #sysadmin_info a

inner   join sys.sql_logins b on a.MemberName=b.name where b.name not in ()

if   OBJECT_ID('tempdb..#tb_databaseinfo')is not null

 drop table #tb_databaseinfo

 create table #tb_databaseinfo

 (

  id int identity(1,1),

  dbname varchar(50)

 )

 insert into #tb_databaseinfo

 select name from master.sys.databases where   state_desc='ONLINE'

if   OBJECT_ID('tempdb..#userinfo')is not null

 drop table #userinfo

 create table #userinfo

 (

  id int identity(1,1),

  dbname varchar(50),

  dbrole_1 varchar(50),

  membername varchar(50),

 )

DECLARE   db_Cursor CURSOR FOR SELECT dbname FROM #tb_databaseinfo

OPEN   db_Cursor

 DECLARE @DBName   VARCHAR(50)

FETCH   FROM db_Cursor INTO @DBName

 WHILE @@FETCH_STATUS=0

  BEGIN

   declare @sql varchar(max)

   set @sql='select '''+@DBName+''',DbRole =   g.name, MemberName = u.name

from   '+'['+@DBName+']'+'.sys.database_principals u,'+'['+@DBName+']'+'   .sys.database_principals g, '+'['+@DBName+']'+'.sys.database_role_members m

where   g.principal_id = m.role_principal_id

and   u.principal_id = m.member_principal_id

order   by 1, 2'

   insert into   #userinfo(dbname,dbrole_1,membername)

   exec (@sql)

     FETCH NEXT FROM db_Cursor INTO @DBName

  END

CLOSE   db_Cursor;

DEALLOCATE   db_Cursor;

select   dbname,dbrole_1,membername from #userinfo where dbrole_1='db_owner'

drop   table #tb_databaseinfo

drop   table #userinfo

select   (select count(1) from msdb.dbo.sysjobs) as job_N,

(select   count(1) from msdb.dbo.sysjobs where enabled=1 ) as job_O,

(SELECT   count(1) FROM  msdb.dbo.sysjobs S   WITH(NOLOCK)

 INNER JOIN msdb.dbo.sysjobhistory H   WITH(NOLOCK) ON S.job_id=H.job_id

 LEFT JOIN sys.syslogins L WITH(NOLOCK) ON   S.owner_sid=L.sid

 WHERE run_status<>1 AND   step_id<>0 AND   H.run_date>=CAST(CONVERT(VARCHAR(8),DATEADD(HH,-24,GETDATE()),112) AS   INT)) as job_F 

-- 作业执行情况

SELECT   [sJOB].[name] AS [jobname] ,sJOB.date_created as createdate,CASE WHEN   [sJOBH].[run_date] IS NULL

OR   [sJOBH].[run_time] IS NULL THEN NULL

ELSE   CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '+

STUFF(STUFF(RIGHT('000000'+   CAST([sJOBH].[run_time] AS VARCHAR(6)),

6),   3, 0, ':'), 6, 0, ':') AS DATETIME)

END   AS [last_date] ,

CASE   [sJOBH].[run_status]

WHEN   0 THEN 'failure'

WHEN   1 THEN 'successful'

WHEN   2 THEN 'retry'

WHEN   3 THEN 'cancel'

WHEN   4 THEN 'running' -- In Progress??

END   AS [last_status]

into   #tmp

FROM   [msdb].[dbo].[sysjobs] AS [sJOB]

LEFT   JOIN ( SELECT [job_id] ,

MIN([next_run_date])   AS [NextRunDate] ,

MIN([next_run_time])   AS [NextRunTime]

FROM  [msdb].[dbo].[sysjobschedules]

GROUP   BY [job_id]

)   AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]

LEFT   JOIN ( SELECT  [job_id] ,

[run_date]   ,

[run_time]   , 

[run_status]   ,

[run_duration]   ,

[message]   ,

ROW_NUMBER()   OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS   RowNumber

FROM   [msdb].[dbo].[sysjobhistory]

WHERE   [step_id] = 0) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]AND   [sJOBH].[RowNumber] = 1

ORDER   BY [jobname]

SELECT   S.name,L.loginname

into   #tmp1

FROM

msdb.dbo.sysjobs   S WITH(NOLOCK)

INNER   JOIN

msdb.dbo.sysjobhistory   H WITH(NOLOCK) ON S.job_id=H.job_id

LEFT   JOIN

sys.syslogins   L WITH(NOLOCK) ON S.owner_sid=L.sid

select   distinct l.name,l.loginname,a.createdate,a.last_date,a.last_status from #tmp   a

inner   join #tmp1 l on a.jobname=l.name

drop   table #tmp

drop   table #tmp1

SELECT

      d.name as   DatabaseName,bs.type,bs.backup_finish_date,

      case

      when

       CONVERT(varchar, (DATEDIFF( HH,   bs.backup_finish_date,getdate())), 120) <24 then 'OK'

      else

      'Expired'

      end      

      AS LastBakupDate     

FROM   sys.databases d 

left   join   

(  

    SELECT database_name,   MAX(backup_finish_date) backup_finish_date, type 

    FROM msdb.dbo.backupset 

    where type = N'D'

    GROUP BY database_name, type 

)   bs ON d.name = bs.database_name