The Database Corruption Challenge (DBCC 2015) t-shirt is now available at CafePress. It looks something like this.
Check it out and order one to show your participation in the Database Corruption Challenge.
Last week, I mentioned that I would come up with prizes for the contest, and that the prizes would be based on the number of participants each week. So here is what I have come up with:
The Bellingham SQL Server Users Group will be having its next meeting on May 20th at 6:00pm. This is free event for the local SQL Server community.
PASS Chapters are local user groups that meet regularly in communities around the world to share their passion and knowledge about Microsoft SQL Server and Business Intelligence. Each Chapter has its own style, leadership, and schedule to meet members’ needs. PASS Chapters are a free and easily accessible resource for learning more about SQL Server and connecting with other SQL Server professionals in your area. They are also your local connection to the PASS Community.
Yesterday I blogged about submitting to SQL Saturday Denver, which is about 5 months away, after writing that post, I realized that I haven’t even blogged about SQL Saturday Redmond which is occurring in just over 2 weeks.
SQL Saturday #393 Redmond marks just 3 years since my first time speaking at a SQL Saturday #108 in Redmond. So lets take a look back, at Feb 5th, 2012, it was my first time presenting at a SQL Saturday ever, I had discovered this cool new query tool called the Common Table Expression, and I prepared my presentation. I practiced my presentation once before Saturday, and I was ready. Did I mention, that this was also my first time every attending a PASS event, or a SQL Saturday, and I was presenting. I was in the morning time slot after the keynote, and boy was I nervous. Over the next year I presented at I few other SQL Saturdays, and finally a book publisher, Joes 2 Pros noticed my presentation and asked me if I wanted to turn it into a book. In 2012 and 2013, I turned it into a book, which was released the same day as SQL Saturday #212 Redmond, where I again was selected to present on Common Table Expressions, this time I had books to give away as a prize.
This year the Denver SQL Saturday is being held on September 19th.
I believe it has been 2 years since I attended SQL Saturday in Denver. Since the last time I was at SQL Saturday Denver, my employment has changed. Previously I was employed as a full time employee at a local company. Now I am a freelance database consultant at my own consulting firm, Stedman Solutions, LLC. The new role gives me more freedom to decide which events I am going to attend, and SQL Saturday Denver is certainly on my list.
You have a need to keep track of all insert, update and delete actions on a table, or multiple tables. As you consider solutions, you might think about using a trigger, however triggers have their own baggage. You consider using the OUTPUT clause to log to a changes table, but then realize that the output clause cant be enforced.
Then the SQL Server feature called Change Data Capture comes into play. CDC is a SQL Server Feature that monitors the transaction log, looking for changes to specific tables, when the changes are discovered, they are then written into a Change Table that can then be queried to find out what was changed and when it was changed.
Sample Code
Lets take a look, to start with, I create a database called [DemoCDC] to use for the demo. That database contains a single table called [History] that you may recognize from the Week 3 Database Corruption Challenge. Following that are a few insert statements to just start with some data in the table.
CREATE DATABASE [DemoCDC];
GO
USE [DemoCDC]
GO
CREATE TABLE [dbo].[People]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[name] NVARCHAR(200) NULL,
[dateAdded] DATETIME DEFAULT GETDATE()
);
INSERT INTO [dbo].[People] ([name])
VALUES ('Simba'), ('Annette'), ('Bobby'),
('Fred'), ('Mary'), ('Beth');
Find out more about the Week 3 Challenge on the overview blog post.
The winning solution, was submitted by Randolph West with the following steps:
I created a dummy database with the same name, set it offline, deleted the files, dropped in your LDF, brought it back online, and did a tail of the log backup with NO_TRUNCATE to bypass the error.
Then I followed a standard full restore with NORECOVERY, the three transaction logs you supplied, plus my tail, and set WITH RECOVERY to bring it into my shiny 2008 R2 instance.
Read More »Week 3 – Corruption Challenge – The Winning Solution
It is my pleasure to announce the winners in the Week 3 Database Corruption Challenge. But first how about a rundown of challenge.
The challenge was issued at 10:00pm (pacific time) on Friday night. Yes, this was a difficult time for those on the East coast of the US, and if you are from EST, next week I promise the time will be better for you. Trying to switch up the times to give everyone across the planet a fair advantage. Those who had signed up for my Newsletter received early notification of when the corruption challenge would occur, but not details on what the challenge would be.
Once the challenge was issued, I received a couple of partial submissions, one contained the correct steps, but no results. The second one contained the right results, but was missing the steps to do it. The first correct submission that contained the steps, and proof that it was done correctly came in 37 minutes after the contest had started. It was almost an hour later before the next winning solution was sent in.
Today I had the opportunity to present at LinuxFest Northwest on MySQL Join Types, using the MySQL JOIN Types poster in the presentation. It was… Read More »MySQL JOIN Types for LinuxFest Northwest
Welcome to week 3 of the database corruption challenge. Hi, I am Steve Stedman and I have created this challenge to help the best and… Read More »Week 3 of the Database Corruption Challenge
After two great weeks of the database corruption challenge, week 3 is approaching. I have a few things to announce as we go into week 3.
The database corruption challenge will run over 10 weeks at which point another challenge will begin, and everyone will start out with a fresh score, sort of like bowling. This post I am going to cover scoring and prizes, just to keep it interesting.
I realized that I had never finalized the scoring of the competition. Here are the scoring criteria that I have come up with.