SQL Saturday Rochester Recap and Speaker Feedback

dmmaxwell1 Comment

Rochester, you all are so, so kind… :-)

First things first: Big thanks to Matt Slocum, Andy Levy and their entire crew, the sponsors, the speakers, the volunteers, everyone who put together such a fun event. SQL Saturday Rochester was a great time.

I started my day off in Colleen Morrow’s session: “So You Want To Be A DBA?” It’s a question I’ve posed to quite a few people recently, as I’ve been approached by multiple people interested in the career track. I always kind of struggle to describe what I do and what makes a good DBA. Colleen doesn’t struggle with this at all. She did a great job, presenting all the main points, pains and proclivities of a DBA, and even had a fun quiz in her session. The quiz showed the background of several well known people in the community and the attendees were asked to match the person to the bio. I was a bit surprised at a couple of them, which was very cool.

Next up, I went to Adam Belebczuk’s session, “XML Without Xanax.” My knowledge of XML is sorely lacking. (Read, just enough to pass an exam.) So, I was desperate for some help and better insight into how SQL stores and actually works with XML. Adam didn’t disappoint. One of the really good takeaways I got from this session was the idea of using an XSD schema definition as a sort of column constraint, where XML is being stored. Something I’m definitely going to have to look into.

After a break to make sure my laptop was charged, I stopped by David Klee’s session, which was called “My Brain Has Blue Screened.” Rather than deep technical content, this was a bunch of DBAs and the like, gathered around swapping war / horror stories. Many of which had me cringing. Excellent idea for a session, and something we may have to do at SQL Saturday Columbus this year, if he’s willing. (Hint, hint…)

After a very tasty lunch, I stopped in to see Kendal Van Dyke set up a SAN-less cluster with SIOS’ Data Keeper product. This wasn’t something I had seen before in the wild and it was certainly interesting. We’ve been kind of trying to move away from failover cluster instance at work in favor of Availability Groups. I’ll have to check this out to see if it may be a better fit for us.

I skipped the next session to re-run the demos for my own two sessions in the afternoon. Good thing I did so. I made a few final adjustments to one of my demos and was ready to rock. My first session, “The Usual SUSPECTs”, which deals with database states, went off without a hitch. “DBA 911 – Database Corruption” went pretty much as it usually does. I was able to get through both sessions without modifying too much, though I did gloss over a couple of things due to the time limit. I was trying very hard to make sure the attendees were able to get to the raffle on time at the end of the day, since you do have to be present to win.

I don’t have much in the way of speaker feedback this time, since the feedback I received was universally positive across the board. The attendees must have been very kind people. For the Usual SUSPECTs session, I did get a pair of really great comments under the heading “How will you use the information you learned here?”

“Not sure if I will, but good to know.” – Trust me, one day, you will. :-)

“Test at office on production. No, just kidding!” – If you do, let me know. I could use the consulting work…

And from the DBA 911 session:

How will you use the information you learned here? “Test Backups”.

My work here is done. See you in Philadelphia? :-)

Thanks for reading.

-David.

Quick Speaking Event Roundup

dmmaxwell1 Comment

Wow… so much on the radar right now.

This Saturday, I’ll be speaking at SQL Saturday Rochester. I’ll be giving two sessions. First, The Usual SUSPECTs which covers some of the states that a database can be in other than “online”. Second, DBA 911 – Database Corruption, which covers on-disk data corruption and repair techniques. I’m really looking forward to it, and hope to see many of you there.

Next month on June 6th, I’ll be speaking at SQL Saturday Philadelphia, giving my DBA 911 – Database Corruption session. That session has certainly been popular. I think I’ve presented it about a dozen times. :-) Both Rochester and Philadelphia are new cities for me, and I’m really looking forward to enjoying my visits.

