Timothy Ford

Do I Ask Too Much of My Beloved RDBMS?

2007-10-16T21:45:00Z

OK Simple-Talkers I have a interesting situation driving be nuttier than squirrel poo.

I have the following 2 steps in a scheduled SQL job I use to backup logs. If for some reason a log file does not exist it will throw the error 22049. I trap for it and want it to proceed with the remainder of the step before completing the job. The issue I have is that if any error is raised in a SQL job step you have limited options: Quit Reporting Success, Quit Reporting Failure, or Go To Step X. I need it to complete the step before making that decision. That is not one of the options. The action occurs on the first error - whether or not the error is handled gently. If any error occurs the step stops.

Do I ask too much of the RDBMS I love so?!?

--STEP 1 BACK UP LOGS

DECLARE @folder_name nvarchar(500)
DECLARE @name sysname
DECLARE @file nvarchar (1000)

DECLARE @dirtree table (subdirectory nvarchar(255), depth int)

SET @folder_name = '\\SQLBackup\Backup'

INSERT @dirtree
EXEC master.sys.xp_dirtree @folder_name

DECLARE backup_cur CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
  AND recovery_model_desc <> 'SIMPLE'
  AND DATEDIFF(day, create_date, GETDATE()) >= 1  -- Must perform 1 full backup before you can do log backups.
  AND name NOT IN
   (
   SELECT database_nm
   FROM iDBA.backups.ignore_databases
   )
ORDER BY name

OPEN backup_cur

FETCH NEXT FROM backup_cur INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @folder_name = '\\SQLBackup\Backup\' + @name

IF NOT EXISTS (SELECT 1 FROM @dirtree WHERE subdirectory = @name)
EXECUTE master.dbo.xp_create_subdir @folder_name

   SET @file = @folder_name + '\' + @name + '_tlog_' + CONVERT(NVARCHAR, GETDATE(), 112) + LEFT(REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), 4)+ '.trn'

   BACKUP LOG @name
   TO DISK = @file
   WITH RETAINDAYS = 1

INSERT iDBA.backups.db_log_backups
VALUES (@name, @file, GETDATE(), DATEADD(dd, 1, GETDATE()), NULL)

FETCH NEXT FROM backup_cur INTO @name
END

CLOSE backup_cur
DEALLOCATE backup_cur

--STEP 2 DELETE EXPIRED BACKUP FILES

BEGIN TRAN
DECLARE @backup_file nvarchar(1000)

DECLARE del_cur CURSOR FOR
  SELECT backup_full_file_nm
  FROM iDBA.backups.db_log_backups
  WHERE database_nm NOT IN ('master', 'model', 'msdb', 'tempdb')
   AND backup_expiration_dt <= GETDATE()
   AND backup_removed_dt IS NULL
  ORDER BY database_nm

OPEN del_cur

FETCH NEXT FROM del_cur INTO @backup_file

WHILE @@FETCH_STATUS = 0
  BEGIN
   BEGIN TRY
    EXEC master.sys.xp_delete_file 0, @backup_file
    PRINT CAST('Deleted File: ' + @backup_file AS nvarchar(120))
   END TRY

   BEGIN CATCH
    IF @@ERROR <> 22049
     BEGIN
      DECLARE @err_msg NVARCHAR (2000)
      DECLARE @err_sev INT
      DECLARE @err_state INT

      SELECT @err_msg = ERROR_MESSAGE() , @err_sev = ERROR_SEVERITY(), @err_state = ERROR_STATE()
      RAISERROR (@err_msg, @err_sev, @err_state)
     END
    ELSE
     BEGIN
      PRINT CAST('Missing File: ' + @backup_file AS nvarchar(120))
     END
   END CATCH

   UPDATE iDBA.backups.db_log_backups
   SET backup_removed_dt = GETDATE()
   WHERE backup_full_file_nm = @backup_file

FETCH NEXT FROM del_cur INTO @backup_file
END

CLOSE del_cur
DEALLOCATE del_cur

IF @@TRANCOUNT > 0 COMMIT TRAN

PASS Day 3: Hello Muddah, Hello Faddah

2007-09-23T17:25:00Z

Here it is, the last day of PASS 2007 and I find myself in a melancholy that comes with the last day of summer camps from many years past. We are all far past the stage of being exhausted, yet not ready to seek out the family come to collect me and take that long silent trip home. It’s been three days of breakout sessions, hands-on labs, and vendor expo visitations. For a few of us, you can add sponsor parties, volunteer training sessions, media interviews, PASS Board business meetings, stints as Ambassadors and in my case Game Show Host.

