Find Actively Running SQL Server Agent Jobs

Hi there,

An ETL developer asked my help recently with a SQL Server Agent job needed to start running if the main job finished running.  Occasionally main job run longer than expected and interfered with the secondary job which should run after the main job.  In the first step of secondary job we added a step to continue if the following query returned no rows if it returns a row then we set the job to retry again in 10  minutes for 3 times which solved our issue.

HTH,
Bulent


USE msdb;
GO
SELECT
    j.name AS job_name,
    ja.start_execution_date,     
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM dbo.sysjobactivity AS ja
    LEFT OUTER JOIN dbo.sysjobhistory AS jh ON ja.job_history_id = jh.instance_id
    INNER JOIN dbo.sysjobs AS j ON ja.job_id = j.job_id
    INNER JOIN dbo.sysjobsteps AS js ON ja.job_id = js.job_id AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND ja.start_execution_date is not null
AND ja.stop_execution_date is null
--AND j.name = 'NameOfTheJob' -- Uncomment and supply the name of the job you need to find out if it's actively running
GO

Get Size Information For All Tables in All Databases

Hello there,

I have been working on a small project to log and track the size and growth of the tables in all databases.  So that overtime I can show the growth size and estimate  the disk space allocation requirements for databases in production environment.  Here is the script I used to capture the information.  I used SQL Server Agent job scheduled to run once a day after midnight and store the results in a repository to be monitored and reported on later.  You can uncomment the where  clause to exclude system tables and tables with no records in them if you want.


SET NOCOUNT ON;
-- Get Table Statistics (Row Count, total space used)
IF OBJECT_ID('tempdb..#TableStatistics') IS NOT NULL
    BEGIN
        DROP TABLE #TableStatistics;
    END
CREATE TABLE #TableStatistics (DatabaseName SYSNAME, SchemaName SYSNAME, TableName VARCHAR(128), TableRowCount BIGINT, TotalSpaceKB VARCHAR(20), UsedSpaceKB VARCHAR(20), UnusedSpaceKB VARCHAR(20));

EXEC sp_msforeachdb 'USE [?];
    INSERT INTO #TableStatistics
    SELECT
          ''?'' as DatabaseName
        , s.Name AS SchemaName
        , t.NAME AS TableName
        , p.rows AS TableRowCount
        , SUM(a.total_pages) * 8 AS TotalSpaceKB
        , SUM(a.used_pages) * 8 AS UsedSpaceKB
        , (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
    FROM    sys.tables AS t
        INNER JOIN sys.indexes AS i ON t.OBJECT_ID = i.object_id
        INNER JOIN sys.partitions AS p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
        LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
    --WHERE    p.rows > 0 AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255
    GROUP BY    t.Name, s.Name, p.Rows
    ORDER BY s.name,  t.name' ;

SELECT    *
FROM    #TableStatistics;

DROP TABLE #TableStatistics;


HTH,
Bulent

SQL Function QUOTENAME()

Hello all,

I support third party software databases at work. During the implementation of the application it needed to create a database to store the data. However the application created a database with a name that does not follow the rules for the format of identifiers. In my case we ended up with a database name '20120101_Survey'.

On the same server I have a custom built script that backups up all the databases at night. However, following the database creation my backup process failed and I got an alert about the process. Further looking into the problem I found out that there was a problem with the syntax. As soon as I see the statement I knew what the problem was but never thought about it while deploying the application and database. To keep the story short I used the SQL Server function called QUOTENAME() to fix the problem. Using the function in my custom script help me return the database names with delimiters so that backup process completes without error. Since then I have been using the function with all the object names. This simple function can prevent some headaches if a DBA needs to support 3rd party databases (SharePoint is also a good example because the SharePoint database names don't follow the rules of the identifier format). Here is the link to the msdn and below is simple statement that returns all the databases in your system with delimiters.

SELECT QUOTENAME(name) AS DelimitedDbName

FROM sys.databases

HTH,

Bulent