SQL Server: Dynamically Check if SQL Agent Job is Running

It can be helpful to have a SQL script to determine if a SQL Agent job is currently running or not. In SSMS this check can be done using the tools and GUIs provided, but it may be necessary to embed this check in script.  For example you may have a series of scripts, some scheduled through jobs and some triggered by, say, a button click on an interface.  If you need to ensure that the button job does not run at the same time as the Agent Job, you'll need to dynamically check the agent job status. Here's how to do it.

First let's create a simple SQL Agent job with two steps:

In the above image I have created a new job called TestDelay, containing two steps, WAIT 1 and WAIT 2.  The first step simply waits for 20 seconds before completing successfully, while the second step waits for a further 5 seconds before doing the same.

The information relating to this job and it's execution is contained in the following tables in the msdb database:

dbo.sysjobs
dbo.sysjobsteps
dbo.sysjobactivity

From the first table we can find out the job_id, which is key for joining the 3 tables together:

SELECT job_id FROM msdb.dbo.sysjobs

WHERE name LIKE 'TestDelay'

Then from the second table we can find out "step" information:

SELECT step_id, step_name, subsystem

FROM msdb.dbo.sysjobsteps

WHERE job_id = (SELECT job_id FROM MSDB.dbo.sysjobs

                        WHERE name LIKE 'TestDelay')

step_id     step_name     subsystem
----------- ---------------------------
1           WAIT 1        TSQL
2           WAIT 2        TSQL

(2 row(s) affected)

Here we can see the two steps in our job and their step_ids. Using the third table we can run the following query for our job:

SELECT     start_execution_date,

           last_executed_step_id,

           last_executed_step_date

FROM       msdb.dbo.sysjobactivity a

INNER JOIN msdb.dbo.sysjobs j

ON   j.job_id = a.job_id

WHERE      j.name LIKE 'TestDelay'

start_execution_date    last_executed_step_id last_executed_step_date
----------------------- --------------------- -----------------------
2014-04-28 15:20:23.000 2                     2014-04-28 15:20:43.000

(1 row(s) affected)

We can see that on it's most recent execution, the job finished it's second and final step at 15:20:43. If the job were still running and had not completed even the first step yet,  then the last_executed_step_id and last_executed_step_date fields would be null for this row.
If, however, the first step has completed but the second has not, the two fields will be populated but the step_id will be 1, not 2.

So to know if our job is still running we really need to combine these three tables into a single query, checking whether the last_execution_step_id field is null, and if not, if it is at it's maximum value or not.

SELECT DISTINCT

            start_execution_date,

            last_executed_step_id,

            last_executed_step_date,

CASE  WHEN  last_executed_step_id = MAX(step_id) THEN 'Job Complete'    

      ELSE  'Job Still Running'

END         AS result

FROM        msdb.dbo.sysjobactivity a

INNER JOIN  msdb.dbo.sysjobsteps s

      ON    a.job_id=s.job_id

INNER JOIN  msdb.dbo.sysjobs j

      ON    j.job_id = a.job_id

WHERE       j.name LIKE 'TestDelay'

GROUP BY    start_execution_date,

            last_executed_step_id,

            last_executed_step_date

In the above query, the "result" field will say either "Job Still Running" or "Job Complete" depending on the logic stated above.  Alternatively the script could be rewritten in the form of a stored procedure that accepts a parameter containing the job name and returns a 1/0 depending on whether it is running or not.

It is worth examining the rest of the fields in these three tables as they can offer additional options for extracting information about the jobs set up on the server.

Using Rank() to Remove Duplicates

In a recent post on SQL Server Window Functions I looked at "scoring" rows of data based on the value of specified columns.

The same approach can be used to help cleanse data prior to, or as part of ETL processes.  Data issues should be corrected at source, but in the real world this isn't always possible.  The source may be external, there may be issues of data ownership, or there simply may not be time to carry out the process.

