Skip to content

SQL Server

Bellingham SQL Server Users Group – May 20th

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.

What is the Bellingham SQL Server Users Group?

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.

Monthly Chapter Meeting

Read More »Bellingham SQL Server Users Group – May 20th

TSQL – Change Data Capture

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');

Read More »TSQL – Change Data Capture

Deadlock and Trace Flags 1204 and 1222

When you are looking for deadlocks there are, like most things in SQL Server, more than one way to find the deadlocks. This article is specifically focused on using the SQL Server ERRORLOG file located in the LOG directory for the SQL instance. If you used the defaults on SQL Server 2012, this log would be at this location:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG

For SQL Server 2014 it would be here:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG

and in similar locations for other versions of SQL Server.

 

What is a Deadlock?

deadLock

A deadlock is a specific case in SQL Server and other databases where, in the simple case of two transactions, the first transaction gets stuck waiting on blocking from the second transaction, and the second transaction gets stuck waiting on blocking for the first transaction.

Imagine traffic so jammed up that one lane of travel is blocking another lane of travel, and vice versa. The deadlock in SQL Server is similar to this, but instead of traffic being blocked, it is queries and transactions being blocked.

Eventually in traffic when cars are blocking, one will give up and back out, and the other will be allowed to drive through. The one that backs out is known as the deadlock victim.

When one query is chosen as the deadlock victim, it looks something like this:

Read More »Deadlock and Trace Flags 1204 and 1222

Difference Between TRUNCATE TABLE and DELETE FROM Table

Most DBAs know the usual difference between TRUNCATE and DELETE FROM, however one not so obvious difference between the two is how things are handled if the table is corrupt.

For instance, if you look at the corrupt database from the Database Corruption Challenge Week 1, you see the following

DeleteVsTruncate1

If you want to clear out the corrupt Revenue table, one way to attempt to do it is with a DELETE statement like this.

Read More »Difference Between TRUNCATE TABLE and DELETE FROM Table

A Weekend Full of Database Corruption

On Saturday morning, I announced the Database Corruption Challenge, and I had to abbreviate it as the DBCC, why not, acronym overloading isn’t always a bad thing.

There were 91 participants, 22 of which ended up with correct answers with no corruption and no data loss.

I created a database, with 3 bytes of corruption in one of the leaf node pages of a clustered index, however all I stated was that the database was corrupt, and it could be fixed with no data loss.

The number of different ways that people solved the problem was amazing. There were many attempts that did remove the corruption, and did recover all the rows, however many judged success as having the same number of rows as before, as a success. There were so many successful solutions that I think I will post one a day for the rest of the week, with the details explained. One solution even involved editing the database file on disk with a hex editor. I don’t have time to post all the good solutions right now.

 

The original corrupt database had been created on SQL Server 2014, but many people contacted me stating that they didn’t have SQL Server 2014 on their home computer, so I ended up creating a SQL Server 2008 version of the problem, but some were so driven to solve the challenge that they ended up installing SQL Server 2014 just to work the problem.

house1

Nice work John. Thanks for playing along, I hope you enjoyed the experience.  The next tweet from Mike Fal followed the post from John Morehouse.

nerd2

 

I hope that John took this comment from Mike Fal as a great compliments, however it was soon corrected by AJ Mendo that it is more geek than nerd.

aj1

Either way, geeks or nerds, it was a good learning experience for those involved, and for me having to evaluate each solution to determine if it was indeed a valid solution with no data loss. Personally I ended up restoring the corrupt database and testing different solutions over 50 times this weekend.

Read More »A Weekend Full of Database Corruption

Introducing the DataBase Corruption Challenge (DBCC) – Week 1 Challenge

corruption

Welcome to the DataBase Corruption Challenge, this is an about weekly blog challenge where I will post a corrupt SQL Server database with some details on what happened to it.

If at this point you are already a bit irked by my use of capitalization in the DataBase Corruption Challenge, and the acronym of DBCC that I have used to describe it, then you are already ahead of many people reading about this challenge. Welcome to the challenge.
The challenge will be to download the corrupt database and attempt to recover it. If you can recover it, please send me the steps to recover it, along with some proof that the database has been recovered. The goal each week will be the following:

Read More »Introducing the DataBase Corruption Challenge (DBCC) – Week 1 Challenge