I’m also excited to say that I’ve been selected to speak for this summer’s 24 Hours of Pass. This is a great event, and the lineup of speakers is awesome. I’m grateful to be included in such a group. I’ll be presenting my latest session, Turbo-Charged Transaction Logs, on transaction log internals and performance. I encourage you to register, and look forward to ‘seeing’ you there.

Last, but most definitely not least, SQL Saturday Columbus is coming up on July 11. We just extended the speaker submission deadline through the weekend for those of you who still have to submit sessions. Please do so; we’d love to hear what you have to say.

Thanks for reading, and I hope to catch up with you at one of these events, or another.

-David.

Database Locks – Why?

dmmaxwellLeave a comment

Many moons ago, I was attending a session at a SQL Saturday like I often do, and I remember the presenter showing a demo of locks on his local instance of SQL Server. While it was not specifically a session on locking, he was describing the locks at various levels, and remarked on the database locks, “I don’t know why you’d need to lock an entire database.”

I love “I don’t know.”  It’s a great starting point for learning many, many things. For purposes of discussion, I’m going to keep things super-simple.

The idea of a “locking” is really best described as controlled access to a given resource. There are a couple of different kind of locks in SQL Server, which I will roughly classify as DML and DDL locks.  Data Manipulation Language locks are those that are used by DML statements, such as SELECT, INSERT, UPDATE, and DELETE. You have probably heard of Isolation Levels in SQL Server, and this is what those isolation levels affect; the behavior of those DML locks. Data Definition Language locks control the behavior of DDL statements that alter objects within the database, or the database itself, such as CREATE, ALTER, or DROP statements.

Now, let’s talk about shared vs. exclusive locks. They do pretty much exactly what they say on the label. If a process has an exclusive lock on a resource, such as a data row or an object, then nothing else can touch that resource. If the lock is a shared lock, then other processes can access that resources at the same time, as long as they don’t need an exclusive lock.  Depending on whether we’re talking about DDL or DML, this can change once you start getting into things like Snapshot Isolation, but for now, we’ll keep it simple, and just say that exclusive and shared locks are incompatible. You can’t get a shared lock on a resource, while another process already has an exclusive lock on it, and vice-versa.

Certain actions that would affect an entire database, such as changing collation, enabling snapshot isolation or even simpler: dropping the database, require exclusive access to the database in order to do so. I can’t drop a database while you’re still querying it. Not only would that be rude, it would violate the ACID principles of database transactions, which is how SQL Server and other relational database platforms work. So any action that requires access to a resource within a database, comes with a shared lock on that database. An exclusive lock on the database can’t be obtained until all shared locks are released.

The “exception” to this is when issuing an ALTER DATABASE command using WITH ROLLBACK. I use the quotation marks with the word “exception” since we’re still honoring the ACID properties, we’re just forcing all running transactions with shared locks on the database to roll back, freeing up the database for an exclusive lock. Obviously, this is something you want to do very carefully, and only when you’re sure that rolling back all running transactions isn’t going to cause a problem for your application users or developers, or anyone connected to that database.

Thanks for reading.

-David.

PROC: Convert LSN from hex to decimal for fn_dblog()

dmmaxwell4 Comments

I was working out some code recently for a presentation on transaction log internals. Part of the presentation was showing the records in the transaction log, using the system function fn_dblog. I’m not going to go into a full description of fn_dblog right now, but I will get into some of it’s uses in a later post. It’s pretty fantastic. :-)

As part of the demonstration I was writing, I wanted to only show a few specific records in the transaction log. Sometimes, that’s easily done by using a WHERE clause with fn_dblog, and specifying a specific column, where that column is not null. For example, if you were looking for the number of page splits in the current transaction log, you could use:

select count(*)
from fn_dblog(null,null)
where [New Split Page] is not null;

Sometimes, though, you want to show a range of records and cannot find a good column to filter by. Or maybe you just want to start at a specific place in the log without regards to a particular column.  In that case, fn_dblog accepts a start LSN, and an end LSN as parameters. You just pass in those LSNs instead of the (null,null), and it will only show the log records that fall between those two LSN numbers. Simple, right?