It often falls to the SQL Developer to handle these issues. Here I will look at handling the issue of two records in a table for the same entity but with slightly different spelling (I refer to this as  "duplicate" records, though the records are not exact copies).

I've encountered this problem several times at different organisations.  If the two records were identical, it would simply be a case of using a SELECT DISTINCT. You might also consider imposing a unique constraint to ensure there are no actual duplicates.  But the spelling difference means that they are regarded as two completely different entities by the code.

When presenting this to business users, the response has often been "Just take the first one".  But how do you actually just take the first one? And first one based on what exactly?

First let's create some data:   

CREATE TABLE Stones

(

ID          INT IDENTITY(1,1),

FirstName   VARCHAR(10),

LastName    VARCHAR(20)

)

INSERT INTO Stones(FirstName, LastName) 

VALUES ('Mick','Jagger')

INSERT INTO Stones (FirstName, LastName) 

VALUES ('Michael','Jagger')

INSERT INTO Stones(FirstName, LastName) 

VALUES ('Kieth','Richards')

INSERT INTO Stones(FirstName, LastName) 

VALUES ('Ronnie','Wood')

INSERT INTO Stones(FirstName, LastName) 

VALUES ('Charlie','Watts')

SELECT * FROM Stones

ID          FirstName  LastName

----------- ---------- --------------------

1           Mick       Jagger

2           Michael    Jagger

3           Kieth      Richards

4           Ronnie     Wood

5           Charlie    Watts

(5 row(s) affected)

Note IDs 1 and 2 for Mick and Michael Jagger.  These are actually the same person, and we only want to pick up the first one.  We only want 4 rows in our result.

We can use the RANK() function to now score these records, partitioning by LastName (see here for more on how to do this):

  SELECT ID, 

       FirstName, 

       LastName,

       RANK() OVER (PARTITION BY LastName ORDER BY ID ASC) AS Score

  FROM Stones ORDER BY 1

ID          FirstName  LastName             Score

----------- ---------- -------------------- --------------------

1           Mick       Jagger               1

2           Michael    Jagger               2

3           Kieth      Richards             1

4           Ronnie     Wood                 1

5           Charlie    Watts                1

(5 row(s) affected)

I have ordered by ID, on the assumption that "Take the first one" means the first one to enter the table (the seeding of the table I have therefore used as the indicator of "first". If there are timestamps, they can be used instead.) 

Examining the results of the query above shows that we now only require records with a score of 1.  It is then quite a simple process to use the above code in a sub-query with a WHERE clause to restrict to the rows we want:

SELECT * FROM

(

  SELECT ID, 

       FirstName, 

       LastName,

       RANK() OVER (PARTITION BY LastName ORDER BY ID ASC) AS Score

  FROM Stones

)a

WHERE Score = 1

ID          FirstName  LastName             Score

----------- ---------- -------------------- --------------------

1           Mick       Jagger               1

3           Kieth      Richards             1

5           Charlie    Watts                1

4           Ronnie     Wood                 1

(4 row(s) affected)

We are now left only with the four rows we want. All duplicates have been excluded.

The database principal owns a schema in the database, and cannot be dropped (Error: 15138)

On a recent audit of a database I noticed that there were a lot of logins for a database that were not required.  I set about removing the logins one by one, but soon hit the following error:

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

Well this threw me a bit, and I could not figure out what was going on and why, this one particular login could not be removed.  As the message indicates, this account owns a schema on the DB. So I opened the user properties from SSMS and sure enough, the user  in question owned three schemas: db_datareader, db_datawriter, db_owner.  However, SSMS will not simply allow you to unselect the checkboxes here, so it is still not so easy to remove this link and allow the user to be deleted!

The schemas owned by the user can also be displayed by running the following query, within the context of the database under scrutiny:

SELECT s.name FROM sys.schemas s
WHERE s.principal_id = USER_ID('UserInQuestion'');

For a more complete picture of all schemas and users associated with the database:

SELECT *, USER_NAME(principal_id) as username
FROM sys.schemas;


In order to remove this user we have to first transfer ownership of these schemas to a different user.  Typically this would be dbo.  This has to be done once for each schema owned, using the ALTER AUTHORISATION ON SCHEMA command:

 ALTER AUTHORISATION ON SCHEMA::db_datareader TO dbo
 ALTER AUTHORISATION ON SCHEMA::db_datawriter TO dbo
 ALTER AUTHORISATION ON SCHEMA::db_owner TO dbo

(note the double colon!)

Re-run the previous scripts to ensure that the user in question no longer owns any schemas.  Now if we again try the delete again, this user will be removed successfully.

Random Data Using SQL Default Values

Wow, I decided to take a bit of time off over the winter and the next thing I know it's been two months since the last  SQL Banana update!

While faffing around over the last few weeks I came across a few code snippets I found quite useful.  Here's the first :)

Often I need to fill a table with some random dummy data.  Lot's of people have different ways of doing this, and I don't want to get into a discussion of what "random" actually means when working with computers, but 99% of the time this approach works just fine for me.

First create your table.  Set a default constraint to use the NEWID() function - this will generate a uniqueidentifier value.  The value will be unique every time it is generated.  Then use the CHECKSUM() function around it to compute it's hash value,  for our purposes effectively generating an int based on the uniqueidentifier value.

CREATE TABLE dbo.Dummy
(
Id INT IDENTITY (1,1) PRIMARY KEY,
IntData INT CONSTRAINT DummyDefault DEFAULT CHECKSUM(NEWID()))

Next, insert as much row data as required:

INSERT INTO dbo.Dummy DEFAULT VALUES
GO 500

This will run the insert 500 times, each time the identity column will increment, and a random integer will be inserted into the IntData field.

If you require varchar data then simply adjust the default constraint as required. EG:

CharData VARCHAR(MAX) CONSTRAINT TestCharDefault DEFAULT CAST(NEWID() as VARCHAR(MAX)) 

And that's it. Dummy data is now ready for use.

SQL Server Global Variables: @@Rowcount, @@Error and more

There are lots of SQL Server global variables (prefixed with @@), but I thought I'd list a few that I have found particularly useful in the past.  I often find I need to make use of them to gather data when I first go on client sites:

@@ROWCOUNT

Stores the number of rows affected by the last command.  I find myself using this variable all the time for logging.  For example, if I have a stored procedure inserting/updating/deleting data from a table, I use this variable to store the results of each of those commands in a log table.  This makes debugging much easier and any spikes or drops in records can be captured and easily viewed.  A word of warning though - this variable only stores the rowcount for the last command.  If you find that you are not storing the correct number, it is likely you have another line of code executing before you are reading this variable.

@@ERROR

Stores the error code for the immediately previous command.  I use this most often in conjunction with the @@ROWCOUNT variable, and store them together for logging/debugging purposes.  Note that if you want to log both of them for the same line of code, you need to make sure to write them both to the output in a single command. If you don't do this, then whichever variable's value is written second, will contain the result from the writing of the first variable, and not the actual insert/update/delete that is of concern.

@@SPID

Stores the session ID.  Again, my advice would be to log it with the previous two values.  If you have a number of sessions performing a range of activities, the log table will be confusing without being able to identify which sessions are doing what.

@@TRANCOUNT

Stores the number of currently open transactions.  I often perform a check using this variable in my CATCH block to determine if we have hit an error inside a transaction and left it open : IF (@@TRANCOUNT > 0) ROLLBACK

@@CPU_BUSY, @@IDLE, @@IO_BUSY

These ones are useful for gathering stats on the SQL box.  They store the number of "ticks" that the CPU has been busy doing SQL Server activities, the amount of ticks SQL Server has been idle and the number of ticks SQL Server has spent doing IO.  All are measured since the last time SQL Server was started.

@@TIMETICKS

The number of microseconds per tick - this helps to transform the previous stats into understandable time :)

There are many other global variables available in SQL Server, but this is simply meant to be a list of the ones I find myself using most often.

