Surveillance 185

Summary 202
Chapter 8: Finding data corruption 204
Causes of corruption 204
Consequences of corruption 205
Fighting corruption 206
Seeking out corruption 224
Summary 228

vi
ABOUT THE AUTHOR
Rodney Landrum has been working with SQL Server technologies for
longer than he can remember (he turned 40 in May of 2009, so his memory
is going). He writes regularly about many SQL Server technologies,
including Integration Services, Analysis Services, and Reporting Services. He
has authored three books on Reporting Services. He is a regular contributor
to SQL Server Magazine and Simple-Talk, the latter of which he sporadically
blogs on about SQL and his plethora of geek tattoos. His day job finds him
overseeing the health and well-being of a large SQL Server infrastructure in
Pensacola, Florida. He swears he owns the expression "Working with
Databases on a Day to Day Basis" and anyone who disagrees is itching to
arm wrestle. Rodney is also a SQL Server MVP.


ABOUT THE TECHNICAL
REVIEWER

Shawn McGehee is a full-time professional DBA and a part-time amateur
developer from Pensacola, Florida. He is very active with the local SQL

users’ group in Pensacola and helps organize / speaks regularly at their
events. Shawn is also a contributing writer to popular SQL websites such as
Simple Talk and SQL Server Central. He was also a co-author of the book
"Pro SQL Server 2008 Reporting Services."


vii
ACKNOWLEDGEMENTS
I would like to thank everyone involved in the making of this book,
peripherally and personally, but first and foremost Karla…my love, who has
been with me, spurred me on and understood when I needed a fishing or
beer respite through 5 books now. I love you.
To all my kids who also sacrificed during the writing of this book. Megan,
Ethan, Brendan and Taylor. Well, OK, Ethan did not sacrifice so much, but
he did help me understand that "Buffalo buffalo Buffalo buffalo buffalo
buffalo Buffalo buffalo" is a legitimate sentence.
Thanks to my Mom and Dad, as always. I love you. There will still be a
novel, I promise; just not a Western. Sorry, Mom.
Thanks also to Shawn McGehee, my good friend and DBA colleague, who
tech-edited the book. It is much better for it. Also, thanks Shawn, for letting
me use snippets of your hard-won code as well.
Special thanks also go to Truett Woods who has opened my eyes in a lot of
ways to good coding practices, and for the use of one of his base code
queries in Chapter 1.
Thanks to Joe Healy of devfish fame, a straight up bud whose .Net
tacklebox is more full than mine. I will be getting the devfish tattoo next.
Finally, I would personally like to thank Throwing Muses, The Pixies and
Primus for providing the music that helped me through the many late
nights. OK, so they will never read this and offer to come over to play a set
at a backyard BBQ, I know, but one can hope.



viii
INTRODUCTION
This book, as with almost all books, started out as an idea. I wanted to accumulate
together those scripts and tools that I have built over the years so that DBAs
could sort through them and perhaps adapt them for their own circumstances. I
know that not every script herein will be useful, and that you might ask "Why are
you using this table and not that DMV" or "Why is this code not compatible with
SQL Server 2008?" After writing about SQL Server solutions now for the past 10
years, I can expect this criticism, and understand it fully. Everyone has their own
ways of solving problems. My goal is to provide useful samples that you can
modify as you please.
I wrote the book the way it is because I did not want to bore DBAs to tears with
another 500+ page textbook-style tome with step-by-step instructions. I wanted
this book to be a novel, a book of poetry, a murder mystery, a ghost story, an epic
trilogy, a divine comedy. But realizing that this is, after all, a technical book, I
compromised by imbuing it with some humor and personality. If you make it as
far as the monster at the end of this book, my hope is that you will have been
entertained and can use the code from the Tacklebox in some fashion that will
make your lives as DBAs easier. Why "The Tacklebox," you might ask, rather than
"Zombie Queries," "You Can’t Handle the Code" or "You had me at BEGIN?" I
think, as I push halfway through my career as a DBA and author, this book is as
close as I will ever get to "The Old Man and the Sea"…oh yes, and apparently the
"Toolbox" had been copyrighted. Plus, come on! Look at the cover of the book.
How can I live here and not go fishing once in a while?

Chapter 1
Here you will find wholesome SQL Server installations on the menu, complete
with Express, Continental and Deluxe breakfast choices, depending on your

application’s appetite. And there will be a little GUI setup support here. This
chapter is about automation, and a lengthy script is included that will help you
automate SQL installations and configurations. There is some foreshadowing
lurking as well, such as code to enable a DDL trigger that I will show later in the
book. This is the chapter where your new SQL Server installation is completely
yours, having not as yet been turned over to the general populace of developers or
users. Enjoy it while you can.
Chapter 2
In this chapter, I introduce the DBA Repository, a documentation tool I have
built using Integration Services and Reporting Services. It is easy to manage one,
Introduction
ix

two or three SQL Server instances with the panoramic view the tool gives. It is
even easy to work with ten SQL Servers without documentation, but when you
have 70 or 100 or 2,000 SQL Servers without an automated documentation
solution, you cannot successfully manage your SQL Server Landscape – ironically,
that is the name of the chapter, "The SQL Server Landscape."
Chapter 3
I think we can all agree that data at rest never stays that way. No, far from it. The
data in this chapter has begun the swim up river to its spawning grounds and will
migrate and transform like the intrepid salmon (hey, a fishing reference) from the
open ocean, to river, to stream. Here, I look at different ways that data moves
about, and I investigate tools such as SSIS and BCP that help facilitate such
moves, whatever the reason, be it high availability, disaster recovery or offloaded
reporting.
Chapter 4
In this chapter, I describe one of the first hungry monsters of the book, the disk-
space consuming databases. The hunger may not be abated entirely, but it can be
controlled with proper planning and also with queries that will help you to

respond to runaway growth. Here, I will show how to battle the appetite of space-
killers with just a bit of planning, tempered with an understanding of how and why
data and log files grow to consume entire disks.
Chapter 5
There is a murder in this chapter. Someone or something is killed and most likely
you, the DBA, will be the lone killer. Of course, I am talking about processes,
SPIDs, that we see every day. Some are easier to kill than others. Some will just
not die and I will explain why, using ridiculous code that I hope you never see in
real life. The queries here were designed to help you get to the bottom of any issue
as quickly as possible without the need for DNA testing. And I am not talking
about Windows DNA, if you are old like me and remember this acronym for
Distributed Networking Architecture, precursor to .NET. No, no .NET here.
Chapter 6
To sleep perchance to dream…about failures. Here, I will introduce the sleep
killer of DBAs everywhere, where jobs fail in the hours of darkness, and the on-
call DBA is awakened from slumber several times a night in order to sway,
zombie-like to the computer to restart failed backup jobs. You cannot resolve an
issue unless you know about it and here, while discussing notifications and
monitoring your SQL Server infrastructure, we will stay up late and tell horror
stories. But, in the end, you will sleep better knowing all is well.