March 10, 2009 at 1:58 pm
Hi All,
I wanted to see if there was a way to, in TSQL, check if a specific job is running and if it is then wait for it to complete between a loop issuing delete statements?
The problem I'm having is that the job in question runs in 15 min intervals and whenever I'm doing deletes on one of its source tables the job fails. I’ve tried using the WITH(RowLock) and also tried doing a 30 sec waitfor with limited success. Below is the code from the SP I'm running. Any suggestions are greatly appreciated.
DECLARE
@FacilityName VARCHAR(50),
@Message VARCHAR(2000),
@ProcName VARCHAR(50),
@ImportStart DATETIME,
@ImportEnd DATETIME,
@JobDuration INT,
@Error INT,
@debug BIT,
@server VARCHAR(128),
@Subject VARCHAR(100),
@TestOnly BIT,
@Notify BIT,
@Success BIT,
@RecordsInserted INT,
@RecordsDeleted INT,
@RecordsUpdated INT,
@DestinationTable VARCHAR(128)
----------------------------------------------------
-- Declare local variables unique to this sproc --
----------------------------------------------------
DECLARE @comparison_date INT, -- compares @limit_date to ensure it is GT 6 months ago
@month_id CHAR(2), -- months less than 10 get a 0 pre-pended
@day_id CHAR(2), -- days less than 10 get a 0 pre-pended
@max_month_day INT, -- per-month within the cursor, the last day of that month
@table_name VARCHAR(100), -- the table that is being BCP'd out
@date_column VARCHAR(50), -- the table's date column
@table_min_date INT, -- the minimum date id in the current table
@year_month CHAR(6), -- The year and month in date_id format, will prefix date_ids
@i INT, -- Loop counter of the days of the month during the delete
@dyn_sql NVARCHAR(4000) -- Create #tmp tables to be used by the inner cursor
-- Initialize local variables
SET @debug = 0
SET @comparison_date = 29991231
-- The run of this sproc is for all tables
IF @gl_tbl_name IS NULL
BEGIN
-- Get all of the table names into a cursor for the purpose of
-- finding the minimum date of all tables. This will be the month/year that
-- is deleted
DECLARE crs_table_list CURSOR FAST_FORWARD
FOR
SELECT table_name,
date_column
FROM EDWStore.dbo.ctl_archive_tables
OPEN crs_table_list
FETCH NEXT FROM crs_table_list INTO
@table_name, @date_column
WHILE @@FETCH_STATUS = 0
BEGIN
IF @debug = 1
BEGIN
PRINT 'Starting discovery of minimum date in all tables'
END
SET @dyn_sql =
'SELECT @table_min_date = MIN( ' + @date_column + ')
FROM EDWStore.dbo.' + @table_name + ' WITH(nolock)
-- We use site_id b/c most indexes are based off of site_id
WHERE site_id IN (1, 11)'
IF @debug = 1
BEGIN
PRINT @dyn_sql
END
EXEC sp_executesql @query = @dyn_sql,
@params = N'@table_min_date INT OUTPUT',
@table_min_date = @table_min_date OUTPUT
IF @debug = 1
BEGIN
PRINT CAST(@table_min_date AS VARCHAR) +
' is the ' + @table_name + ' table''s min date'
END
-- @comparison_date gets assigned the earliest date that is found
IF @table_min_date < @comparison_date
BEGIN
SET @comparison_date = @table_min_date
END
FETCH NEXT FROM crs_table_list INTO
@table_name, @date_column
END
CLOSE crs_table_list
DEALLOCATE crs_table_list
PRINT CAST(@comparison_date AS CHAR(8)) + ' is the minimum date of all tables.'
-- The minimum date of all tables is found. Now that we have the month/year
-- lets find that month's maximum day value and assign it to a variable
SELECT @max_month_day = cadt.day_of_month
FROM EDWDatamart.dbo.dim_calendar_date cadt
WHERE cadt.last_day_of_month_flag = 1
AND cadt.year = LEFT(@comparison_date, 4)
AND cadt.month = RIGHT(LEFT(@comparison_date, 6), 2)
-- Setup the year + month in a date_id format - the prefix of the dates
SET @month_id = RIGHT(LEFT(@comparison_date, 6), 2)
SET @year_month = LEFT(@comparison_date, 4) + @month_id
-- Now start the deletes
DECLARE crs_table_list CURSOR FAST_FORWARD
FOR
SELECT table_name,
date_column
FROM EDWStore.dbo.ctl_archive_tables
OPEN crs_table_list
FETCH NEXT FROM crs_table_list INTO
@table_name, @date_column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i = 1
IF @debug = 1
BEGIN
PRINT 'Starting delete in all tables'
END
WHILE @i < (@max_month_day + 1)
BEGIN
SET @day_id = CASE
WHEN @i < 10
THEN '0' + CONVERT(CHAR(1), @i)
ELSE
CAST(@i AS CHAR(2))
END
SET @dyn_sql =
'DELETE FROM EDWStore.dbo.' + @table_name + ' WITH(Rowlock)
WHERE ' + @date_column + ' = ' + @year_month + @day_id
PRINT @dyn_sql
IF @print_only = 0
BEGIN
EXEC sp_executesql @dyn_sql
END
SET @i = @i + 1
-- Pause 3 seconds between deletes
IF @print_only = 0
BEGIN
WAITFOR DELAY '000:00:30'
-- Issue a CHKPT every 10th day to save TLog Space
IF @i > 9 AND @i%10 = 0
BEGIN
CHECKPOINT
END
END
END
FETCH NEXT FROM crs_table_list INTO
@table_name, @date_column
END
CLOSE crs_table_list
DEALLOCATE crs_table_list
END
-- The run of this sproc is for a specific table only
ELSE
BEGIN
SET @table_name = NULL
-- Ensure that this table is one that we archive
SELECT @table_name = table_name,
@date_column = date_column
FROM EDWStore.dbo.ctl_archive_tables
WHERE table_name = @gl_tbl_name
IF @table_name IS NULL
BEGIN
PRINT 'The table name supplied as not listed in EDWStore.dbo.ctl_archive_tables. Process halted.'
RETURN
END
SET @dyn_sql =
'SELECT @table_min_date = MIN( ' + @date_column + ')
FROM EDWStore.dbo.' + @table_name + ' WITH(nolock)
-- We use site_id b/c most indexes are based off of site_id
WHERE site_id IN (1, 11)'
IF @debug = 1
BEGIN
PRINT @dyn_sql
END
EXEC sp_executesql @query = @dyn_sql,
@params = N'@table_min_date INT OUTPUT',
@table_min_date = @table_min_date OUTPUT
IF @debug = 1
BEGIN
PRINT CAST(@table_min_date AS VARCHAR) +
' is the ' + @table_name + ' table''s min date'
END
SET @comparison_date = @table_min_date
-- The minimum date of this table is found. Now that we have the month/year
-- lets find that month's maximum day value and assign it to a variable
SELECT @max_month_day = cadt.day_of_month
FROM EDWDatamart.dbo.dim_calendar_date cadt
WHERE cadt.last_day_of_month_flag = 1
AND cadt.year = LEFT(@comparison_date, 4)
AND cadt.month = RIGHT(LEFT(@comparison_date, 6), 2)
-- Setup the year + month in a date_id format - the prefix of the dates
SET @month_id = RIGHT(LEFT(@comparison_date, 6), 2)
SET @year_month = LEFT(@comparison_date, 4) + @month_id
SET @i = 1
IF @debug = 1
BEGIN
PRINT 'Starting delete in all tables'
END
WHILE @i < (@max_month_day + 1)
BEGIN
SET @day_id = CASE
WHEN @i < 10
THEN '0' + CONVERT(CHAR(1), @i)
ELSE
CAST(@i AS CHAR(2))
END
SET @dyn_sql =
'DELETE FROM EDWStore.dbo.' + @table_name + ' WITH (rowlock)
WHERE ' + @date_column + ' = ' + @year_month + @day_id
PRINT @dyn_sql
IF @print_only = 0
BEGIN
EXEC sp_executesql @dyn_sql
END
SET @i = @i + 1
-- Pause 3 seconds between deletes
WAITFOR DELAY '000:00:30'
END
END
March 10, 2009 at 2:15 pm
All the jobs data is stored in msdb. You should be able to get what you need from there.
You don't need to put "with(rowlock)" on deletes. So far as I know, it doesn't actually do anything to do so.
It looks to me like this proc is intended to delete everything older than X date from all tables in the database. That's from a cursory glance at it. Is that what it does?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 6:52 am
AS I look at your code, I'm wondering if the issue isn't a long running transaction. I'm not sure how SQL Server handles transactions when using dynamic SQL and sp_executesql, but I wonder if you are getting one big transaction and if you might be better off issuing a BEGIN TRANSACTION before the delete and COMMIT TRANSACTION after to make for shorter transactions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2009 at 9:15 am
Thanks for the replies. I've been going through the tables in views and was thinking I could check sysjobactivity table to see if the current time is between the start_execution_date and stop_execution_date for the job in question. If it is then wait a few min and if it's not then go ahead with the delete. I'm just not too sure how current the sysjobactivity table is. Or the more I look at this table I could just check if the stop_execution_date is NULL and then delete or wait based off of that.
As far as the BEGIN/COMMIT TRANSACTION goes should this be a part of the dynamic SQL or should it be like the code below? I’m not too familiar with dynamic SQL either.
IF @print_only = 0
BEGIN
BEGIN TRANSACTION
EXEC sp_executesql @dyn_sql
COMMIT TRANSACTION
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply