Database Restores – Zeroing Out Log Files

IFI_TwitterQuestionI recently came across a question on the #sqlhelp Twitter feed that I thought was interesting.  The question pertains to whether or not SQL Server zero outs the log file when doing a restore on an existing database.

We already know that even with instant file initialization, the log file is always zeroed out upon creation or file growth.  But what about a restore?  Given the restore is a page by page copy of the data, shouldn’t it just over lay the existing file?

Let’s find out!

In order to test this, we will:

  1. Create a new database and a new table
  2. Insert some data into the new table
  3. Take a backup of the database
  4. Truncate the table
  5. Restore the database
  6. Look at the logs to see what is happening

Let’s get started!

-- Create our database 
USE LogFileRestoreDemo
CREATE TABLE Table1 (id int identity(1,1), number INT)
-- in order to increase the log file, take a backup first to set the log chain.  This will prevent the log file from rolling around.
TO DISK = 'C:\Backups\LogFileRestoreDemo_FULL_20150318.bak'
-- Insert 100K random numbers
INSERT INTO dbo.Table1 (number)
GO 100000
-- Let's check, we have data right?
SELECT TOP 100 ID from dbo.Table1

Now that we have inserted some data, the log file should be a decent size.   You can confirm the file size by looking at the physical LDF file within Windows or you can use this script:

-- Do we have some size to the log file?
SELECT size, size*8 as 'Size (Kb)', name, physical_name, type_desc FROM sys.master_files
WHERE database_ID = db_ID('LogFileRestoreDemo')


We know now that the log file has some size to it, let’s take another backup of it and then truncate the table.

TO DISK = 'C:\Backups\LogFileRestoreDemo_FULL.bak'

Turning on two specific trace flags will help us see some of the magic that takes place during the restore.  Keep in mind that truncating the table is just a way to confirm that the restore worked as expected.  

Trace Flag 3004: this flag will allow you see when SQL Server is doing zeroing out operations.  This works for both the data files as well as log files.

Trace Flag 3605: this flag will output results of certain DBCC commands to the SQL Server log.  It’s very similar to trace flag 3604 which outputs the results of certain DBCC commands to the results window.

Note that both of these trace flags are undocumented so you won’t find specific information about them directly from Microsoft.  However, there are several blogs on the internet that discuss them.

USE [tempdb]
DBCC TRACEON(3004,3605)
FROM DISK = 'C:\Backups\LogFileRestoreDemo_FULL.bak'
DBCC TRACEOFF(3004,3605)

With those trace flags on, we can now look into the SQL Server log and see what it’s doing.


We can clearly see that SQL Server zeroed out the pages required for the log file. This shows that even if the database is already present on the server, the log file gets zeroed out when doing a restore.

As we have shown here, if you have to restore a database where the log file has grown to a significant size, SQL Server will have to zero out the pages for it.  This could take a significant amount of time to do so.  If you find yourself in this situation, please make sure that you plan accordingly.

As a final clean up, make sure that the trace flags are turned off.  If they are left on you would potentially put a lot of restore information into the log file.  Let’s also drop the database to clean everything up.

-- This will show you all of the trace flags currently enabled
USE tempdb
-- Drop the database
DROP DATABASE LogFileRestoreDemo




Posted in Administration, Internals | Tagged , | Leave a comment

Quick Script: Finding Columns

Locked Lock, Tom Magliery,

Is this secured enough?

For any data professional, data security should be an utmost priority.  Having unsecured data lying about can cause major issues, if discovered.  I’m speaking of software security, more specifically data encryption or data masking.

Finding the instances of unsecured data in an environment can be a pain.  Last year, I had to look for unencrypted data within a non-production environment.  In this case, I was looking for Social Security or also known as Tax Identification Numbers.

Red-Gate offers a tool, SQL Search, which is completely free and completely awesome. I’ve used this tool previously on many occasions .   The problem that I had was that I wanted to look across all servers within an environment utilizing a Central Management Server.  I haven’t been able to find a way to accomplish that with SQL Search so I set out to create my own script.

There are some things to keep in mind about this script,

  1. It makes assumptions that the column is named in a certain pattern.
  2. It uses dynamic SQL.
  3. It uses a WHILE loop.

However, even with these nuances,  the script works pretty well to help find potential sensitive data that might not be encrypted across multiple servers.   Of course, I’m also assuming that encryption is intended to be handled by the database and not some other process.