Well, I wish it was that simple. You see, when you select from fn_dblog, the [Current LSN] column that is returned shows the LSNs in a three-part hexadecimal format; three hexadecimal numbers, separated by colons, like “000000a4:000000d8:0001”. However, if you want to pass those back to fn_dblog as parameters, you have to pass them as three=part decimal numbers, instead. So instead of…

select * from fn_dblog('000000a4:000000d8:0001',null);

…you would convert each hexadecimal part to decimal and…

select * from fn_dblog('00000164:00000216:0001',null);

And the first record that would be returned would have a [Current LSN] number of “000000a4:000000d8:0001”.

Nope, doesn’t make sense to me either.

However, rather than deal with the pain of doing the conversion math every single time I had to grab an LSN during my demo, I created a stored procedure to use for it. I placed it in the [master] database, and just call it every time I need to convert from one format to the other. The code is below.

Now, I’m in no way a developer, so I figure someone out there has to know a better way to do this. If you do, please leave a comment or drop me a line. I’d love to hear it.

Thanks,

-David.


USE [master];
GO

IF (SELECT OBJECT_ID('dbo.ConvertLSNHexToDec')) IS NULL
BEGIN
EXEC ('CREATE PROCEDURE dbo.ConvertLSNHexToDec
AS
PRINT ''STUB VERSION - Replace with actual procedure.'';
')
END
GO

ALTER PROCEDURE dbo.ConvertLSNHexToDec
/*******************************************************************************
AUTHOR:
David Maxwell

DATE:
April 2015

DESCRIPTION:
Converts the LSN in fn_dblog() from a three part hex to three part decimal,
so you can pass it back to fn_dblog() as a parameter. (Because inconsistency.)

DEPENDENCIES:
none

TEST EXEC:
EXEC dbo.ConvertLSNHexToDec @lsn = N''
GO

TO DO LIST:
1. Find a better way to do this other than this proc.
2. Add an option to do this either in three part format or an integer string.

NOTES:
Please send any comments or suggestions to dmmaxwell@gmail.com.

CHANGE HISTORY:
Date - Author - Change
April 2015 - David Maxwell - initial version - pending feedback

*******************************************************************************/
@lsn nvarchar(22) = NULL /* The LSN from fn_dblog that you want to convert */
AS

set nocount on;

declare @cmd nvarchar(2048), /* Dynamic SQL required for this, explained below */
@declsn nchar(22); /* The resulting, decimal-formatted LSN. */

/* In order to do this and keep everything in a three part format, I have to break
up the LSN into its three component parts via string parsing. So this makes the
hux numbers turn into strings. You can't convert a string to an int.

So we create a dynamic SQL query and execute it, bypassing any conversions
when we break this apart into strings.
*/

select @cmd = '
select
right(replicate(''0'',8) + cast(convert(int, 0x' + left(@lsn,8) + ') as nvarchar(8)),8) + N'':'' +
right(replicate(''0'',8) + cast(convert(int, 0x' + substring(@lsn,10,8) + ') as nvarchar(8)),8) + N'':'' +
right(replicate(''0'',4) + cast(convert(int, 0x' + right(@lsn,4) + ') as nvarchar(4)),4)
';

print @cmd; /* This is just here for troubleshooting, if this fails. */

/* Execute the dynamic SQL, and output to the @declsn variable. */
exec sp_executesql @stmt = @cmd, @declsn = @declsn output;

/* Here's your LSN. */
return @declsn
go

Why you should speak or volunteer at SQL Saturday Columbus.

dmmaxwell1 Comment

“Hi. My name is David, and I’m a SQL Saturday addict.”

“Hi, David…”

If you’ve been following my posts, you’ll no doubt have noticed I’ve spent a good bit of time on talking about SQL Saturdays on here recently. SQL Saturday is a big deal for me. No – scratch that – it’s an all caps BIG DEAL. Why? If for no other reason then it’s the number one thing that has contributed to my career over the last five years.

Let’s break that down, shall we?

1. People. This is the number one reason to attend a SQL Saturday, period. The people you will find at these events are amazing. Not only have I made many valuable professional contacts, I have made some life-long friends as well. At how many professional events can you say that? Also, consider this: How many times in your life can you remember being in a room with a couple dozen people, and being able to explain what you do in five words or less… and everyone *gets it*.  Rare, right? Not at SQL Saturday. Here, you are one of us.

2. Knowledge. Tons of it. Everywhere. Not just in the session rooms, either. Conversations in the hall, during breakfast or lunch, even after the event… There’s a ton of knowledge being shared on just about any aspect of SQL Server that you can think of. The learning opportunities are limitless. I’ve learned more than I thought I would just by talking to people outside of the sessions. That issue you’ve been dealing with at work and can’t seem to find a good solution to? There’s a good chance the person sitting next to you has seen that as well. Maybe you should ask them about it?

3. Community. This could easily have fallen under ‘people’ but I wanted to give it its own separate space. A community is much, much more than just a collection of people. Remember that the people who put on this event are volunteers. This includes the speakers. They don’t get paid for putting on these events, they simply do it because they love it. And when I say they love ‘it’ I don’t mean SQL Server, but the community of professionals that make it better, faster, more powerful, and more valuable to the businesses and organizations that use it. Giving back to that community is kind of like a feedback loop. The more we put into it, the more we get out of it, and the better it gets.

That brings me to the point of this post. I want you – yes YOU – to consider submitting a session for SQL Saturday Columbus. Speaker selection is open until May 15. Have you given talks at work, or maybe for a local user group? Perfect – you’d be an excellent candidate. Need help with the submission process or coming up with your abstract? No problem – contact me via my contact page and I’ll help you out!

And if you’re not interested in speaking, then maybe volunteering is for you. We’ve got all kinds of things that need to be set up, taken down, monitored, moved, and generally managed the day of the event. We need all kinds of people to do it, as well. Volunteers are what make these events run, and make the community great. If you would like to volunteer, you can sign up on the SQL Saturday site, or simply contact me.

I hope to see you there.

Thanks for reading.

-David.

 

SQL Saturday Madison – Recap and Speaker Feedback

dmmaxwell3 Comments

First things first: THANK YOU to the organizers, volunteers, speakers and sponsors for putting on yet another amazing SQL Saturday in Madison WI. I had a fantastic time, as I expected. Well done all around.

I attended some excellent sessions that day. I particularly want to call out Andy Yun (B | T) for an excellent session on SQL Server Data Types. I got a couple of ideas from attending his session that I want to add to my own presentation. Andy did a great job illustrating the advantages of selecting the right data types for the data you have, and more importantly, what you want to do with it. I was even able to use some of his points in discussions at work already.

One of the things I really like is the feedback I get from my sessions. I’m consistently surprised how many people show up to my sessions in the first place. This time, I got some fantastic feedback this time, from some obviously experienced people. I’m going to condense and respond to some of that here.

“Work on not talking to the monitor during demos.”  Yeah, I have a problem with this. Will do!

“Explain named transactions more.” Got it. I think I mention naming a transaction in passing, but I don’t really explain what a named transaction is. I’ll add a bit about that.

“Do ‘Who Am I?’ first. Disjointed transition between goals and self.”  Good point. Thanks!

“Explain how log backups affect the tran log and the difference between simple and full recovery models.” Ok, time for me to confess something. For some reason I don’t remember, I thought this was supposed to be a 60 minute session instead of a 75 minute session. So I had cut a couple things out, and one of them happened to be an explanation of log behavior in the various recovery modes. I should have had that handy and put it back in. I will be adding it back to the session soon.

“Add some visual drawings in the slides explaining VLF reuse and the circular use of the transaction log.” Yep – this was another thing I cut for time. Also, I wasn’t happy with how my initial iteration of that came out, and planned to re-do it anyway. Plus, I thought it made more sense the way it looked in the demo. So yes, this will be back.

“Light green font was hard to read from the back.” I changed color schemes on this presentation about six times. Apparently, I didn’t pick a good one. :-)  I’ve already planned on changing that, as well.

