Wait for me!

I was given a situation at work a couple of days ago where we had a conflict between SSIS jobs.  There are two jobs in SQL Agent that get called by an external process.  They both insert to a single, specific table.  If they fire at the same time, they deadlock.  So how do we keep the two from stepping on each other?  That was my task, so I wrote the following proc in response.  You can insert it at the beginning of the job, using the other job name as the @job_name parameter.

CREATE PROCEDURE dbo.[WaitForJob]
/****************************************************************
NAME:
 WaitForJob

DESCRIPTION:
 This is used as a delay step. If you have two jobs that should
 not run at the same time, you can use this to check to see if
 the interfering job is running before continuing.  Like waiting
 for a backup to complete before running a reindex.

DEPENDENCIES:
 XP_SQLAGENT_ENUM_JOBS - this is an undocumented stored proc in
 the master database.  As such, this script is not necessarily
 going to work on your version of SQL.  It was written on SQL
 2005, though it may work on other versions.  Use at your own
 risk. 

TEST EXEC:

EXEC dbo.WaitForJob
 @job_name = 'Backup All Databases',
 @job_owner = 'sa',
 @delay_interval = '00:01:00'

TO DO LIST:
 1. Nothing yet - pending feedback.

NOTES: 
 Please send any comments or suggestions to dmmaxwell(at)gmail.com.

*****************************************************************
AUTHOR           EXT.    DATE            REASON FOR CHANGE
-----------------------------------------------------------------
David Maxwell    61142   2009.01.26      Initial version.
****************************************************************/
 @job_name varchar(200) = NULL,  -- No job name = FAIL.
 @job_owner varchar(30) = 'sa', -- The job owner.
 @delay_interval char(8) = '00:00:30' -- Delay between checks.
AS

SET NOCOUNT ON

-- We'll be passing this to xp_sqlagent_enum_jobs
DECLARE 
 @job_guid uniqueidentifier,
 @ErrorMessage varchar(4000)

-- No errors yet...
SET @ErrorMessage = (SELECT NULL)

--Validate the input first.
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs
   WHERE [name] = @job_name) = 0
 BEGIN
   SET @ErrorMessage = (SELECT
   'Job name invalid.  Please supply a valid job name.'
   )
   GOTO EXITPROC
 END

-- Drop the temp table, if it already exists.
IF (SELECT OBJECT_ID('tempdb.dbo.#TempJobs')) IS NOT NULL
DROP TABLE #TempJobs

-- Create the temp table for holding the xp_sqlagent_enum_jobs
-- results.
CREATE TABLE #TempJobs (
 JobID int null,
 LastRunDate int null,
 LastRunTime int null,
 NextRunDate int null,
 NextRunTime int null,
 NextSchedule int null,
 Requested bit null,
 ReqSource  int null,
 ReqSourceID varchar(100) null,
 Running bit null,
 CurrentStep  int null,
 CurrentRetry int null,
 JobState int null
 ) 

-- Get the job GUID.
SELECT @job_guid = job_id
FROM msdb.dbo.sysjobs
WHERE name = @job_name

-- Get the info for that job, and insert into the temp table.  
INSERT INTO #TempJobs
EXEC master..xp_sqlagent_enum_jobs 1, @job_owner, @job_guid

