SQL

Useful SQL Server scripts in one place.

3 Responses to SQL

  1. Spradlike says:

    /* 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:

    /* 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:

    —————————————————
    — 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

Leave a Reply

Your email address will not be published. Required fields are marked *