So thanks to everyone who provided feedback to me. I really do take it seriously and consider it very valuable. Presentation materials are available on my resources page, linked at the top.

That’s that! See you in Rochester? :-)

Thanks for reading.

-David.

SQL Saturday Roundup – April 2015 Edition

dmmaxwellLeave a comment

This year is picking up nicely for events for me. After thoroughly enjoying SQL Saturday Nashville in January, and SQL Saturday Cleveland in February, I’m really looking forward to my next few SQL-related trips. I’ve updated my schedule page accordingly, but wanted to call out the next couple of events I’m going to be at. If anyone in those areas, or travelling to them for the events, wants to get together and chat, please let me know.

April 11 – SQL Saturday – Madison WI

I’ll be heading to SQL Saturday Madison for the second year in a row, and I’m very excited for a couple of reasons. First, I’ll be presenting my brand new session, “Turbo-Charged Transaction Logs“. In that session, we’ll dig a little bit into the transaction log internals, show how SQL logs what it does, and some ways to make the logging process faster.  Second, and more importantly, I’m looking forward to spending time learning and relaxing with the fantastic people of the Madison SQL Server community. You’ve got a good crew up there, folks. Looking forward to seeing you all.

May 4 – 8: SQL Skills IE:PTO2 – Chicago IL

