This is a follow-on from the article Do Alpacas dream of Farming DBAs' which you will need to read first in order to make any sense of this!

I would like to extend greetings once again to all of you from my alpaca factory.

Okay, so my lack of understanding how these creatures are manufactured will 'key you into' the fact that I never did jump the cubicle wall and head to the highlands to begin a new venture in raising Vicugna pacos. (I even had to look that up on Google.)

So Dear Readers, what saved me you may ask? Well, collectively you did. Before I get into precisely how I came to resolve my little Implement This dilemma and announce the winners of this contest that our beloved editor dreamed up, I want to thank you all for your kind critiques of my first article as well as the greater-than-anticipated participation in this little effort. Of course it was because Red-Gate dangled an iPod Shuffle in front of your nose – but I am determined to believe that I had just a little something to do with it.

When we last met, I was heading off to start my new enterprise – chucking the mouse and calling it a career thanks to the frustration of dealing with vendors whom have little knowledge or regard towards proper database structure and behavior as well as those members of management with more money than sense; hungry to sign that next contract. I outlined my latest task at hand: to delete a large sum of space with limited resources for doing so. I mentioned that I suffered through many failed attempts before coming to a solution that worked. These numerous failures wore heavy on me. I’ve been associated with Microsoft SQL Server since version 6.5, first as a Programmer, then as a Database Administrator for the last 6 years. I often tapped into both disciplines in order to accomplish many “outside-the-box” issues and thought myself a competent (if not talented) SQL Server professional. I was left questioning that assertion.

Then inspiration struck:

“What if I’m not alone in this process of trial-and-error?”

“What would other SQL professionals do?”

“Would others have been able to solve this the first time?”

I called on you to offer up your solutions and you rose to the challenge! Though I did not see a solution that specifically matched what I did to solve the matter, I did see more similarities than differences between us all. I specifically liked the brevity and simplicity of the response offered by Michael: 'Outsource HR, Delete database'

Before announcing the winners let me take a few moments to look at some the ways I managed to fail as well as the final solution.

Just Run The #$%! Vendor Script!”

I work for a medical system and, though I am not a medical practitioner, I’ve taken the crux of the Hippocratic Oath to heart: “I will prescribe regimens for the good of my patients according to my ability and my judgment and never do harm to anyone.” The script offered by the vendor to clean up orphaned records would not delete any records we desired to keep. Performance hits during the script execution would be of no concern as I had been given the latitude to remove access to the database during my maintenance. Therefore no harm would come to the database. Most-importantly, I had a valid backup.

I placed the following script into the construct of a SQL Agent job in order to take advantage of the notification feature so I would not need to periodically check on the query execution status.

DELETE  FROM tblOrphans
WHERE   FieldX NOT IN ( SELECT  FieldX
                        FROM    tblParent1 ) AND FieldX NOT IN (
        SELECT  Field X
        FROM    tblParent2 )

Unfortunately I did not think matters through completely because I failed (as many of you suggested) to place the database in Simple Recovery mode first. At certain times, our bravado pushes us into action before we are ready. The end result was a full transaction log and disk volume. Welcome to failure number one.

“Reduce Logging and Then Just Run The #$%! Vendor Script!”

After altering the database to run under Simple Recovery mode I truncated and shrunk the database’s transaction log to a manageable size. I started the SQL job that ran the vendor’s delete script assured that this time I’d thought everything through; and then ran the server’s D drive out of space due to expansion of the tempdb.

According to your responses, this is one key issue that nobody seemed to pick up on. Attention was focused on the database we were attempting to shrink and the server. No thought was given to the SQL instance and the system databases. I have to believe the derived tables created as a result of the IN clauses are what resulted in a bloated tempdb. Having no relevant indexes on either of the parent tables did not help matters either. Figuring I was onto something moved the tempdb files to the C drive and re-ran the DELETE statement. Even with the additional space available on this volume I still ran the drive out of space.

Network Drive to the Rescue

I came to the realization that I had no choice but to bridge the fix across the network. I moved the tempdb files for the SQL instance to a network drive with over 300Gb of free space.

USE master
GO
ALTER DATABASE tempdb 
   MODIFY FILE (name = tempdev, filename = '\\networkshare\tempdb.mdf')
GO
ALTER DATABASE tempdb 
    MODIFY FILE (name = templog, filename = '\\networkshare\templog.ldf')
GO

I then executed the following code to drop the offending indexes.

DROP INDEX dbo.tblOrphans.idx1

DROP INDEX dbo.tblOrphans.idx2 

Using the newly-created space I reclaimed by dropping the indexes I created and loaded a staging table with only the records from tblOrphans that I wished to keep:

SELECT  O.*
INTO    dbo.tblOrphans_KEEP
FROM    dbo.tblOrphans O LEFT JOIN dbo.tblParent1 P1 
    ON O.FieldX = P1.FieldX 
    LEFT JOIN dbo.tblParent2 P2 ON O.FieldX = P2.FieldX
WHERE   P1.FieldX IS NOT NULL AND P2.FieldX IS NOT NULL  

I then proceeded to drop the tblOrphans table after verifying that there were no dependencies placed upon the table.

DROP TABLE tblOrphans
GO
EXEC sp_rename 'tblOrphans_KEEP', 'tblOrphans'

Afterwards, it was time for cleanup duty. I needed to recreate the indexes, shrink the database, and relocate the files for tempdb back to their original locations on the database server. It was also necessary to delete the files from the network drive after I restarted the SQL services thereby recreating the tempdb files on the database server.

In order to avoid this issue from occurring again I created the following script to be run as a scheduled SQL Agent job:

DELETE  FROM dbo.tblOrphans
FROM    dbo.tblOrphans O 
   LEFT JOIN dbo.tblParent1 P1 
        ON O.FieldX = P1.FieldX 
   LEFT JOIN dbo.tblParent2 P2 
        ON O.FieldX = P2.FieldX
WHERE   P1.FieldX IS NULL AND P2.FieldX IS NULL