Backup Restore Chain (tested in SQL 2008R2)

This is based on the script created by SQLSoldier (Day 3 of 31 Days of Disaster Recovery: Determining Files to Restore Database) but with slight modifications as it did not correctly display the order to restore in for SQL2008R2.

Comments welcome!  Good or bad.

If you test this on different versions, it would be great if you would comment which versions this works on.

Lines with my changes noted by --Anders

/****************
Created by @SQLSoldier
http://www.sqlsoldier.com/wp/sqlserver/day3of31daysofdisasterrecoverydeterminingfilestorestoredatabase

Modified by Anders Pedersen @arrowdrive
Made to work with SQL 2008R2.

2015-09-24
*****************/DECLARE @DBName SYSNAME,
   @DBBackupLSN numeric(25, 0);

DECLARE @Baks TABLE (
   BakID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   backup_set_id INT NOT NULL,
   media_set_id INT NOT NULL,
   first_family_number tinyint NOT NULL,
   last_family_number tinyint NOT NULL,
   first_lsn numeric(25, 0) NULL,
   last_lsn numeric(25, 0) NULL,
   database_backup_lsn numeric(25, 0) NULL,
   backup_finish_date DATETIME NULL,
   TYPE CHAR(1) NULL,
   family_sequence_number tinyint NOT NULL,
   physical_device_name NVARCHAR(260) NOT NULL,
   device_type tinyint NULL,
   checkpoint_LSN numeric (25,0)-- Anders
   )SET NOCOUNT ON;
-- Set the name of the database you want to restoreSET @DBName = N'AndersTest';
-- Get the most recent full backup with all backup filesINSERT INTO @Baks (backup_set_id,
   media_set_id,
   first_family_number,
   last_family_number,
   first_lsn,
   last_lsn,
   database_backup_lsn,
   backup_finish_date,
   TYPE,
   family_sequence_number,
   physical_device_name,
   device_type
   ,checkpoint_LSN)-- AndersSELECT TOP(1) WITH Ties B.backup_set_id,
   B.media_set_id,
   B.first_family_number,
   B.last_family_number,
   B.first_lsn,
   B.last_lsn,
   B.database_backup_lsn,
   B.backup_finish_date,
   B.TYPE,
   BF.family_sequence_number,
   BF.physical_device_name,
   BF.device_type
   , B.checkpoint_lsn -- AndersFROM msdb.dbo.backupset AS BINNER JOIN msdb.dbo.backupmediafamily AS BF
   ON BF.media_set_id = B.media_set_id
       AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_numberWHERE B.database_name = @DBNameAND B.is_copy_only = 0AND B.TYPE = 'D'AND BF.physical_device_name NOT IN ('Nul', 'Nul:')ORDER BY backup_finish_date DESC, backup_set_id;
-- Get the lsn that the differential backups, if any, will be based onSELECT @DBBackupLSN = checkpoint_LSN -- AndersFROM @Baks;
-- Get the most recent differential backup based on that full backupINSERT INTO @Baks (backup_set_id,
   media_set_id,
   first_family_number,
   last_family_number,
   first_lsn,
   last_lsn,
   database_backup_lsn,
   backup_finish_date,
   TYPE,
   family_sequence_number,
   physical_device_name,
   device_type)SELECT TOP(1) WITH Ties B.backup_set_id,
   B.media_set_id,
   B.first_family_number,
   B.last_family_number,
   B.first_lsn,
   B.last_lsn,
   B.database_backup_lsn,
   B.backup_finish_date,
   B.TYPE,
   BF.family_sequence_number,
   BF.physical_device_name,
   BF.device_typeFROM msdb.dbo.backupset AS BINNER JOIN msdb.dbo.backupmediafamily AS BF
   ON BF.media_set_id = B.media_set_id
       AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_numberWHERE B.database_name = @DBNameAND B.is_copy_only = 0AND B.TYPE = 'I'AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND B.database_backup_lsn = @DBBackupLSN
ORDER BY backup_finish_date DESC, backup_set_id;

--select * from @Baks

-- Get the last LSN included in the differential backup,
-- if one was found, or of the full backupSELECT TOP 1 @DBBackupLSN = last_lsnFROM @BaksWHERE TYPE IN ('D', 'I')ORDER BY BakID DESC;
-- Get first log backup, if any, for restore, where
-- last_lsn of previous backup is >= first_lsn of the
-- log backup and <= the last_lsn of the log backupINSERT INTO @Baks (backup_set_id,
   media_set_id,
   first_family_number,
   last_family_number,
   first_lsn,
   last_lsn,
   database_backup_lsn,
   backup_finish_date,
   TYPE,
   family_sequence_number,
   physical_device_name,
   device_type)SELECT TOP(1) WITH Ties B.backup_set_id,
   B.media_set_id,
   B.first_family_number,
   B.last_family_number,
   B.first_lsn,
   B.last_lsn,
   B.database_backup_lsn,
   B.backup_finish_date,
   B.TYPE,
   BF.family_sequence_number,
   BF.physical_device_name,
   BF.device_type FROM msdb.dbo.backupset BINNER JOIN msdb.dbo.backupmediafamily AS BF
   ON BF.media_set_id = B.media_set_id
       AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_numberWHERE B.database_name = @DBNameAND B.is_copy_only = 0AND B.TYPE = 'L'AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND @DBBackupLSN BETWEEN B.first_lsn AND B.last_lsnORDER BY backup_finish_date, backup_set_id;
-- Get last_lsn of the first log backup that will be restoredSET @DBBackupLSN = NULL;SELECT @DBBackupLSN = MAX(last_lsn)FROM @BaksWHERE TYPE = 'L';
-- Recursively get all log backups, in order, to be restored
-- first_lsn of the log backup = last_lsn of the previous log backupWITH LogsAS (SELECT B.backup_set_id,
       B.media_set_id,
       B.first_family_number,
       B.last_family_number,
       B.first_lsn,
       B.last_lsn,
       B.database_backup_lsn,
       B.backup_finish_date,
       B.TYPE,
       BF.family_sequence_number,
       BF.physical_device_name,
       BF.device_type,
       1 AS LogLevel
   FROM msdb.dbo.backupset B
   INNER JOIN msdb.dbo.backupmediafamily AS BF
       ON BF.media_set_id = B.media_set_id
           AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
   WHERE B.database_name = @DBName
   AND B.is_copy_only = 0
   AND B.TYPE = 'L'
   AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
   AND B.first_lsn = @DBBackupLSN
   UNION ALL
   SELECT B.backup_set_id,
       B.media_set_id,
       B.first_family_number,
       B.last_family_number,
       B.first_lsn,
       B.last_lsn,
       B.database_backup_lsn,
       B.backup_finish_date,
       B.TYPE,
       BF.family_sequence_number,
       BF.physical_device_name,
       BF.device_type,
       L.LogLevel + 1
   FROM msdb.dbo.backupset B
   INNER JOIN msdb.dbo.backupmediafamily AS BF
       ON BF.media_set_id = B.media_set_id
           AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
   INNER JOIN Logs L ON L.database_backup_lsn = B.database_backup_lsn
   WHERE B.database_name = @DBName
   AND B.is_copy_only = 0
   AND B.TYPE = 'L'
   AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
   AND B.first_lsn = L.last_lsn)INSERT INTO @Baks (backup_set_id,
   media_set_id,
   first_family_number,
   last_family_number,
   first_lsn,
   last_lsn,
   database_backup_lsn,
   backup_finish_date,
   TYPE,
   family_sequence_number,
   physical_device_name,
   device_type)SELECT backup_set_id,
   media_set_id,
   first_family_number,
   last_family_number,
   first_lsn,
   last_lsn,
   database_backup_lsn,
   backup_finish_date,
   TYPE,
   family_sequence_number,
   physical_device_name,
   device_typeFROM LogsOPTION(MaxRecursion 0);
-- Select out just the columns needed to script restoreSELECT RestoreOrder = Row_Number() OVER(Partition BY family_sequence_number ORDER BY BakID),
   RestoreType = CASE WHEN TYPE IN ('D', 'I') THEN 'Database'
           WHEN TYPE = 'L' THEN 'Log'
       END,
   DeviceType = CASE WHEN device_type IN (2, 102) THEN 'Disk'
           WHEN device_type IN (5, 105) THEN 'Tape'
       END,
   PhysicalFileName = physical_device_nameFROM @BaksORDER BY BakID;

SET NOCOUNT OFF;

Result will look something like this:

Querying IO Statistics, the quick and dirty way

(Apologies for formatting, still trying to figure this thing out.....)

Reducing IO is one of the "easiest" way to speed up SQL queries.  There are off course many other ways, but for many situations, IO is the most bang for the time spent optimizing.

Recently I came across a query that really needed help.  Not going to go into WHY it needed help, as that is a full on article by itself.  One of the tools I use for optimizing is SET STATISTICS IO ON.  This will give a nice line of reads per table used in a query.  (There are plenty of articles online about how to use this information).  This particular query had some, uhm, scary numbers.  And many of the tables where re-used multiple times.  With 2 (yes 2, not 2 thousand, or 2 million) records in the base table that needed processing, I saw values of over 1 million table scans.  At one part in the query plan with 1002 records in the base table, there was a node with 11 billion records output....

I started looking for a way to compile these numbers so I could compare, and get a better overall picture of what I was looking at. Below is a typical line from IO Stats.  This is not the complete line, just the first few pieces of information in it:

Table 'XYZ'. Scan count 5, logical reads 18575......

I decided what I need is a tool that I can relatively easily load these lines into a table, and be able to query and see the differences between different modifications I make to the query.


First we need a table to store and modify the IO lines:

CREATE TABLE [dbo].[StatsImport](
    [StatsLine] [varchar](MAX) NULL,
    [StatsXML] [xml] NULL
)  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

To get the stats we need, have these two lines before you run a query:

SET STATISTICS io ON
SET NOCOUNT ON

The nocount will make them all come out as one nice big list if there are multiple queries, this makes the next step easier.

Now take the text from messages and load it into the StatsImport table:

SET QUOTED_IDENTIFIER  OFF
DELETE FROM StatsImport
INSERT INTO StatsImport (StatsLine)VALUES("Table 'ABC'. Scan count 5, logical reads 18575, physical reads 68, read-ahead reads 16895, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.",),
("Table 'DEF'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.",),
("Table 'GHI'. Scan count 5, logical reads 8929, physical reads 63, read-ahead reads 8822, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.") 

Now the tricky thing is to get this into a format that can easily be put into a table, I decided to go via XML (GASP!!!!!).

Part of the solution, was done with this excellent article by Sylvia Moestl Vasilik , mostly the part of how to get the lines set to be valid XML.  But since I wanted to make all the lines query-able, had to make some slight modifications.

SET QUOTED_IDENTIFIER  OFF

UPDATE StatsImport
  SET StatsLine = REPLACE(StatsLine, "'", "'")
UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine, ' reads', '_reads')UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine, 'Table ', '<Detail Table="')UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine, 'Reads ', 'reads="')UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine, '. Scan count','" Scancount="')UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine, '.', '" />')UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine, ',', '"')UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine,',','"')UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine, 'lob ', 'lob_')UPDATE StatsImport
SET StatsLine = REPLACE(StatsLine,'-', '_')
UPDATE StatsImport
SET StatsLine = '<Output> ' + StatsLine + ' </Output>'
UPDATE StatsImport
SET StatsXML = StatsLine  

At this point we have all the data in a valid XML format in the StatsXML column.

Now, if we wanted to just read it and display it, all we would have to do is query that column, I however, wanted to make it so I could do all kinds of crazy things with it.

Table to store the shredded XML:

CREATE TABLE [dbo].[IOStats](
     [TableName] [varchar](128) NOT NULL,
     [ScanCount] [int] NOT NULL,
     [LogicalReads] [int] NOT NULL,
     [PhysicalReads] [int] NOT NULL,
     [ReadAheadReads] [int] NOT NULL,
     [RunIdentifier] [varchar](50) NULL
) ON [PRIMARY]

Now to parse it into this table:

SET QUOTED_IDENTIFIER ON
INSERT INTO IOStats
    SELECT
            c.value('@Table[1]', 'varchar(128)') AS tableName,
            c.value('@Scancount[1]', 'integer') AS ScanCountc,
            c.value('@logical_reads[1]', 'integer') AS logical_reads ,
            c.value('@physical_reads[1]', 'integer') AS physical_reads  ,
            c.value('@read_ahead_reads[1]', 'integer') AS read_ahead_reads
    --c.value('@lob_logical_reads[1]', 'integer') as lob_logical_reads
            , 'Original'
    FROM StatsImport
            CROSS APPLY StatsXML.nodes('Output/Detail') AS T(C)

The line that reads 'Original' is just a text identifier for this load of stats.  Now you can see, I did not include all the data from stats as I did not need it for this particular problem, I think for most of you it would be easy to modify this if you need the lob etc. values.  One caveat here is you might need to go to a larger data type than integer if you have particularly crazy tables/queries.

With a little careful naming of your RunIdentifier values, it becomes easy to get output in the order you want.

So how did I use this?  Two ways.  First one was to see changes between different versions of the query.  With a baseline loaded as 'Original' and my first version loaded as 'New':

WITH TableNames AS (SELECT DISTINCT TableName FROM IOStats WHERE TableName NOT LIKE '#%')
, IOStats2 AS (
   SELECT
       i.RunIdentifier
       , i.TableName
       , SUM(i.ScanCount) AS ScanCount
       , SUM(i.LogicalReads) AS LogicalReads
       , SUM(i.PhysicalReads) AS PhysicalReads
       , SUM(i.ReadAheadReads) AS ReadAheadReads
   FROM IOStats I
   WHERE TableName NOT LIKE '#%'
   GROUP BY i.RunIdentifier, i.TableName
   )SELECT TN.TableName,  
   ISNULL(New.ScanCount,0) - ISNULL(Orig.ScanCount,0) AS ScanCountChange,
   ISNULL(New.LogicalReads,0) - ISNULL(Orig.LogicalReads,0) AS LogicalReadsChange,
   ISNULL(New.PhysicalReads,0) - ISNULL(Orig.PhysicalReads,0) AS PhysicalReadsChange,
   ISNULL(New.ReadAheadReads,0) - ISNULL(Orig.ReadAheadReads,0) AS ReadAheadReadsChange--into #TablesFROM TableNames TNLEFT OUTER JOIN IOStats2 Orig
   ON TN.TableName = Orig.TableName
       AND Orig.RunIdentifier = 'Original'LEFT OUTER JOIN IOStats2 New
   ON TN.TableName = New.TableName
       AND New.RunIdentifier = 'new'ORDER BY TN.TableName ASC

The output looks like below, although table names here has been changed to protect the guilty.  Basically this shows the reduction in reads on the different tables.

Another way I used it was for a more overall view of changes:

SELECT RunIdentifier,SUM(scanCount) SumScanCount, SUM(LogicalReads) sumLogicalReadsFROM IOStatsGROUP BY RunIdentifier

The 1k, 6k and 20k, are how many values I started with that would eventually need an update.  Although the particular query I was optimizing just inserts it into a temporary table that then will have further manipulations done to it.  I didn't even attempt 20k records with the original query, since I know when that happens (which is what caused me to start this project) we had to kill the job after 3 hours.  There are some abnormalities in this result that I need to go back and re-test, but at least I can easily see that the new query scales much much better as the number of records goes up.

So what is left to do?  I think writing a PowerShell script to be able to just save the IO Statistics to a file and load to the table and run the parsing.  Make the file name be the name of the parse.  That way re-loading into the tables would be much more straightforward, as well as make it possible to have my developers send me the stats and load them should be straight forward.

Know your testing parameters

This could be in the Stupid Stuff I have done series.....

So over the last 3 weeks I been tweaking a procedure.  In a nutshell we use this procedure to redeem items for points in a frequent shopper type system.  However due to the nature of the business, and a wholesale changeover from one system to another system at the beginning of 2015, I had loaded indicating ETL from the previous system as the source for points.

When a new calculation came in from the new system, the available points got set to zero in the ETL record, and a new record got created indicating the calculation engine as the source of the record.  This table has one record per rebate program for each customer.

To add to the confusion of all this, a customer can also have been merged with another customer, usually themselves when a bad customer record has been entered somewhere.  But, it can also be a merger of two organizations.

And this is where my testing took a turn for the worse.

This is the data as it sat in the stored procedure after some assembling of data by a few other rules.

As it sits above 2100 points are available for redemption.  Fairly straightforward, sum of points earned minus sum of points redeemed.  Straight forward.  This procedure takes a couple of parameters, the two important ones for this is the MemberID and how many points to redeem.  The winner MemberID must be the one passed in if there is a merged member, the proc then finds all the MemberIDs and their respective EarnedIDs, assembles the above, then allocates how many points each Earned record should have redeemed.

So simple!

In my unit testing everything worked fine.  My fix worked.  So I set to grab a few thousand MemberIDs to test the new vs. the old procedure for my regression testing.  Everything looked fine, until I came to one of them.  They both redeemed the right amount of points total, but the old one picked up that last record for 600 points against MemberID 300, but left 600 of one of the other records.  While the new one did not redeem against the record for MemberID 300.  They both redeemed the right amount of points according to what I put into the call to the procedure.

I was going back and forth on this for about 2 hours (the data was A LOT more complex than the output above).  Not quite grasping what had happened.  Until it dawned on me:  I'm not passing in the full amount for how many points to redeem!!!!!!!!

So I went back, looked at my test setup and saw it immediately.  The function used to find how many points are available for a Member, takes ALL MemberIDs as a table parameter!  Not just the winner. When I grabbed MemberIDs to test for, I made sure that the ones I picked where winners if there was merges involved, however I did not call the available points function with all the MemberIDs.  So all I got in this case was the 1500 points available for MemberID 100.

Both procs did it right, in that both redeemed 1500 points.  All the other tie breakers for which ones to do a partial redemption against where even, so something else in the queries made them pick different records to do redemption against.  The new one luckily did it the way I wanted them done, which was part of the changes I did to the proc.  But not often I have two version of a procedure, that comes back with different answers, that are both correct.

Lesson learned: Understand your test data.  Double check you have the right test.  Assume nothing.

Working in the financial industry

I worked at Sammons Financial Group for almost 7 years, first as the only DBA for the annuity division, later as part of the enterprise DBA team.  Besides working way too long days, this was a great opportunity to expand my knowledge of SQL.  Worked with some very talented people on both the .NET side and the SQL side, created some applications from some crazy specifications, and had 80 or so databases on one server.

Got my feet wet with the use of SSIS and SSAS, snuck in the use of SSRS while none was looking.  SSRS turned out to be highly liked by the user base, for once they could get ok looking reports fairly quickly, and they could set up their own schedules for when they wanted them and how to have them delivered.  Win.

After I had run SSRS for the Annuity division for about a year, the enterprise decided to do so as well.  They paid for a consultant to take a look at what they needed for hardware etc., without consulting me.  The specs they gave him for how many reports at the enterprise level was 100, at that time I had over 150 reports just for one division.  Fail.

During my years at Sammons I upgraded the annuity division from SQL 2000 to SQL 2005, and finally to 64 bit SQL 2008.  What a relief to be on 64 bit after dealing with too little memory for so many years.  Our production server went from 4 GB to 32, then to 64+.  Since we had a somewhat subpar SAN, keeping the caches warm for hours instead of seconds was a major plus.

Being the only DBA in charge of a division took it's toll on my life, and I decided a change was needed since one did not occur internally when they where given a chance.  The market in Des Moines is fairly small, especially if you do not want to work for any of the banks located there.  A few years earlier I had been in Chattanooga, TN on a motorcycle vacation that my wife insisted I go on.  I think she might have had ulterior motives!  So we started looking at cities within riding distance of the Blue Ridge mountains.  Soon I received an offer from AgData in Charlotte, NC.