OK. This isn’t a SQL Saturday event, but I will be travelling to Chicago for a week to get schooled on SQL Server performance by one of the best companies in the business, SQL Skills. At some point, my brain is going to need a break so if you’re in the Chicago area and want to catch up, or just chat about SQL Server, drop me a line. I should have an evening or two free. :-)

May 16 – SQL Saturday – Rochester NY

This will be my first time speaking for SQL Saturday Rochester, and I’m stoked. The schedule hasn’t been finalized, but the approximate schedule is up, and if it sticks, I’ll be doing two sessions on disaster recovery, back to back. A whole afternoon block of breaking and fixing. Sounds like fun!

One session I’ll be presenting is: “The Usual SUSPECTs: When Good Databases Go Bad“.  This session is all about database states. The good, the bad, and the ugly, as it were. In addition to talking about things like moving OFFLINE database files and rolling a database forward through its transactions using a STANDBY restore, we’ll look at what happens when a SAN failure puts a database in to RECOVERY_PENDING, or when a disk crash lands you into SUSPECT mode. I’ll show you how to recover from those states in a safe way.

The other session I’ll be doing is “DBA 911 – Database Corruption“. In that session, we’ll look at the basic definitions and terms of database corruption and repair. We’ll talk about some of the different kinds of corruption, how to look for and troubleshoot corruption issues, and when it’s best to repair vs. restore. As in all things Disaster Recovery – preparation is key, so we’ll also cover steps you can take to ready yourself for a corruption problem. (Hint: have you hugged your backups today?)

Thanks for reading, and I hope to see you at one of these events.

-David.

Passing Exam 70-461 – Querying SQL Server 2012

dmmaxwell4 Comments

tl;dr – Study. A lot. Hard.

I recently took and passed MS exam 70-461. Here are my reflections on it. I am, of course, forbidden to tell you exactly what’s on the test, and I don’t know what’s in the entire bank of questions, so this is more of a guideline than anything else. Your mileage may vary, as usual.

Study Materials

I had previously been through the official class with a local training company, as well has having studied the official MS Press book, which I got from the library. (If you’re buying the books just to pass the tests, you’re wasting money.) However, I didn’t take the test immediately after. I’m kind of glad I didn’t since we didn’t actually cover some of the most important things on the exam in the course material. I got my best results by using the MeasureUp practice exam, and reading the suggested BOL articles on MSDN. Between those two things, as well as mucking around with the code on my own, I feel like got enough of a review of the material and exposure to new stuff to be able to pass.

