ost days I truly love being a SQL DBA. Then there are the days when I seriously consider making the jump to Alpaca farming.

You see, my company recently purchased an application that the Human Resources department uses in order to monitor the Internet usage of employees.

Like most turnkey applications it was not vetted properly by anyone in the Technology department. Unfortunately, I.T., to some, does not stand for Information Technology, but rather Implement This. So, contracts were signed and we were handed the specification sheet from the vendor.

According to this specification, we should be able to keep a running 90 days of data to report against, data older than that is archived and purged from the database. Under this plan, the database should consume no more than 50 GB of space.

We set things up according to this specification and after the initial eight months we were topping out at 150 GB of consumed disk space for the database; even whilst purging the data that was more than three months old. Currently, ten months after implementation, we hit crisis point when the database is 270 GB in size, there is a mere 1 GB free on the disk devoted to the data file, and we are purging all data older than 30 days rather than the planned 90 days. Clearly, something was very wrong

A series of calls to the vendor establishes the fact that the purge process is not working correctly. There are two tables that still hold orphaned records from archived data. These records are overlooked in the purge process controlled via the application because of the lack of referential integrity between these tables and any others in the database which would have fired an error, or which could have been modified for cascading deletes.

The vendor’s level-two support provided our analyst with two scripts, which they rather optimistically believed would successfully run to clear out unnecessary data from each table. It looks something like this:

This is one of those situations that every DBA encounters occasionally. We all know from experience that it takes time and space to make space. Balancing log and tempdb growth against reclaimed space, i/o, locking, and many other variables are not taken into consideration by most application vendors when they provide you with scripts like this one. Understanding the root cause of a problem and formulating the correct plan for its resolution is arguably one of the most important traits of a good DBA.

In order that you should share our feelings of dread at this point, I’ll sketch in a few details. The schema of tblOrphans is thus:

There are two indexes on tblOrphans:

And…

Other items of note:

• None of the three tables in question have foreign key constraints
• Neither parent table has a relevant index
• tblOrphans consists of 205 million records, consuming 50GB of space
• 191 million records would be deleted as a result of the delete query
• Customers are accommodating of downtime. 24×7 access is not required
• The server consists of two logical drives
  • C: 136 GB RAID 5 with 50 GB available space
    • Application files
    • all user database log files
  • D: 273 GB RAID 5 with 1 GB available space
    • system databases’ data and log files
    • user database data files
    • browser logs

The problem is this. We can see that the script as it was given us is most unlikely to run successfully. We need to release enough space to the O/S to allow the nightly 2GB worth of browsing logs to be input into the database until the next monthly purge; retaining enough data file space allowing for growth from the nightly input from these logs. The bright side is that we will be replacing this product instead of upgrading to a new version of their software. This means that modifying schemas, adding/removing indexes, and implementing referential integrity is acceptable so long as reporting and data entry is not adversely affected.

I know the steps (and failures) I encountered along the way to resolving this matter and I’ll outline the solution I ended up utilizing in a future article. In the meantime, I’d be very curious to know what you would have done with the information that has been presented.

Simple-Talk offer an iPod shuffle to the best proposed solution, submitted by 1st September 2007 as a comment to this article, and there will be three runners-up prizes of a Simple-Talk gift bag, which includes the highly desirable Red-Gate USB memory drive, and higly-collectable Simple-Talk pen..

I must be going, the Alpacas are getting restless!

(as you see below in the comments, the Simple Talk readers did not spare their energies in giving Timothy their sage advice. We continue the story, and Timothy’s award of the prizes, in the sequel…. Greetings from the Alpaca Mill)