Check Rows

/*

Check Rows

*/

USE  [App]

GO

SELECT '['+ SCHEMA_NAME(A.schema_id) + '].[' +  A.Name + ']' AS [TABLE]

     , SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

AND SCHEMA_NAME(A.schema_id) = 'pro'

GROUP BY    A.schema_id, A.Name

ORDER BY 2 desc

GO

USE  Staging

GO

SELECT '['+ SCHEMA_NAME(A.schema_id) + '].[' +  A.Name + ']' AS [TABLE]

     , SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

AND SCHEMA_NAME(A.schema_id) = 'DW'

GROUP BY    A.schema_id, A.Name

ORDER BY 2 desc

GO

SELECT '['+ SCHEMA_NAME(A.schema_id) + '].[' +  A.Name + ']' AS [TABLE]

     , SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

AND SCHEMA_NAME(A.schema_id) = 'OTH'

GROUP BY    A.schema_id, A.Name

ORDER BY 2 desc

GO

SELECT '['+ SCHEMA_NAME(A.schema_id) + '].[' +  A.Name + ']' AS [TABLE]

     , SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

AND SCHEMA_NAME(A.schema_id) = 'DBO'

GROUP BY    A.schema_id, A.Name

ORDER BY 2 desc

GO

SELECT '['+ SCHEMA_NAME(A.schema_id) + '].[' +  A.Name + ']' AS [TABLE]

     , SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

AND SCHEMA_NAME(A.schema_id) = 'FLE'

GROUP BY    A.schema_id, A.Name

ORDER BY 2 desc

GO

Check Running Jobs

/*

Check Running Jobs

*/

SELECT

    [sJOB].[job_id] AS [JobID]

    , [sJOB].[name] AS [JobName]

    , CASE [sJSTP].[last_run_date]

        WHEN 0 THEN NULL

        ELSE

            CAST(

                CAST([sJSTP].[last_run_date] AS CHAR(8))

                + ' '

                + STUFF(

                    STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),  6)

                        , 3, 0, ':')

                    , 6, 0, ':')

                AS DATETIME)

      END AS [LastRunDateTime]

    , [sJSTP].[step_uid] AS [StepID]

    , [sJSTP].[step_id] AS [StepNo]

    , [sJSTP].[step_name] AS [StepName]

    , CASE [sJSTP].[last_run_outcome]

        WHEN 0 THEN 'Failed'

        WHEN 1 THEN 'Succeeded'

        WHEN 2 THEN 'Retry'

        WHEN 3 THEN 'Canceled'

        WHEN 5 THEN 'Unknown'

      END AS [LastRunStatus]

    , STUFF(

            STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)),  6)

                , 3, 0, ':')

            , 6, 0, ':')

      AS [LastRunDuration (HH:MM:SS)]

    , [sJSTP].[last_run_retries] AS [LastRunRetryAttempts]

FROM

    [msdb].[dbo].[sysjobsteps] AS [sJSTP]

    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]

        ON [sJSTP].[job_id] = [sJOB].[job_id]

where [sJOB].[name] = 'DAILY_'

ORDER BY [JobName], [StepNo]

SELECT

    [sJOB].[job_id] AS [JobID]

    , [sJOB].[name] AS [JobName]

    , [sJSTP].[step_uid] AS [StepID]

    , [sJSTP].[step_id] AS [StepNo]

    , [sJSTP].[step_name] AS [StepName]

    , CASE [sJSTP].[subsystem]

        WHEN 'ActiveScripting' THEN 'ActiveX Script'

        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'

        WHEN 'PowerShell' THEN 'PowerShell'

        WHEN 'Distribution' THEN 'Replication Distributor'

        WHEN 'Merge' THEN 'Replication Merge'

        WHEN 'QueueReader' THEN 'Replication Queue Reader'

        WHEN 'Snapshot' THEN 'Replication Snapshot'

        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'

        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'

        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'

        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'

        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'

        ELSE sJSTP.subsystem

      END AS [StepType]

    , [sPROX].[name] AS [RunAs]

    , [sJSTP].[database_name] AS [Database]

    , [sJSTP].[command] AS [ExecutableCommand]

    , CASE [sJSTP].[on_success_action]

        WHEN 1 THEN 'Quit the job reporting success'

        WHEN 2 THEN 'Quit the job reporting failure'

        WHEN 3 THEN 'Go to the next step'

        WHEN 4 THEN 'Go to Step: '

                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))

                    + ' '

                    + [sOSSTP].[step_name]

      END AS [OnSuccessAction]

    , [sJSTP].[retry_attempts] AS [RetryAttempts]

    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]

    , CASE [sJSTP].[on_fail_action]

        WHEN 1 THEN 'Quit the job reporting success'

        WHEN 2 THEN 'Quit the job reporting failure'

        WHEN 3 THEN 'Go to the next step'

        WHEN 4 THEN 'Go to Step: '

                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))

                    + ' '

                    + [sOFSTP].[step_name]

      END AS [OnFailureAction]

FROM

    [msdb].[dbo].[sysjobsteps] AS [sJSTP]

    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]

        ON [sJSTP].[job_id] = [sJOB].[job_id]

    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]

        ON [sJSTP].[job_id] = [sOSSTP].[job_id]

        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]

    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]

        ON [sJSTP].[job_id] = [sOFSTP].[job_id]

        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]

    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]

        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

where [sJOB].[name] = 'DAILY_'

ORDER BY [JobName], [StepNo]

Check Store Proc Process Inside

/*

Check Store Proc Process Inside

*/

SELECT  spid,

        sp.[status],

        loginame [Login],

        hostname,

        blocked BlkBy,

        sd.name DBName,

        cmd Command,

        cpu CPUTime,

        physical_io DiskIO,

        last_batch LastBatch,

        [program_name] ProgramName  

FROM master.dbo.sysprocesses sp

JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid

where sp.[status] = 'runnable                      '

ORDER BY spid

SELECT  requests.session_id,

        requests.status,

        requests.command,

        requests.statement_start_offset,

        requests.statement_end_offset,

        requests.total_elapsed_time,

        details.text

FROM    sys.dm_exec_requests requests

CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) details

WHERE   requests.session_id > 50

ORDER BY total_elapsed_time DESC

SELECT  SUBSTRING(detail.text,

                  requests.statement_start_offset / 2,

                  (requests.statement_end_offset - requests.statement_start_offset) / 2)

FROM    sys.dm_exec_requests requests

CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) detail

WHERE   requests.session_id = 68 -- this is the id of the store proc running.