The MeasureUp practice test isn’t perfect – not by a long shot. I sent in at least three corrections on their test. The only time that actually caused me a problem was when I answered a question incorrectly, and was shown an answer in the “explanation” that wasn’t an available answer for the question. Once or twice, the “correct” code was just flat-out wrong. However, even that was close enough, and I already had enough experience with the concept to get what they were driving at. Also, it’s not exactly cheap, but not much more expensive than the book. I would get the book from the library and pay for a 30 day pass to the practice exam. Even with the minor issues I described, I would still recommend it as a study tool.

One more thing about the MeasureUp exam: It’s nowhere near like the actual test, no matter how much they say it is. It’s a great study tool, but a poor excuse for a simulation of the actual exam. Example: Most of the questions on the MeasureUp exam are multiple choice, with some drag and drop. On the official exam, you will be asked to actually write code. So don’t get used to just picking the correct code snippet out of the list. Know how the code works, and be able to actually write it. This is where practicing with the examples in a copy of Management Studio will come in handy.

Subject Matter

There are four sections to the test. I’ll take each one in turn and talk a little about the types of things I think you should practice. Now the way I describe these doesn’t exactly line up with the exam objectives, but I think this is a decent way to study them, since they make more sense to me in this grouping.

Create Database Objects – I did pretty well on this section. Know how to create tables to work with FILESTREAM, and brush up on the ins and outs of views. Pay particular attention to options required for indexed views like SCHEMABINDING, as well as what VIEW_METADATA and CHECK OPTION do. Finally, know how SEQUENCE objects work, and when they’re a good choice.

Troubleshoot and Optimize – I did moderately well on this. Here, I’d recommend knowing isolation levels really well. Also know what the various *physical* join types are, and when they are to be expected. Know how to get execution plan information from T-SQL, and how to optimize queries by doing things different ways. Many of the questions involved optimizing queries not by altering an existing function or CTE, but by taking a different approach all together.

Modify Data – This was my best section. Know the intricacies of MERGE, as well as INSERT/UPDATE/DELETE. Know what the set operators do, like UNION (ALL), INTERSECT and EXCEPT. Often, you’ll see those being used to filter a result set in a derived table, so keep in mind what their output should look like. I’d also recommend careful review of the details of creating stored procedures and functions. More so table valued functions than scalar ones, at least in my experience. COALESCE and IIF are important functions. Know those well.

Work With Data – I did poorest on this section. Caveat: I am not a developer, and rarely spend my time writing queries to work with business data. If I were to study for this section again, I would spend a lot more time on the FOR XML set of functions, things like GROUPING SETS and ranking functions like RANK vs DENSE_RANK. I would recommend reviewing data types very carefully. A few of the questions relied on some of the more intricate details of them. (i.e., there’s only one date/time type that stores time zone information, and you should know the precision levels of the various non-integer numeric types.) Also, I recommend reviewing some of the more obscure types like ‘cursor’.

Exam Generalizations

Most of passing a Microsoft exam is a matter of reading the questions extremely carefully, and understanding what they’re asking for. No lie: I spent the most amount of my time on the very first question, trying to decide what a particular word in the question meant. I definitely recommend marking questions, and returning to them later if they take you more than a few minutes or so to answer. There were 42 questions in 120 minutes on my exam. I completed the test in a little over an hour, but going back to that first question, and deciding what they really wanted as the result took the bulk of my time. Don’t worry about skipping questions – you’ll get the chance to come back and review anything you marked or skipped at the end of the exam. When in doubt, mark it and come back to it later. You’ll make up the time on the simpler, “gimme” questions, and there are quite a few of those as well.

Often, you can tell what the correct answer is just by the context clues in the question. i.e., You’re joining data from two tables, and you want a result set for the first table, regardless of whether or not there are matching rows in the second table… it’s a good bet the correct answer is going to have a LEFT JOIN in it, effectively eliminating answers with INNER JOIN in them quickly. Process of elimination is a perfectly valid way to take this exam. Use it.

Hope that helps.

-David.

Corruption Bug: Common Criteria and SA.

dmmaxwellLeave a comment

When I talk about recovering from database corruption, I mention that occasionally corruption can be caused by bugs in SQL Server. Such things are very rare, but they do happen. Case in point – MS just recently updated an article stating such. KB2888996 details a specific set of circumstances where actually running CHECKDB can cause corruption.

Scary, huh?

I thought it would be an interesting exercise to reproduce those results, and then test the patch for myself. Feel free to follow along.

WARNING: Once again, we are causing intentional damage, here. Do this only on a test system where you can afford to simply reinstall SQL Server if everything goes to hell. And before you do anything else, take a backup of your master database. Do it now. I’ll wait. <muzak plays…>

Ready? Ok, here we go…

  • Make sure you’re running an affected version of SQL Server. It should be Enterprise, Standard or Developer edition of SQL Server 2014, down to 2008 R2. See the KB article mentioned above for the specifics.  Note that you’ll need to be running a version earlier than the hotfixes or cumulative updates that they mention in the fix.
  • Enable common compliance criteria on your SQL Server instance using:
EXEC sp_configure 'common criteria compliance enabled',1;
GO
RECONFIGURE WITH OVERRIDE;
GO
  • Install the auditing trace procedures, available on Microsoft’s SQL deployment page.
  • Restart the SQL Server service. Common Criteria is one of those rare cases where just running RECONFIGURE WITH OVERRIDE won’t actually cause the change to take effect.
  • Now log into the server as SA, and run:
DBCC CHECKDB(master) WITH NO_INFOMSGS, DATA_PURITY;

Remember that out of the box, data purity checks on master and model are not enabled. That’s why I’m including it in the command above, even though it’s a default from 2005 going forward.

In my case, when I ran CHECKDB, I got the following:

Msg 2570, Level 16, State 2, Line 1
Page (1:85), slot 26 in object ID 58, index ID 1, partition ID 281474980511744, alloc unit ID 72057594037993472 (type "In-row data"). Column "name" value is out of range for data type "nvarchar".  Update column to a legal value.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysbinobjs' (object ID 58).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'master'.

So there is our data purity error. These are kind of a mixed bag. You can’t run a repair statement to fix them, but then again, you don’t risk losing any more data than you already have.  What it means is that SQL Server cannot interpret the data within a column, because the data isn’t valid or within the correct range for the data type of that column. In this case, we have an nvarchar column with a single byte of data in it.

Now you’ve probably guessed by this point, and if you haven’t then I’ll make it clear: the corruption in this case was NOT caused by CHECKDB. It has to do with some peculiarity in the security setup here. Somehow something is being logged in such a way that is bypassing the data type check, and inserting a record into this table that doesn’t fit the data type. Let’s take a look at this using DBCC PAGE.  If you’re not familiar with DBCC PAGE, please see my previous post on it, DBCC PAGE to the Rescue.

DBCC TRACEON(3604); /* Enables output to console */
GO
DBCC PAGE (1,1,85,3); /* dbid 1, file 1, page 85, style 3 (show me everything) */
GO

After bringing up the page, we do a search for slot 26, and find:

Slot 26 Column 4 Offset 0x2f Length 1 Length (physical) 1
name = INVALID COLUMN VALUE

Just for comparison, here’s what a normal, single-character value of that type would look like:

Slot 0 Column 1 Offset 0xb Length 2 Length (physical) 2
col1 = a

See the problem? Because this is an nvarchar type, the minimum length of a value is going to be 2 bytes. One for the data, and one for the unicode data that goes with it. (See here for the details on what I mean regarding the data types.) So, the value stored is too small for SQL Server to attempt to interpret as a valid nvarchar.

In the demos I do, I fix a data purity error with an update statement. Unfortunately, you can’t directly access this particular table. It’s a hidden system table, so there’s no way to even select from it, much less update it.

So what *can* we do? Restore master from a backup. Well, technically we could just ignore the error by running CHECKDB against master with PHYSICAL_ONLY, which would just avoid data purity checking. You can also disable data purity checking on master by enabling trace flag 2566.  Keep in mind, though, that disabling data purity checks means that if any new data purity issues crop up, you’re not going to know. I’m not in favor of this option, so let’s go to that backup we took at the beginning of this exercise.

Restoring master is understandably different than restoring any other database, and here’s how you have to do it. You need to stop the SQL Server service, then start it from the command prompt in single user mode, using the -m switch. In my case, I have multiple instances of SQL running on this particular machine, so I also need to specify which instance using the -s switch. Like so:

X:\<path to sqlservr.exe>\sqlservr.exe -m -s mssqlserver

Note that specifying “mssqlserver” will start the default instance, which is where we’re testing this. In a separate command window, I logged into my instance using sqlcmd.exe and restored master with a plain old restore command:

RESTORE DATABASE [master] FROM DISK = 'X:\<path to file>\master_full.bak' WITH REPLACE;

When the restore of master is complete, SQL Server will automatically shut down. I was already at the command line, so I restarted both SQL and the SQL Server Agent with net start…

Net start mssqlserver
Net start sqlserveragent

Logging in with my user account, (not sa), and running CHECKDB as above, shows that the database is now clean.

So to avoid this, should you fit the criteria at the beginning of this article and on Microsoft’s KB article, please be sure you download and install the appropriate update for your instance of SQL Server.

Keep checking those databases for corruption, folks.

Thanks for reading.

-David.

To Backup or not to Backup. In Development, is the question.

dmmaxwell1 Comment

Recently an old debate has resurfaced for me where the central question is: Do we, or do we not back up the development environment? Say it with me, now:

“It depends.”

I have seen a trend towards reducing or even eliminating the backups in development or test environments, since they don’t get as high a priority as the production environment. Development isn’t a client- or public-facing area of the business, so it gets lower priority when it’s down. Dev and Test *are* the production environment for your developers, though. That’s where they work, day in and day out. How comfortable would you feel telling a developer that they’ll have to re-do the last couple of hours worth of work? How about the last day or so? More?

Another common argument that I hear against backing up the development environment is the storage cost of what could be considered duplicate data, since Dev and Test are usually copies of Production. Fair enough, but if development is not as high a priority to the business, wouldn’t we be able to save costs on backup storage using things like de-dupe, snapshots, and compression?  In addition to storage cost, how much are you paying your developers? I assume they’re still drawing a salary while the environment is unavailable, or while they are re-doing work that was lost due to a database issue. Is that not also a cost?

I’m heavily skewed in favor of backing up the development environment. As the DBA, I’m very nervous about having anything without a backup. But I have to admit that there are some situations where backups just aren’t as useful as they should be, and it’s easier to just recreate instead of restore. The key question is this: What is your RPO/RTO for your developers, and how will you ensure that? Most of the time, backups are part of the procedure. Sometimes they aren’t.

One situation I have seen more than a few times is where production databases are regularly copied to the development or test environments, and the latest version of database changes are applied. Not just to make sure that the databases are code-current, but this has the additional benefit of testing the deployment scripts as well. In this case, is it worth taking a backup? Again, it depends. What is your SLA for development, and is it faster to restore the development database, or to re-do all the changes?  If it takes an hour to restore, but an additional hour to re-apply the changes, is that extra hour worth saving a little bit on storage cost for backups?

There’s no clear-cut answer, but I know that I restores save careers. I will almost always advocate for backing up all my environments.

What about you?

Thanks for reading.

-David.