DISCLAIMER:  While I’ve been able to run it across multiple servers, over several hundred databases without an issue, your mileage will vary.  Please test accordingly.   NEVER run code that you find on the internet against your Production systems without testing first.  You have been warned!

-- Author: John Morehouse
-- Date: March 2015
-- E:
-- B:




--You may alter this code for your own purposes.
--You may republish altered code as long as you give due credit.
DECLARE @dbs AS TABLE ( name sysname, processed BIT)

-- check for the table and drop if in existance
IF object_id ('tempdb..#results') IS NOT NULL
DROP TABLE #results

-- Create our new table
CREATE TABLE #results ( dbName sysname
, tableName VARCHAR (100)
, schemaName VARCHAR(100)
, columnName VARCHAR (100)
, DataType VARCHAR (100)
, MaxDataLength INT
, theRowCount INT )

-- Fetch a list of databases
INSERT INTO @dbs ( name, processed )
SELECT name, 0 FROM sys.databases
WHERE database_id > 4
AND [state] = 0 --online

-- Loop through the list of databases; use dynamic sql to look for specific columns
WHILE @x <= (SELECT COUNT( 1) FROM @dbs WHERE processed = 0 )
SELECT TOP 1 @dbName = name FROM @dbs WHERE processed = 0

SET @sql =
'INSERT #results (dbName, tableName, schemaName, columnName, DataType, MaxDataLength, theRowCount)
SELECT ''' + @dbName + ''',,,,, c.max_length,p.rows
FROM [' + @dbName + '].sys.columns c
INNER JOIN [' + @dbName + '].[sys].[tables] t ON c.object_id = t.object_id
INNER JOIN [' + @dbName + '].[sys].[schemas] s ON t.schema_id = s.schema_id
INNER JOIN [' + @dbName + '].[sys].[systypes] st ON c.user_type_ID = st.xusertype
INNER JOIN [' + @dbName + '].[sys].[partitions] p ON t.object_id = p.object_ID
INNER JOIN [' + @dbName + '].[sys].[allocation_units] au ON au.container_ID =
WHEN au.TYPE IN (1,3) THEN p.hobt_id
WHEN au.type IN (2) THEN p.partition_id
OR LIKE ''TIN_TX%'') -- looking for specific column name
AND c.OBJECT_ID > 100 -- exluded system objects
AND p.index_id IN (0,1) -- Heap or Clustered Index
AND au.type = 1 --IN_ROW_DATA only'

-- used for debugging
--PRINT @sql

EXEC sp_executesql @sql
, ERROR_MESSAGE () AS 'Error Message'
, ERROR_NUMBER () AS 'Error Number'
, @dbName AS 'Database'

SET processed = 1
WHERE name = @dbName

-- show us the goodness
SELECT dbName, tableName,schemaName,columnName,DataType,MaxDataLength,theRowCount FROM #results

The script will return a number of things:

  • Table Name
  • Schema Name
  • Column Name
  • Column Data Type
  • Size of the Column
  • Number of rows

Using this script in conjunction with a Central Management Server, I’ve been able to find out where potential sensitive data might be residing.  I used this list of columns to investigate whether or not it contained sensitive data.  If it did, I would either mask it or encrypt it, depending on our company policies.

I haven’t run into any issues with running the script in Production, however, I would again advise caution.  Your mileage will vary.  Test accordingly!


Posted in T-SQL | Tagged , | Leave a comment

SQL Saturday 381 – Richmond, VA

sqlsat381_webI love presenting and I love visiting new places so on March 21st, 2015 I’ll be speaking at SQL Saturday #381 located in Richmond, Virginia.  This will be my first time presenting at their SQL Saturday and I’m really looking forward to the event.  I’ll get to hang out with some awesome #sqlfamily members. Hopefully I’ll get to get to see a some of the surroundings as well.

One really cool thing about this event is that they are offering you the chance to get a Microsoft Technology Associate (MTA) certification for FREE!  On Friday March 20th, 2015 they will be hosting a half-day lab & testing session.  If you are interested in obtaining a certification, this would be a fantastic way to get one at no cost to you.  Make sure to check out the registration page for further details.

If you have nothing better to do on Saturday, March 21, 2015, come on out and get your learn on.  The schedule for this event is quite impressive and there will be a TON of things to learn.  Come, join us and learn something.

Oh yeah, I’m speaking!

Social Networking Is Alive!

SocialMediaIsAlive!Social media is every where these days and if you’re not on the train, you could be missing out.  This one is about how you can use social media to improve not only your professional life, but your personal one as well.  It isn’t geared to specifically geeks (totally non-technical), but to anybody who wishes to improve their online presence through the use of social networking.  It’s a fun session and I hope that the attendee’s enjoy it!!

HINT: I will show you how to get free SQL Server help from one of the popular social networks.  How would you like to tell your management that you solved a real world enterprise level issue with the help of social media?  For free?

Yes, that has actually happened.

You might even see reference to a popular YouTube video.

Free Training

Can you really pass up on free training?  Ok, even with lunch it’s only $10!  So if you’re still on the fence on attending, get off the fence, register, come on out and learn something.  Meet some new people.  Say hello to the speakers (we don’t bite).  Grab some swag and a fantastic lunch.   Register now!!

Get your learn on!  Expand your horizons and break out of the ordinary.  You know you want to.

Why are you still here?  Go.  Register.  Do it now! See you in Richmond!!

Seriously.  GO!

Posted in Community, Presenting, SQL Saturday | Tagged , , | Leave a comment

Quick Script: Dropping Indexes


If Only My Doctor Ordered This..

Sometimes a quick script is just what the doctor ordered.

I recently had the task of packaging up a database to ship it offsite.  This database had some size to it, approximately 200GB with 6 tables.  While looking at the tables, I noticed that though they were relatively narrow, each had ~22 million rows in them. In my environment this wasn’t wild or out of the ordinary, however, I did think that the disk space usage was a little out of whack given what I was seeing.  So, I set out to see if I could make the database smaller so that it was easier to transport.

I discovered that each table had anywhere from 7-15 non-clustered indexes on them.  As we know, a non-clustered index is a partial copy of the data, so it was extra bloat that we didn’t need.  I knew those that would be consuming the data would pull in every table in its entirety, so I wasn’t overly concerned about them having index scans versus index seeks on the data.

Each table, however, did have a clustered index on it so I knew that I wanted to leave it in place.  It was only the non-clustered indexes causing extra weight that I didn’t need.  So I wrote a quick T-SQL script to help drop that weight.  While this script isn’t overly fancy or complex, it turned out to be handy and gave me the ability to manually control dropping all of the non-clustered indexes in the database.

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it very thoroughly.  Period.  

-- Author: John Morehouse
-- Date: February 2015
-- E:
-- B:




--You may alter this code for your own purposes. 
--You may republish altered code as long as you give due credit.