This finally brings us to today.  Part of a team of 7 developers, 2 DBAs, working with a single client (it's a large client).  For the first time part of a team where the other DBA is competent, and we both can cover 100% for each other when the other is out.  Been MANY years since I could take a vacation for 2 weeks and leave my work behind completely with no worries about there being a meltdown waiting for me.  Even to the point that I went to Norway over Christmas 2014 in the middle of a large project, a project I was responsible for moving to production a week after coming back.  Very nice!

So here we are, up to date with where I am at.  Hopefully found a city we want to live in for a long time.  Weather is nice, not as hot as Texas was, nor as cold as Iowa was.  Yet we have 4 seasons.

Here is the reason why I love living in this area, best motorcycle roads pretty much anywhere.

Now that you know a few things about me, on to talking about SQL!

Anders

Going back to my roots, DBA at an Aviation company

Late October 1999 I was in Norway celebrating my moms 60th birthday, to her great surprise as I was not supposed to be there.  The day after her birthday party I got a call from my boss telling me I had to get to Houston ASAP.  A cancer treatment company had just had their DBA walk off the job due to not handling the stress very well.  2 week engagement until they could get things under control.  When I got there a few days later I learned that they had known Y2K issues that had not been fixed yet.  All in all I stayed there for 6 months, after fixing their Y2K issues at 6 PM on New Years Eve.  Cutting it a bit too close.  The longer I stayed the more I understood why the previous DBA had walked off the job, high stress when your servers run radiation machines in about 30 hospitals around the country.

After this I was ready to not be a consultant any more.  Took a job with FlexJet in Dallas,  being one of 3 DBAs, managing their database servers that ran their fleet management program.  FlexJet at the time had about 100 business jets that would fly wherever their customers/owners needed them, being that they were time shares we had a lot more owners than planes.  Due to this the scheduling of aircraft was extremely complex even compared to an airline, at least they know in advance what the schedule should be, we generally knew about 4 hours ahead of time where we needed a plane.  Getting the right size airplane, to the right airport, could be a challenge.  True 24 hour operations with world wide coverage (although most of it in the US, or at least US based owners).

The SQL code at FlexJet was full of code that was deprecated.  To make sure we could upgrade to newer versions of SQL I went through every line of SQL code in the company to make sure it had no deprecated features, this included rewriting stored procedures, as well as rewriting dynamic queries in C++.  Joy!  The fun thing with this job was to use some of my skills learned in flight training and operations classes, being able to speak the language on both sides has some benefits.  When I for personal reasons decided to move on (I considered moving back to Norway), they asked me to come work in their Copenhagen office and they would pay for my commute every week.  Was very tempting.

Instead, I decided it was time to get my feet wet with replication.  Something I had never done before.  On the interview, as soon as the pleasantries where over, I opened by stating "I know nothing about replication, have never done it before, but so far have not run into anything in SQL I could not figure out.  If that is not acceptable, let us not waste any time and call this interview over."  I was there from the beginning of the project until 3 months after going live and handing it over to a permanent DBA to handle.

Replication was a challenge for sure.  This was for a book seller, at the time they had about 75 stores.  We set up replication to send inventory records out to each store as books where loaded on the trucks, then they where made active when they got scanned at the receiving end.  We used about every different kind of replication possible, even to the extent that we could query sales live on the main server at head quarters, yet they could operate the stores even if communication was down.  For early 2000s this was quite a big deal, last time I checked in with the DBA there 2 years ago the system was still in use, but quite expanded both by number of stores and scope of their capabilities.  If their project has gone right, the new system should be out by now.  Except for me, I believe all the programmers involved in the first version is still there.  Great testament to the work culture at this company, one of the very few places I was on contract that I was sad to leave.

2002 came with the troubles after 9/11 and I was laid off for a few months, spent some time studying up on holes in my SQL skills while looking for a job.  Being unemployed sucks.  Finally landed a job with ABC Radio Networks (strangely enough taking over the job one of the DBAs from a previous job had held), responsible for databases running the radio operation.  Had a great team of programmers and network engineers, some of which are friends to this day.

Had some interesting challenges at ABC.  How to deal with 53 week years?  WHHHAAT?  Yeah it happens, and the way they wanted the report was 53rd week compared to the 1st week of the same year.  Quarterly reports?  Don't even ask.  I have to admit I did a design flaw here by calculating it, should just have had a look up table that defined the reporting period.  OR convinced them to use normal reporting where you would just compare week by week, and in the 53rd week the comparison year by year would be to the same week as the previous week.  Live and learn.  The function that would calculate this was one of the nastiest piece of code I have ever written.

One of the systems I took over had a table with a 12 column primary key, foreign keyed into a table with 16 columns primary key.  Surprisingly it was pretty fast.  Was a good challenge to write an archiving routine for this table, since when I got there we had 100s of millions of records in the table, most of which where not needed.  The arching job ran in batches for the better part of a month to move data off the primary file.  Later on SQL came out with partitioned tables for this.  Oh how life has gotten easier over the years.

Wrote a search application that could search their entire transcribed news archive.  In the beginning the users wanted to be able to search the result of the search, since their current search application was super slow they thought this was needed.  Luckily convinced them to let me try it my way and just run a search all over again every time they wanted to narrow down the result.  The day I demo'ed it for a few users they demanded I get it in production immediately, the searches all took less than 2 seconds on the entire data set, vs. 10-15 minutes in their current application.  SQL wins!

I was there when Citadel bought ABC Radio Networks from Disney, due to certain activities I decided to move on.  Having forgot that I had turned on nationwide searches while unemployed, the first call I got was from a company in Des Moines, Iowa.  When they offered me the job I was watching TV with my wife, I turned around and asked her if she would be ok with moving to Des Moines.  Yupp.  After over 11 years in Dallas, 2 weeks later I had moved from Dallas, TX to icy cold Iowa.  Had snow on the 5th day I was there.  This might not have been a good move.  Traffic was, however, much better.