SQL Server Window Functions: RANK, DENSE_RANK, NTILE, ROW_NUMBER

Recently I have been finding it more and more useful to work with Window functions. These are sometimes referred to Analytic functions or more commonly Ranking functions:

RANK
DENSE_RANK
NTILE
ROW_NUMBER

These functions essentially "score" each row that is returned by the select statement.  So for example if we look at the Sales.SalesTerritory table in AdventureWorks and run the following query:

SELECT

t.Name,

t.CountryRegionCode,

t.[Group],

t.SalesYTD,

RANK() OVER (ORDER BY SalesYTD ) AS Ranked

FROM sales.SalesTerritory t

This results in the final column contain the "rank" of the record (ascending order), ie if it is the second lowest SalesYTD this column will contain a two. 

These functions become much more powerful, however, when using the optional PARTITION BY clause that can be stipulated.  Using this clause we can "subrank" within partitions: 

SELECT t.Name,

t.CountryRegionCode,

t.[Group],

t.SalesYTD,

RANK() OVER (PARTITION BY [Group] ORDER BY SalesYTD) AS SubRank,

RANK() OVER (ORDER BY SalesYTD) AS OverallRank

FROM sales.SalesTerritory t

The results of this query display each record, ranked within its Group (Europe, North America, Pacific):

Name CountryRegionCode Group    SalesYTD      SubRank OverallRank

-------------------------------------------------- ----------------- ---Northeast      US North America 2402176.8476  1       1

Southeast      US North America 2538667.2515  2       2

Central        US North America 3072175.118   3       3

Germany        DE Europe        3805202.3478  1       4

France         FR Europe        4772398.3078  2       5

United Kingdom GB Europe        5012905.3656  3       6

Australia      AU Pacific       5977814.9154  1       7

Canada         CA North America 6771829.1376  4       8

Northwest      US North America 7887186.7882  5       9

Southwest      US North America 10510853.8739 6       10

(10 row(s) affected)

This makes it very easy for us to produce reports that return, say, only the top performers in each region.

These functions avoid the need to write subqueries or CTEs to produce their results and are therefore highly efficient.  More details on these four functions can be found on technet here.

In the next post we'll look at additional lesser known window functions within SQL Server.

Truncate vs Delete - Myth vs Reality

I have been asked many times (often in interviews) the following question:

Q. What is the difference between Truncate and Delete in SQL Server?

The answer that, in my experience, is most expected and most often given is that Delete allows you roll back the data you have wiped, while Truncate does not - Truncate is irreversible.  A quick search on google gives a number of posts that say as much.

Simple.  But wrong!  Just try this simple test.

1. Insert records into a table. 
2. Open a transaction. 
3. Truncate the table. 
4. View the contents of the table.
5. Rollback the transaction.
6. View the contents of the table.

 The code below is a very simple example:

CREATE TABLE MyTable (col1 CHAR(1), col2 INT)

GO

INSERT INTO MyTable (col1, col2)

SELECT 'a', 1 UNION ALL SELECT 'b', 2 UNION ALL SELECT 'c', 3

GO

SELECT * FROM MyTable --(3 row(s) affected)

BEGIN TRAN

TRUNCATE TABLE MyTable

GO

SELECT * FROM MyTable --(0 row(s) affected)

ROLLBACK

SELECT * FROM MyTable --(3 row(s) affected)

Before the transaction is rolled back, there are no rows in the table, but after the rollback all three records have returned, despite having used Truncate to remove them.  Clearly Truncate is reversible!

So what actually is the difference between Truncate and Delete?  There are a number of general differences, but the relevant one here is the following:

When rows are DELETE'd the operation is logged for each row removed. TRUNCATE logs only the deallocation of the data pages rather than the rows themselves, making it a much faster operation.  

There are many other differences related to permission levels, seeding, triggers, contraints etc. That's for a future post.  For now, the point to note is that TRUNCATE can indeed be rolled back, and the myth has been busted!