--20150316: MorehouseJ: Added in join to sys.tables based off feedback from JohnW to account for FILESTREAM tables.  Also removed ORDER BY for the KeyConstraintID column.  
USE [AdventureWorks];
    ELSE 'DROP INDEX ' + + ' ON ' + QUOTENAME( + '.' + QUOTENAME( + ';'
    END as 'Drop Command'
    , si.object_id as 'Object ID'
    , as 'Index Name'
    , si.index_id as 'Index ID'
    , si.type_desc as 'Index Type'
    , object_name(so.object_id) as 'Table Name'
    , si.is_unique
    , si.is_primary_key
 FROM sys.indexes si
    INNER JOIN sys.objects so ON si.object_id = so.object_ID
    INNER JOIN sys.schemas ss ON so.schema_ID = ss.schema_id
    LEFT JOIN sys.key_constraints kc on =
    INNER JOIN sys.tables st ON si.object_id = st.object_ID
 WHERE si.object_id > 100             -- system objects
    AND si.type >= 2                  -- anything not a clustered index
    AND so.is_ms_shipped = 0         -- ignore MS specific objects
    AND NOT EXISTS (SELECT name FROM sys.xml_indexes WHERE xml_index_type > 0 AND name = 
    AND st.filestream_data_space_id IS NULL
 ORDER BY [Index Type]

It is worth noting that if the index was created from a PRIMARY KEY or UNIQUE constraint, it cannot be dropped using a simple DROP statement.  You have to use the ‘ALTER TABLE … DROP CONSTRAINT’ method to drop the index.  The script I have provided will handle that for you as well as handle XML indexes and spatial indexes.

For a given database, it generates the ‘DROP’ statements for all of the non-clustered indexes.   It’s that simple.  It also includes some additional meta-data, like the schema, about the index.  Once the query is complete, all you have to do is just copy out the command in the first column, labeled ‘Drop Command’. Take that statement and paste it into another SSMS window and execute it as you wish.  You can execute the entire thing or perform it line by line.

Of course, I’d be remiss if I didn’t say it again, please DO NOT execute that resulting command on a production database.  That command could have severe impact to your production systems.

Yes, I could have probably written a PoSH script to do this work.  Well, Ok.  I could have asked my good friend Mike Fal (B|T) to write a PoSH script for me.  He’s a wizard at it.   Yes, I too could have written a cursor/loop to go do the work for me, but I didn’t want to.  I like quick and dirty scripts such as this one.  It allows me to do what I need to do with a relatively short amount of work. This script helped to get the database size down significantly (of course I had to shrink afterward *GASP*) and then it was much easier to transport once that weight was dropped.  The consumers of the data were also very happy as this also helped them to save space on their servers




Posted in Indexes | Tagged | 4 Comments

Public Speaking – The Small Things


Rough Road Ahead!

Public Speaking is hard and is sometimes a rough road to go down.

There isn’t any other way to put it.  It takes work and won’t always go smoothly.  Not only do you have to generate a topic that you think will be interesting to attendees, but you also have to put a presentation together, practice it, fine tune it and then finally deliver it.

Keeping in mind that what can go wrong will go wrong, helps. As you stand to deliver your presentation, you realize that you’ve forgotten something. Perhaps you suddenly have the worst case of cotton mouth possible.  Or quite possibly, you will hear your stomach rumbling and maybe, just maybe, that burrito you ate for lunch will remind you that it wasn’t that good of an idea. Things like these are bound to happen.

Take it from someone who speaks, I can say that we’ve all been there.  From my experience over the past couple of years, I’ve put together a list of things that I carry with me to ensure that I avoid those rough roads.  Yes, things out of your control will happen.  Demo gods will raise their fists and smash your demo’s to pieces.  The bulb in the projector will suddenly reach the end of its life just as you start your presentation.  Sometimes you just can’t help those situations, even with the best of preparations.  So know this, especially if you are just starting out as a speaker,

1.  Things will happen that you cannot control.  

2.  You cannot fix rule #1.

However, it isn’t the end of the world.  Don’t fret over the bumps during your presentation. Learn from the experience, adjust accordingly and keep on speaking.  

Good?  Good!!

Now let’s have the list!


  1. theKeningtonClicker

    The Kensington. Clicker Among Clickers

    Presentation Clicker.  Get a good presentation clicker. You can spend a lot of money on one, but I recommend a mid-tier model since it will most likely do what you’ll need and not cost a bundle.  Some clickers are more fancy and have built in ways to keep your presentations on time but those options a matter of preference which I do not see a need for. Having a clicker allows you to have freedom to walk around during your presentation and not be tied to your laptop

  2. Spare batteries.  I always have a set of spare batteries for my gadgets,  Usually a 4-pack of AA and a 4-pack of AAA’s.  I have gadgets that use both so keeping both on hand is important to me.  Take a look at your arsenal of tech and see what you may or may not need batteries for.
  3. Backups.  Get a USB thumb drive and backup your slide deck, demo’s and any virtual machines that you might need.  I also back up my slides & demos to Dropbox as a secondary back up. Any type of cloud provider would also work for this.  In the event your laptop goes south for the winter (it will happen at some point), with a good backup in place you can work around it.
  4. Secondary Presentation Device.  If you have an Apple iPad or Microsoft Surface or a secondary laptop, configure it at the very least be a presentation device.  Personally, I have an iPad and can use it to present the slide deck if need be. I am told on a Surface, SQL server can be installed for demos but I’ve never done it. When you have laptop issues you can save the day by presenting from the secondary device, so make sure you have one available.
  5. VGA/Display adapters.  Carry your own.  Plain and simple.  Don’t assume that every place will have the proper adapters for your model laptop/device to connect to the projector.  This also goes for your backup device.  I’ve found that having just a VGA adapter is a safe bet, as that’s still fairly standard.  You can also get HDMI adapters, however I’ve yet to run into a projector that won’t take a VGA connection. I carry a VGA adapter for both my laptop and my iPad so that both of my devices will be usable.
  6. Travel mouse.  I have a smaller travel mouse that I use for presentations Having a mouse with me ensures I don’t have use the track pad if I don’t want to.  I find the mouse allows me to have a smoother experience while doing demos so I prefer it over the track pad.  This is my personal preference so you can adjust as needed.
  7. Watch.  If you are someone who doesn’t regularly wear a watch, I suggest you get some type of time keeping device that you can place on the desk and can see from a distance. Usually, I try to visit the room where I’ll be speaking prior to my session so that I can get a feel for the lay of the land.  This is also a good time to check to see if there is a clock within the room and make note of its location. Remember in your preparation make sure that you practice using the watch so that you know the timings of your presentation.
  8. Notes/Feedback.   Determine a good way (for you) to take notes during your presentation when needed.  As people ask you questions you’ll find things that you’ll potentially need to research and want to jot down to get back to them on.  Having a quick way to write down a note or question to remind yourself is handy.  Some presenters will use the note section of the slide deck when in Presenter mode while others will use a simple pad of paper & pen.

Personal Items

  1. Water.   Make sure you have bottled water with you.  Large amounts of talking dries my mouth out and having cotton mouth while presenting is not fun.  I usually carry at least 1 if not 2 bottles of water with me when I present.  Keep in mind, however, I do monitor my liquid consumption leading up to the my session so that I don’t have a sudden need to use the facilities in the middle of my session
  2. Hard candy/cough drops.  Some speakers would frown on this, but if your throat starts to dry out, you can use a hard candy or cough drops to help with that.  Sometimes water just isn’t enough. I have found that when traveling to locations where the elevation differs greatly from my normal surroundings, my throat tends to be more sensitive to those conditions.
  3. Hand sanitizer.  Pretty simple, keep the germs away.  Most attendees are polite and some will want to shake your hand and tell you “Thank You”.  This just helps to keep colds at bay.
  4. Pain Reliever.  Aspirin/Motrin/Advil/Ibproferin/etc, whatever your choice is.  Keep a small bottle with you so that you can take some if you feel a headache or something else coming on.
  5. Pepto Bismol/Tums.
    1. When I travel to another location, I love to try the local cuisine and sometimes that’s a bad decision for me.  Food can sometimes sneak up on you and if you don’t have anything to help counteract that, you could be in trouble.   Personally, I recommend the chewable versions of Pepto Bismol as it’s easy to carry through the airports.


Now that you have the list, how do you keep it organized? I’ve tried a couple of different ways to organize things within my back pack, however I’ve found using two separate cases works best for me.  Of course, your mileage may vary and you might have to try a couple of variations until you find one that works for you.

File Feb 18, 18 07 37

The one case on the left is used for most of my tech.  It holds my VGA cables, charging cables, batteries of various sorts, power adapters, my MiFi along with various odds & ends.

I bought the case from Amazon for less than $20.  So far it has served me well and fits nicely in my back pack.

personalStorageThe case on the right is what I use to keep those personal items organized.  Before traveling, I make a stop at the local Target and visit their travel section.  You can get all of the personal items listed above (except the water) in travel sizes This allows them to fit nicely within the case.

Once I have my supplies, I will usually place the case somewhere in my back pack that is easily accessible.  There are times when traveling that some of these items can come in useful and I want to be able to get them easily.

Will this solve everything?

Nope. It might not even come close.  Then again, something on this list just might help to give you a detour around that rough road and you’ll give the best presentation ever because you planned ahead.  When you set forth to put do a public presentation, don’t forget to think about the small things.  Hopefully my list will help you get started!

Do you already have a list and have something that I don’t have on mine?  I’d like to know!


Posted in General Conversations, Presenting | Tagged , | 3 Comments

SQL Saturday 358 – Albuquerque


I’m once again honored to be selected to speak at SQL Saturday 358 – Albuquerque, New Mexico.  This will be my second time actually speaking there and last year was simply epic for me.  The event was simply fantastic and I’m really looking forward to returning to the beautiful New Mexico landscape.

If you have nothing better to do on Saturday, February 7th 2015, come on out and get your learn on.  The schedule for this event is quite impressive and there will be a TON of things to learn.  Come, join us and learn something.

Oh yeah, I’m speaking!

Social Networking Is Alive!

SocialMediaIsAlive!Social media is every where these days and if you’re not on the train, you could be missing out.  This session is about how you can use social media to improve not only your professional life, but your personal one as well.  This session isn’t geared to specifically geeks (totally non-technical), but to anybody who wishes to improve their online presence through the use of social networking.  It’s a fun session and I hope that the attendee’s enjoy it!!

HINT: I will show you how to get free SQL Server help from one of the popular social networks.  How would you like to tell your management that you solved a real world enterprise level issue with the help of social media?  For free?

Yes, that has actually happened.

You might even see reference to a popular YouTube video.

Free Training

Can you really pass up on free training?  Ok, even with lunch it’s only $10!  So if you’re still on the fence on attending, get off the fence, register, come on out and learn something.  Meet some new people.  Grab some swag and a fantastic lunch.  I promise you that the speakers won’t bite.  Register now!!

Get your learn on!  Expand your horizons and break out of the ordinary.  You know you want to.

Why are you still here?  Go.  Register.  Do it now! See you in Albuquerque!!


Posted in General Conversations, Presenting, SQL Saturday | Tagged , , | Leave a comment

Friend of Red-Gate 2015

forg-logoIt seems that 2015 is going to be the year of new adventures for me.  Not only have I moved myself and my family ~700 miles from Nebraska to Kentucky for a new career opportunity, I also just recently joined the Friend of Red-Gate 2015 program.  I’m really honored to join the program and look forward to see what it offers!

For those that might not know, Red-Gate makes some super slick SQL Server (along with other platform specific) tools that I’ve used throughout my career.  They have a program, Friend of Red-Gate, that is aimed at active community members (in various realms) who would like to be on the inside track as they work on new products.  I know several people at the company and they are all truly top notch.  I can’t recommend them enough.

If you want to know more about the program, you can go here to find out.  If you haven’t tried any of their tools yet, I highly recommend that you do!!


Posted in Community | Tagged , | Leave a comment

Bucket List – Achievement Unlocked!

First, a little back story.

The annual PASS Summit is truly a magical place for me and I look forward to going every year. It’s the one conference that I’ve told management that I would want to go to annually simply because of the networking & training that happens during that week. bucketListAt this year’s Summit, I got into a conversation around bucket lists, both personal and professional. Over the past couple of months (even prior to the Summit) I had been considering such a list but I didn’t really have any meat to it.  I don’t have an actual “on paper” bucket list but I’ve started to mentally keep track of things.  I even found a free website that will help you keep track of your bucket list,

One thing on my list is to “live outside my comfort zone” more.  Now, I realize that is a really broad item to have but hey, it’s my list.  Basically, I want to experience life more and if that means putting myself out there more to do so, then so be it.


Over the years, I’ve determine that I like to be rooted in my community.  Growing up as a military brat (a title I wear with honor & pride), we moved around a lot so I never felt “rooted” so to speak.  When my Dad retired from the Air Force, I told myself that I’d like to find a spot to call home, grow some roots and not really move.  That was in 1990 and I’m still in the same town we moved to from Texas.  I moved around for college and various other life events, but for the most part I’ve stayed in the same general area.  After 24 years living in Nebraska, one can grow quite a set of roots.  These roots then morph into a very large comfort zone.

Bucket List Achievement – Picking Up Roots

FCMAlogoWith that in mind, I’m very happy to announce that I’ve accepted a Database Architect position with Farm Credit Mid-America in Louisville, KY.

That’s right. You read that correctly. My family and I are leaving the good life of Nebraska for beautiful Kentucky.

I’ll wait while you pick your jaw up off of the floor.

Got it picked up?  Great!

Yes, you read that correctly.  My official start date is January 5th, 2015.  Nothing like starting the new year with a new adventure!

Needless to say, this puts me WAY outside my comfort zone.  My family and I will be uprooting and moving several states away from a very large circle of family and close friends.  Some family will be left behind (aunts & uncles, cousins & numerous close friends that are family) but I’ll be closer to other family (my dad & in-laws) and I hope to make new friends!

My wife and I have already found a townhouse to live in while we attempt to sell our house in Nebraska. The real beauty for me? It’s about 2 miles from the new office. My daily commute will go from 60 miles round trip to about 4 (give or take).   I estimate that I’ll recover about 45 minutes a day just in drive time alone!

Another piece of good news is that I’m moving to a company where I know my new manager, Mike and the CIO, Dan.  I’ve worked with them both at Farm Credit Services of America in Omaha, Nebraska and I am looking forward to the challenges that they already have lined up for me.

2014-11-23 08.51.11

This is me accepting the new position..

This new opportunity is an exciting one and one that I’m looking forward to. I think that this will be great opportunity for my family as well as myself to live outside our comfort zone and not be afraid to try new adventures! Make no mistake, leaving will be very bittersweet and it’ll be tough to say good-bye to 24+ years in Nebraska, but it’s time.

I’m very thankful for the opportunity that Farm Credit Mid-America has offered me.  I’m also very thankful for the culture and experience that Farm Credit Services of America has given to me over the past years.  They have been an excellent company to work for.  If you happen to be looking for a position in the Omaha, Nebraska area, I would definitely recommend them.


If you don’t have a bucket list of your own, I challenge you to put a list together and work toward crossing things off.  Keep in mind, your list is just that, yours. You can put anything you want on the list. Start it. Put things on it. Own it. Work towards crossing things off. Who knows, maybe our paths will cross one day while crossing things off.

We only have a short time on this world and everybody should live it to the fullest. I know I’m going to try to moving forward. I hope that you do to.

See you in Louisville!

Posted in Personal, Philosophical | Tagged , | 10 Comments

Achievement Unlocked!

3 years ago in October 2011, I was attending my first PASS Summit in Seattle, Washington. It was a fantastic experience and I knew that I was hooked on the #sqlfamily.


One just wait and see.

Shortly after the Summit, I put out a blog post about a goal that I  wanted to shoot for.  Call it an early New Years Resolution if you will. The goal was simple.  When checking in at the event, I would check in either as a Speaker, Microsoft Certified Master (MCM), or a Microsoft Most Valuable Professional (MVP).  I was fairly certain that I wouldn’t be in the MCM/MVP camp, but being a speaker was definitely something that I could control.  I wasn’t sure when that would happen, but I  knew that one day, I’d be in that line.

PASS Summit 2014


Achievement Unlocked!!

Fast forward 3 years to November 2014.  I’m very happy to say that I’ve achieved that goal.  Yesterday morning, I was able to check in for my 4th SQL PASS Summit as a Speaker.

My great friend Chris Shaw (b|t) and I were able to deliver a full day pre-con session, Real World End to End Performance Solutions to about 43 attendees.  We had great interaction from the audience and I think that the session went really well.

It has be an absolute epic journey getting here and I wouldn’t change a thing. I’ll be setting some other goals and will be blogging about it in the near future.

If you are attending the PASS Summit and get hooked like I did, do not be afraid to set  goals and strive towards them.  You never know, maybe we’ll see you in the Speaker line next year!

Enjoy the Summit!

Posted in PASS, Personal, Summit | Tagged , , , | Leave a comment

Compatibility Level vs Database Version

I recently had a colleague, Billy Bob,  stop by my desk and with a question about the compatibility level of a database.  Essentially, he was interested in upgrading a database’s compatibility level but at the same time have the ability to down grade it if issues were found.  Billy Bob had a concern that if we had to downgrade, this action would impact the database internal structure or version.

I know that this is an old topic since I found a post from 5 years ago by Jonathan Kehayias (B|T) that talks about the exact same thing, but I think that it’s worth repeating. Paul Randal (B|T) also has one here.

Compatibility Level

The compatibility level of a database dictates how certain language elements of the database function as it relates to an earlier version of SQL Server.  In a nutshell, this offers up partial “backward compatibility” to an earlier version.  This functionality isn’t all encompassing as only certain aspects (i.e. certain syntax) of the database would pertain to this setting.  For more details as to what is or isn’t affected, check out Books Online.

We know from various online sources that these are the following compatibility levels:

SQL Server 6.0 – 60
SQL Server 6.5 – 65
SQL Server 7.0 – 70
SQL Server 2000 – 80
SQL Server 2005 – 90
SQL Server 2008 – 100
SQL Server 2012 – 110
SQL Server 2014 – 120

It’s also worth noting that the compatibility level is only available for 3 previous (including the one that you are on) versions from the current version of SQL Server that you are working with.  Here is a listing of the available compatibility levels:

Compatibility Levels
Version Current Compatibility Level Oldest Available Level
SQL 2014 120 100
SQL 2012 110 90
SQL 2008 100 80
SQL 2005 90 70
SQL 2000 80 70
*NOTE: I do not list anything prior to SQL Server 2000 as I don’t have a way to validate what (if any) compatibility levels are available.  Also note that SQL Server 2008 R2 while a newer version still maintains the same compatibility levels as SQL Server 2008.

You can see what compatibility level a database is at by using the UI or via code.

Via UI:

  1. Right click the database
  2. Select Properties.
  3. Go to the Options tab

The following dialog window will show the compatibility for that particular database.  In the example below, you can see that the compatibility mode for the database DBVault is currently 2012 or 110.






Via Code:

For SQL 2005 & newer:

SELECT name, compatibility_level from sys.databases WHERE name = 'DatabaseNameHere'

For SQL 2000:

SELECT name, cmptlevel from sysdatabases WHERE name = 'DatabaseNameHere'

If you want to see the compatibility for all databases of the instance, just leave off the WHERE clause.

Database Version

The database version is an internal versioning system that defines what version of SQL Server the database was a recent resident of.  If you migrate a database from an older version to a newer version, the database version value will be increased to reflect the version number of the new server’s model database.  The database version does not equal the compatibility level and should be considered as a completely different attribute of the database.

When you create a database, the database version is “stamped” with the same version as the Model database.  It’s worth noting that if the Model database was originally created on a different server edition and then subsequently upgraded, you potentially could end up with slightly different numbers than what you might expect.  As you upgrade the database to new SQL Server edition (you can’t go backward) the version of the database increases. This is done automatically regardless of what method you use to upgrade the database to the new version of SQL Server.

Here are some database versions numbers for each version of SQL Server:

Database Version Levels
SQL Server Version Database Version
SQL 2014 782
SQL 2012 706
SQL 2008 R2 661
SQL 2008 655
SQL 2005 611
SQL 2000 539

I did find this article that has a more complete listing of database version values.

It is worth noting that service pack releases will most likely increase the database version of all the attached databases.  There are a couple of ways to determine what database version a database is currently at. There is not, however, any way to do this through a UI that I’m aware of.  Here are 3 ways in code that I know how to get this information.

-- using DBCC PAGE to look at the boot page (9) of the database
DBCC PAGE('DatabaseName',1,9,3)

-- useing database property
SELECT DatabaseProperty('DatabaseNameHere','version')

You’ll note that for each DBCC command we have to turn on trace flag 3604 so that the output of the DBCC command is sent to the SSMS window rather than the default location, the SQL Server log.

Ironically, if you are still on SQL 2000, you can see this information with a simple query:

-- useing database property
SELECT name, version from master.dbo.sysdatabases

If you use the DBINFO/PAGE option, you will need to look for the hi-lighted values in the output:



In the above example, you can see that I’m looking at the AdventureWorks database, the database version is currently set at ‘706’ and it was created with version ‘705’.  This tells me that the database was initially created on a SQL Server 2012 instance (possibly a CTP version) and then subsequently upgraded when restored to my test SQL Server 2012 SP2 instance.

Bringing It All Together

Now that we understand the difference between the compatibility level and the database version, let’s bring it all back to our initial story.   If I recall correctly, the database in question started off as a SQL Server 2000 database which is a compatibility of ’80’ and an internal version of ‘539’.   As mentioned, through the course of the years we have been able to upgrade it to SQL Server 2008 and currently in the process of getting it moved to SQL Server 2012.

Since we know that the database was migrated to SQL Server 2008, the database version was increased to match the model database of that instance, so it was “stamped” with ‘655’.  The problem is that while the database version was increased, the compatibility mode was never upgraded (it slipped through the cracks as a migration step) to reflect SQL Server 2008 and remained at the SQL 2000 level of ’80’.

Since we also know that the database version is related to the version of SQL Server the database is sitting on, we know that we can adjust the compatibility level accordingly without affecting the database version.   Thankfully we have a good testing environment and were able to easily adjust the compatibility level to reflect SQL 2008 level of 100 and verify that nothing was impacted.  Once testing was complete, we made the change in production knowing that we were not going to affect any subsequent applications.

Did I mention that we tested?  Even with this change, you will want to test your applications to ensure that all of your code continues to work as you expected.

As a side note, we now have steps and/or scripts in place, so that when a database migration occurs upgrading the compatibility level to a higher level is accomplished.


Posted in Internals | Tagged , | 1 Comment