Friday, September 25, 2009

Query To Make Sure Data Purity Checks Will Be Performed

When you upgrade a database from 2000 to 2005 or 2008 one of the things you need to do is run the DBCC CHECKDB command with the DATA_PURITY option at least one time to set a flag in the boot page of the database and from that point forward, every DBCC CHECKDB operation will perform the data purity checks.
I have been looking for a way to programmatically check to see if the flag was set and never really found anything. The other day, I read a blog post by Paul Randal on how to tell if the flag was set.
CHECKDB From Every Angle: How to tell if data purity checks will be run?
I found this very interesting, because I have been wondering about what flag this was for a while. However, this was still too manual for me to do. It’s just easier to run the command against all of your databases if you are not sure if the flag is set.
Then I read Sankar Reddy's blog post today, and then it clicked. I can use the same logic to check for the data purity flag.
Database Internal version; Create Version and Current Version
Note: If you read the preceding blog posts, the following code will make more sense.
I created a query based on the logic in Paul’s blog and the code in Sankar’s blog to come up with the following script.

DBCC TRACEON (3604);
GO 

CREATE TABLE #DBCC 
      ParentObject VARCHAR(255),
     
[Object] VARCHAR(255),
     
Field VARCHAR(255),
      
[Value] VARCHAR(255


CREATE TABLE #DBCC
     DatabaseName VARCHAR(255),
     ParentObject VARCHAR(255),
     
[Object] VARCHAR(255),
    
Field VARCHAR(255),
    
[Value] VARCHAR(255
)


EXEC master.dbo.sp_MSFOREACHDB 
'USE ? INSERT INTO #DBCC EXECUTE (''DBCC DBINFO WITH TABLERESULTS'');
INSERT INTO #DBCC2 SELECT ''?'', * FROM #DBCC;
DELETE FROM #DBCC' 

SELECT FROM #DBCC2
WHERE Field 'dbi_DBCCFlags' AND 
      Value AND
      DatabaseName NOT IN ('master','model'

DROP TABLE #DBCC
DROP TABLE #DBCC
GO 


Next, I took a backup of a 2000 database and restored it to my 2008 instance just to make sure I had a database that fit the criteria. After running the script, you see the following results.
image
As you can see, I have several databases on my 2008 instance and the only one without the data purity flag set is the CMS database I restored from the 2000 instance. I thought this was really cool and maybe a few of you will find this useful as well.

Monday, September 21, 2009

A Simple Active/Passive Configuration Change To Reduce Downtime

Okay. 1472549010_d64a719594 It’s not what you think. By “Active/Passive”, I am talking about voice, and by “Downtime” I am talking about sleeping.

The Problem

When I started writing for Apress, one of the editors told me I needed to watch out for writing in the passive voice. He said that writing in the passive voice tends to put readers to sleep. My first thought was… “Great. I’m not an English Major. Now I have to worry about writing all this material and whether or not I am using the passive voice. Oh yeah, and how am I even going to know if I am writing in the passive voice anyway?” I am not going to get into the difference between the active and passive voice, but there is a good article here if you are interested.

The Solution

Now it’s time to get into the “Configuration Change” part of the title. Luckily, there is a setting in Word you can change that will put that nice little squiggly green line under any text that is written (oops that’s passive) you write in the passive voice.

Here is how you make the change in Word 2007.

Here is how you make the change in earlier versions.

Once you make the change, you can right-click on the underlined text and get a nice little suggestion just like you would with misspelled words.

image

In the above picture you can see that it recommended a completely new sentence for the first sentence. The phrase “has been known” makes the second sentence passive and you can rewrite the second sentence with the third sentence to remove the error.

I know this isn’t necessarily about SQL, but it is something that has helped me out a lot this year and wanted to share it.

Friday, September 11, 2009

Upcoming Speaking Engagements

I did a presentation at couple of SQLSaturdays (one in Birmingham and one in Pensacola) called SQL Server Preproduction Tasks and quickly found out that there was too much information for me to cover in a single session. After thinking a little more about the topic, I figured many times servers are not performing well because they were either not configured properly or they have not been properly maintained. That being said, I broke the presentation into two sessions; SQL Server Configuration and Automating Routine Maintenance. I plan to start presenting them as a two-part series.

I will be presenting Automating Routine Maintenance at the Columbus GA SQL Server Users Group on September 23 and at SQLSaturday #21 (which looks like it is going to be awesome btw) on October 17. I know everyone has there own way of doing things, but I would like to cover as much of the basics as I can and let people adjust based on their own judgment. If anyone wants to preview the scripts or the presentation, just ping me and I will send them to you.

Thursday, September 10, 2009

Be Careful When Uninstalling SQL 2008 R2 CTP

When SQL 2008 R2 CTP came out, I immediately downloaded it to try out the new features. I already had an instance of SQL 2005 and SQL 2008 running on the same machine. When you install SQL 2008 R2 it overwrites the management tools for SQL 2008.
A few weeks later I uninstalled SQL 2008 R2 and went to install the SQL 2008 management tools again. Every time I tried to run the install I encountered the following errors.
Unable to get installer types in the C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Microsoft.SqlServer.Management.PSSnapins.dll assembly.
Followed by…
Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0
I tried uninstalling all SQL components and anything to do with PowerShell with no success. I chalked it up to something specific to my system until I saw Mladen Prajdić (Blog Twitter) post something on Twitter the other day having the same issue.
I’m not sure if anyone else is having this issue, or if it just hasn’t surfaced very much yet. Here is a post on the SQL Forums with more details. To date, Mladen or myself have not been able to overcome this issue. I may have to reformat my laptop to get SQL 2008 installed again unless someone comes up with a resolution for this soon.

Wednesday, September 2, 2009

Finally Accomplished My First Goal Of The Year

1251923348071

After a little luck and a few months with very little sleep, I finally got a hard copy of my first book today. I was thinking about goals toward the end of last year and one of the things on the list was to get published. I sent an email to Apress on 12/1/2008 with a couple of book proposals and they responded the next day. One of the editors said “I did google you a bit, and I found your articles on SQL Server Central. In particular, your profanity piece caught my eye:”
http://www.sqlservercentral.com/articles/Editorial/63790/ If I hadn’t written a few articles for SQLServerCentral, I may have never gotten the opportunity to write this book (Thanks Steve). He said my proposals would conflict with some books they had in the works, but they were looking for someone to write a “Pro SQL Server 2008 Administration” book.

What I originally had in mind was writing a chapter or two for a book; not a whole book. That being the case, I reached out to one of my old co-workers for help and he agreed to help me out. The editor sent us all of the official paperwork and by mid December we were good to go. There was a little bit of a learning curve at first, (writing style, word templates, proper configuration for images, etc.), but once we got going it started to get a little easier.

This was one of the hardest goals that I set for myself and was actually able to accomplish. Basically, if you want to do something, write it down as a goal and then do it. This time last year, I never though I would have a book published (I always hated writing in school btw) and now I am finishing up my second one and doing a little tech reviewing on top of that. Now I guess I can move on to some of the other goals I had. Where is that list anyway…