SQL

3 Responses to SQL

1. Spradlike says:

   March 20, 2013 at 8:25 pm

   /* Job history metadata */
   use msdb

   select
   j.name,
   h.message,
   h.run_date,
   h.run_time,
   h.run_duration,
   s.next_run_date,
   s.next_run_time

   from sysjobs j
   inner join sysjobschedules s on j.job_id = s.job_id
   inner join sysjobhistory h on h.job_id = j.job_id

   where run_status = 0 — 1 is succes, 0 is error

   order by run_date desc

   /*
   Status of the job execution:
   0 = Failed
   1 = Succeeded
   2 = Retry
   3 = Canceled
   */
2. Spradlike says:

   March 20, 2013 at 8:36 pm

   /* select all databases without a backup */
   /* Run for compatibility check for SQL Server 2000 – sys.databases does not exist. */
   /* SQL Server 2000 backup script below – IN PROGRESS */

   select @@VERSION as [SQLServerVersion]
   GO
   use msdb
   GO

   select
   –@@Servername as [Instance],
   name as [MissingBackUp]
   from sys.databases
   where 0=0
   AND name <> ‘tempdb’
   AND name not in
   (select distinct database_name from backupset where backup_start_date >= DATEADD(DAY,-7,getdate())
   AND type = ‘D’)
3. Spradlike says:

   March 20, 2013 at 8:37 pm

   —————————————————
   — Table variable based on SP_WHO2.
   — Filter by database name to see who is logged
   — in and what processes are currently running.
   —————————————————

   set nocount on
   declare @sp_who2 table
   (
   SPID int,
   Status varchar(255),
   Login varchar(255),
   HostName varchar(255),
   BlkBy varchar(255),
   DBName varchar(255),
   Command varchar(255),
   CPUTime int,
   DiskIO int,
   LastBatch varchar (255),
   ProgramName varchar(255),
   SPID2 int,
   REQUESTID int
   )

   insert into @sp_who2 exec sp_who2
   select *
   from @sp_who2 sp
   join sys.dm_exec_sessions s on sp.SPID = s.session_id
   join sys.dm_exec_connections c ON s.session_id = c.session_id
   where 0=0
   –and dbname = ”
   order by LastBatch
   GO

Comments are closed.