Cleaning Up Old Database Dirt with SQL Server 2012

I am about to venture into transitioning a 10 year old database that started with SQL Server 2000 which has surely some (a lot?) accumulated  ‘dirt’ in it that started way back in 2001. In the past (am sure it still is with 2012 edition), SQL Server has been very forgiving for all the sins of its handlers (DBAs, developers, users, & pretenders). It had allowed a lot of minor issues to creep into the database without constantly complaining and compromising its ability to run in production. In a perfect world, those issues could have been eradicated a long time ago.

The ‘dirt’ has accumulated as SQL Server hasn’t been pestering people that there is some cleaning up to do. In the real world, that is acceptable. We just can’t stop the show after someone spilt water on the stage floor. After 10 years,with better tools and ways to clean up dirt, it would be a worthwhile exercise so we can navigate easily through the challenges of modern times.

Enter SQL Server 2012, it packs with efficient and reliable tools to accomplish this tasks of cleaning things up. To start on the cleaning process though I need to see the mostly unseen ‘dirt’. SSDT comes in handy for this job.

Using SQL Server Data Tools (SSDT) As Dirt Probe

Here is how to begin identifying what needs to be cleaned.

Then allow SSDT to scan your database for ‘dirt’. I am actually referring to ‘errors’ and ‘warnings’!

After scanning, go to the Error List tab and VOILA!!! now you see the ‘dirt’.

Armed with a long ugly list, you can now hire a cleaner to scrub off each and every dirt you have identified.

That is all for now for the cleaning up!

**************************************************
Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com

Toto Pictures : Surfin Urbiztundo

I shot these photos last November 29, 2008 during the La Union Grommet Championship 2008 in Urbiztundo, San Juan, La Union. I am actually not a sports photographer but when I get to shoot them, I found that they are very excellent subjects. I enjoyed shooting them all day long when I was there.

It has been some time now and I really want to photograph them again. Meanwhile, here are a few shots of surfing young kids that are 16 and below (groms) that participated in the tournament. Attached are the settings I use for these shots.


Nikon D300, Sigma 150-500mm, Ballhead + Tripod
Shot at 500mm, f7.1, 1/500 secs, ISO-200


Nikon D300, Sigma 150-500mm, Ballhead + Tripod
Shot at 500mm, f7.1, 1/500 secs, ISO-200


Nikon D300, Sigma 150-500mm, Ballhead + Tripod
Shot at 500mm, f8, 1/1250 secs, ISO-200

I am hoping to go back one of these days and shoot these surfers again. This time, as a Canon user 🙂

SQL Server 2008 RTM Has To Wait Until Visual Studio 2008 SP1

It’s been 2 days since Katmai RTM’d. I have downloaded a 3GB+ ISO of SQL Server 2008 Developer Edition.

Days prior to the RTM release, upon sensing that Katmai code is about to go gold, I have already readied my laptop, reformatted it, to accomodate the brand new SQL Server 2008. And last night was my first taste of SQL Server 2008 after a day’s download.

My first taste? SQL Server 2008 RTM has to wait until August 11.

Too bad … SQL Server 2008 RTM, with all its glory, will not install without VIsual Studio 2008 SP1. Unfortunately, Visual Studio 2008 SP1 is still on BETA. In some sites, it is rumored to be released August 11.

Here are some of my initial experiences:

  • According to this KB, http://support.microsoft.com/kb/956139, you can install SQL Server 2008 (I tried Developer Edition) by unselecting Analysis Services, BIDS and Integration Services. It still does not though.
  • My first attempt to install seems to be fine until I got the VS2008 SP1 check. I went back to unselect the 3 features mentioned in the KB but still SQL Server wont budge. I tried cancelling the installation and attempted to run it again but this time I got the FILESTREAM validation error.

Very frustrating!

I am thinking of just installing the engine but I don’t want to for fear that there still might be problems and it will just ruin the current state of my laptop. I’ll see if I can do this in some virtual machines later.