IF (SELECT COUNT(*) FROM #TempJobs) IS NULL
 BEGIN
   SET @ErrorMessage = (
   SELECT 'Error getting initial job information.'
   )
   GOTO EXITPROC
 END

---- To test, uncomment this section.
-- IF (SELECT Running FROM #TempJobs) = 1
--   BEGIN
--     PRINT 'Job Is Running.'
--   END
-- ELSE 
--   PRINT 'Job Is Not Running.'
-- GOTO EXITPROC

-- Now start checking every @delay_interval.
-- For testing, comment this section out, and uncomment
-- the one above.
WHILE (SELECT Running FROM #TempJobs) = 1
BEGIN
  TRUNCATE TABLE #TempJobs
  WAITFOR DELAY @delay_interval 
BEGIN TRY
  INSERT INTO #TempJobs
  EXEC master..xp_sqlagent_enum_jobs 1, @job_owner, @job_guid
END TRY
BEGIN CATCH
  SET @ErrorMessage = (SELECT
  'Error during job check loop.' +
  CHAR(13) + ERROR_MESSAGE() + CHAR(13))
  GOTO EXITPROC
END CATCH
END

EXITPROC:
IF (SELECT @ErrorMessage) IS NOT NULL
BEGIN
 RAISERROR(@ErrorMessage,16,1)
 RETURN(-1)
END
ELSE
RETURN(0)

Where'd that backup go?

A friend IM'd me today and mentioned that he was having an issue with a backup. It appeared that the backup ran, but he was unable to find the backup file. I have a 'quickie' backup history script that I use for telling me when stuff was backed up, but it doesn't mention where it was backed up to, since there's some MAX() and GROUP BY going on, and using individual file names would obviate that.

Here's the 'When' version:

SELECT
  SERVERPROPERTY('ServerName') AS ServerName,
  a.Name AS DBName,
  databasepropertyex(a.Name, 'Recovery'),
  CASE b.type
    WHEN 'L' THEN 'Tranlog'
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
  END AS BackupType,
  b.[User_Name],
  MAX(b.Backup_Finish_Date) AS LastBackup
FROM Master.dbo.sysdatabases a
INNER JOIN msdb.dbo.backupset b
  ON a.Name = b.Database_Name
WHERE b.Backup_Finish_Date > (DATEADD(dd,-14,getdate()))
GROUP BY a.Name, b.Type, b.[User_Name]
ORDER BY a.Name ASC, LastBackup DESC

Using that he saw that the backup did indeed run, and was successful. But where was that backup file at?

This found it:

SELECT
  a.Name as DBName,
  CASE b.type
    WHEN 'L' THEN 'Tranlog'
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
  END AS BackupType,
  b.[User_Name],
  b.Backup_Finish_Date,
  f.physical_device_name
FROM Master.dbo.sysdatabases a
INNER JOIN msdb.dbo.backupset b
  ON a.Name = b.Database_Name
INNER JOIN msdb.dbo.backupmediafamily f
  ON b.media_set_id = f.media_set_id
WHERE b.backup_finish_date > getdate() - 7
  AND a.Name = 'MyDB' -- Replace with your DB name.   
ORDER BY b.backup_finish_date DESC  

Enjoy.

-D.

CbusPASS

I attended the first meeting of the new Columbus PASS chapter last night.  Seems like a good group of people so far.  There were a few there I recognized, and a couple of people who's employers I recognized, as well.  It was a good primer into what the PASS community is up to and what to expect.  I have a few new weblinks to add to my regular checklist and RSS feeds.  Most notably the main PASS site: http://www.sqlpass.org, and the local chapter, http://columbus.sqlpass.org.  You can even get paid for user submitted content, so I think I might have to work up an article or two.  Maybe write some fun scripts.

The big yearly PASS Summit meeting is in Seattle WA this year, and early registration is anywhere from $1500 to $2500.  Don't think I'll be attending, but I will keep my eye open for the content to come out of it.  Sounds like it's a pretty in-depth event.  Last night's meeting didn't have much technical content.  The group meets monthly, and each meeting has a specific technical topic. I'll be interested to see what the next one is.

Quick Comment Policy Update.

Just a quick note on the comment policy. No spam. Any comment pushing a for-profit product will be rejected. Feel free to comment on the content, but keep the advertising to yourself.

Thanks.

-D.

iGoogle. Do you?

I've been playing with iGoogle for a couple of weeks now, and I'm pretty pleased with the results. I've lamented before about not having an adequate planner / project dashboard solution, and I think iGoogle may just fit the bill. I'm using it in conjunction with CreattivePro Office, which is a free project management tool. It's working out well so far.

In orther news, I am also learning a few ways to get around some productivity blocks at the current client. I think with the new tools I have in place, I will be able to get a better handle on things. I can't divulge any details at the moment, but I do want to make a quick point about internet filtering.

What I have noticed, both in my own experience, and the experience of others is that blocking internet sites does not really increase productivity in the workplace. I made this point to a co-worker of mine the other night and he seemed to agree, so there. I'm validated now. What I have seen, is that instead of the average employee spending 5 minutes checking twitter or something like that. They spend more time trying to figure out a way to get around that block. Or, they just find other outlets, work slower due to lower morale, etc. When you filter the internet, you're not going to make unmotivated people work any harder, and all you're really going to do is annoy people.

Now I do think that internet monitoring is a good idea. There's a difference between someone who spends 5 minutes on Google News, and someone who is averaging a click every 2 minutes for an hour or so. That really is lost productivity. But why penalize everyone else? Can the slacker, and let the rest of the folks do their thing and get work done.

Anyway, rant over. Time to do some minor housekeeping on my newly customized page, and then get to bed. Going to shoot for an early workout tomorrow. Wish me luck.