Now I find myself with some of the friends I’ve made through PASS over the years: a few Microsoft MVPs, a couple of candidates for the PASS Board, a spattering of other fellow volunteers and even a few new friends I’ve made this year, all making plans for a final dinner together. I’ve just completed my final session, this one a topic I hold great value in: Telecommuting. It was a fitting end to the week, considering I spent quite a few hours doing just that from sessions and hallways of the Colorado Convention Center. How to work effectively from anywhere in the world with the proper tools is quite easy. I’ve done it for almost eight years myself and am an advocate for telecommuting. The responsibilities of a DBA are quite suited to telecommuting.

The workload of the week is apparently getting to the best of us. E-Board Rick (yes, the same Rick from Tony Davis’ post on the horrors of PASS registration) has just wandered over with a camera. Apparently outgoing President Kevin Kline is asleep on a table around the corner. I guess after eight years he is afforded some latitude in slacking-off during the last hour of the Summit.

PASS Day Two: You Can't Get There From Here (A Geog-DUH-phy Lesson)

2007-09-21T14:23:00Z

Here it is day two and I am finally starting to get adjusted to the time difference.

Wimp.

Sure, I thought I had it bad. Two entire time zones between my native Michigan and Denver. I always lose touch with just how global PASS is.

I was attending Peter Ward's fantastic spotlight session on Engineering for the DBA and leaned over to a fellow attendee that I've become friends with through years of volunteerism (see a trend here) and had him note the time on Peter's laptop that he was using for the presentation:

9:30 am.

This was 5:30 pm. This was also my stop complaining moment.

There is some bloke named Tony from the U.K. Says he's an editor of some sort for something called Simple Talk. The U.K., for those who are not geographically savy, is a small land-locked country that borders with Nepal. It's leading export is boiled meat; and it is the only country with a period in it's name. At least that's what it said on Wikipedia. That must be about 7 hours different from the mountains of Colorado!

Then you have Gail Shaw, second-year attendee from South Africa whom I met last year when she answered a question from the audience on the SIG Quizbowl. She was back this year and was up on stage with me this year as a contestant. I am not sure how far in the future she normally lives, my brain is full so I am abstaining from math for the remainder of the week, but I am going to approximate that she is 42 hours ahead of Denver. My estimate may be a little high or low, but I don't care any longer.

There are so many others: Johan and Christoph from Germany (another country bordering Nepal according to Wiki), Greg from Australia, Louis from North Carolina (I believe that is Puerto Rico), Dan from the exotic island of Seattle.

These are some of the people you're in sessions with from 0800 - 1800. These are the friends you make from years of participation. These are the fellow SQL professionals, Microsoft MVPs, and Speakers that you are out "brainstorming" with until 0200 after those long days in classes and meetings.

You learn a great deal about SQL Server and the platforms it runs on from these long days in class. Just as valuable are those late nights/early mornings out at the vendor parties and pub crawls. That's when you learn the politics of people in our industry, you collaborate on problems you're having back at work and perhaps find a new perspective on getting your job done. You may even learn just how much Jagermeister a DBA can consume before he'll get on stage at a Karoke bar. Most-importantly, you realize you're not alone in your experiences.

Sure, after clocking subsequent 17 hour days I am tired. But it is a good tired.

It's PASS 2007 Arg!

2007-09-19T22:39:00Z

Volunteer meetings, PASS business, PASS SIG Quizbowl, Welcome Reception, SQL Server Central Party and the subsequent after party and after-after party, General Session and four breakout sessions and man is my brain tired!

It's all just a part of being at PASS and being involved in PASS. Sure it is a great deal of work, but it is also a great deal of fun. It was brought up in our day-long volunteer meeting yesterday that you may be an attendee, but until you volunteer in some level, you don't really feel like a member of the community. From someone who has been a volunteer since their first summit in January 2002 it is hard to explain what that sense of community feels like. It's akin to explaining a sunset to a blind person. Well, maybe I am prone to hyperbole.

The general session provided a nice inside look at some of the upcoming features of (surprise) SQL Server 2008. It was also noted that there will be no price increase for 2008 versus 2005.

After the general session it was time to make those hard choices that come with wanting to be in two or more places at a time: which sessions to take? The good news with PASS 2007 is that each session is being recorded and attendees will be able to view them online at a later time on demand. On that note I chose the following sessions:

Notification Services, by Joe Webb

Optimizing High Performance SQL Systems Using Lessons Learned From Customer Deployments (A Microsoft-presented session that used MySpace as the basis of the discussion)

Database Recovery For The Paranoid DBA, by James Luetkehoelter (always an engaging speaker.)

Disk Configuration for the Microsoft SQL Server DBA, by Allan Hirt

Now it's on to the vendor exposition to pimp myself out for swag. The kids need more tee-shirts to sleep in!

Oh, James L. reminded us that it was International Talk Like A Pirate Day! (Wow, hard to believe it's been a year since the last one.) So on that note let me close by saying

Gar Maytee!