Have you ever tried to restore a backup over an existing database and receive an error stating the tail of the log for the database has not been backed up? Many times if you are just restoring a database into a development environment you don’t care so you just restore WITH REPLACE and move on.  However if you want to ensure that your restore contains the latest transactions from the production database, simply make a transaction log backup.  Example

(BACKUP LOG [DB_NAME] TO DISK = ‘DRIVE:\PATH\DB_NAME_LOG.BAK’)

Now lets suppose you are working with a production system and something dreadful happens to the database.   Lets assume that you have a transactional database and take TLOG backups every 10 minutes. Now lets suppose at 2:00 in the afternoon a regular TLOG backup is taken and at 2:07 there is a hardware problem where your MDF file becomes corrupt. You now have to restore from backup. What happens to those 7 minutes of transactions?

Without recovering the tail end of the log, this data is gone. Since it was only the MDF file that was corrupt or lost, we can still take a log backup even without the MDF. To do so use

(BACKUP LOG [DB_NAME] TO DISK = ‘DRIVE:\PATH\DB_NAME_LOG.BAK’ WITH NO_TRUNCATE)

The key here is the NO_TRUNCATE statement.  Using NO_TRUNCATE allows you to be able to backup the transaction log without the database being online.  If your database is online you can just take a regular transaction log backup.

If you want to see this in action, check out my video.  In this video I go rouge and delete my MDF file and demonstrate how to backup and restore the tail end of the log.

How to backup and restore the tail end of a transaction log

I had the pleasure of attending SQL Saturday 112 this past weekend.  This was my second time speaking at a SQL Saturday in Birmingham.  I was selected to present two sessions again this year in the first and second time slot.  I started off with “Getting Involved and Getting Ahead”.  This session is all about the SQL Community and how volunteering and giving of your time can pay huge dividends in your career.  You get to learn so much more and at such an advanced rate that the sky is the limit.  While helping others you gain as much or more.  The contacts you make alone are work the investment of your time.

The second session I gave was a brand new session called “Know Backups and Know Recovery”.  I presented this session in March to the Columbus GA SQL Users Group.  I really enjoy this session and hope to make it my primary session I give.  In this session I cover best practices with make FULL, DIFFERENTIAL, and TLOG backups of your databases.  I find that most people are at least doing FULL and Transaction logs.  About 25% do weekly FULL and Daily DIFFERENTIAL backups.  I also have found that most attendees have not practiced restores to include their DIFF and Transaction log backups.  In this session I demo using a script to generate the restore scripts of the last FULL, DIFF and all transaction log backups since the last respective FULL or DIFFERENTIAL backup.  I also demo FILEGROUP backups, peace meal restores and even a TAIL LOG backup and restore.

In between giving these two sessions and the remainder of the day I helped work the RedGate booth and PASS Booth.  I had a great deal of fun helping to spread the word about all things PASS and helping to field questions about many of the RedGate products.  The organization team for SQL Saturday 112 did an excellent job putting on this event.  I really like the speaker shirt and the bags the team got.

The event location at Jefferson State Community College was great.  Large classrooms and a centrally located vendor area.  There was excellent signage leading up the event, schedules posted on the doors to the rooms, a very well put together event guide and tons of drinks, donuts and coffee.

I will definitely be back to this event as long as they are willing to have me.

My slides can be found on the SQL Saturday website here.

“Why I joined Linchpin People as a Teammate”

It was announced publicly this past weekend at SQL Saturday #111 that I have joined Linchpin People as their first teammate.  My friend Andy who is also a Chief Servant Officer of Linchpin made the announcement during the speaker dinner and gave me an official Linchpin dress shirt.

During the remainder of the speaker dinner and throughout the SQL Saturday event the next day people kept asking me about Linchpin.  I would explain to them the culture of Linchpin and tell them the short story of how it came to be that I was joining such a great outfit.

The primary question I kept getting was ” Are you joining Linchpin full time as a consultant?”.  I had to explain that I was only doing so part time at this point in my career.  (Night and weekend type work)  I would then explain my current job and what all has happened with me in the DBA role over the past 4 years.  I was recently promoted to a lead role and have 4 DBA’s that I am responsible for including mentoring and helping them grow in their career.

My current job is wonderful, I really enjoy the folks I work with and have numerous really cool projects going on.  I started the side business primarily to make a few extra bucks to help fund trips to SQL Saturday’s.  I really enjoy getting to travel around the country speaking and teaching others what I know.  I also get to learn a great deal from attending other sessions and networking with the SQL Community.  In forming my LLC, this awesome opportunity arose with Linchpin.  How could I possibly say no to working with Andy Leonard, Brian Moran, Mike Walsh and Robert Pearl.

It is really nice being the team lead at work, but it is even better being the dumbest guy in the group with Linchpin.  There is so much for me to learn from these guys.  I am really looking forward to absorbing as much as I can from each of them.

I was chosen to speak at SQL Saturday 111 in Atlanta GA this weekend.  I will be doing my session called “It’s TEMPDB, Why Should You Care”.  In this session I cover the importance of TEMPDB and how best to configure TEMPDB in your environment.  Not only do I cover a lot of material on slides which are available to download on the event website, I also break out into a live demo demonstrating contention on TEMPDB and discuss how best to eliminate or reduce the contention.

I have presented this session at a number of other events and always get positive feedback on the technical material in the presentation.  If you don’t know why TEMPDB is important or have not configured TEMPDB after a base install, come hear me speak or just shoot me an email.

In recent months I have been asked to ensure we are auditing both successful and failed logins, not just failed logins.  It is simple enough to open SSMS, connect to the instance, right click on the server, chose properties, click on security and then check the proper radio button.  When putting together or most recent updates to our server installation guide I decided to spend the few minutes to research how to make this change with few steps.

In my case I need this to be both failed and successful logins.  I will execute the following code within SSMS.

EXEC xp_instance_regwriteN'HKEY_LOCAL_MACHINE',
 N'Software\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', REG_DWORD, 3

Notice I am updating the registry and setting the AuditLevel to a 3.  What are the other options?

None = 0
Successful Logins Only = 1
Failed Logins Only = 2
Both Failed and Successful Logins = 3

Here is a visual as well.

Recently I learned that you can and probably should increase the number of SQL Server Error logs that your instance of SQL Server maintains.  This is real simple to change.  You can make the change via the GUI with a few simple steps or accomplish it with TSQL.

To make this change with SSMS, open SSMS, connect to your instance, expand Management, right click on SQL Server Logs and chose Configure.  You can then check the box “Limit the number of error log files before they are recycled” and increase the number from 6 to any number up to 99. 

Using TSQL you can execute the following statement to increase to 99 files, simply change 99 to how ever many files you would like to retain.

USE [master];
GO

EXEC xp_instance_regwriteN’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’NumErrorLogs’, REG_DWORD, 99;

GO

I started out day three after the keynote by attending Adam Machanic session on Query Tuning.  I must say that I will need to watch this session again once I can stream it.  There was way to much good information being given for me to remember it all and couldn’t take notes for fear of missing something.  Double edge sword I suppose.  Didn’t take notes but couldn’t remember everything I heard either.

Lunch was set up with birds of a feather style where you could sit at a table with industry experts.  Since I am in need of setting up PBM and CMS when I get back to the office I sat with Jorge and Colin.  Learned a few things as well as getting a full belly.

After lunch I dropped into Glenn Berry’s session on Scaling SQL Server.  Glenn presented some great information that all system admins and architects should also hear.

Next I went to John Sterrett’s session on CMS and PBM.  Combine that with attending Jorge’s session on PBM in a nutshell and I truly feel like I can get this PBM project underway when I get back to work on Monday.

All in all, Friday was a successful day for me to walk away with much more knowledge than I started with.

Day two of sessions only had me attending three sessions.  One of them was 3 hours.  I started out after the keynote attending Klaus’s session on Advanced SQL 2008 troubleshooting.  Klaus had a huge crowd and covered some really good information.  I picked up a couple of new tips to apply when I get back to work next week.

My second session was probably one of the best I attended.  Thomas LaRock put on a sessions titled “Performance Tuning Made Easy”.  LaRock covered the method and framework for troubleshooting.  This really hit home for me.  I have attended numerous sessions on indexing, performance tuning with DMV’s, etc.  LaRock covered a step by step approach to troubleshooting that I will be implementing when I get back.

I finished out the day with Paul Randal in his Myth Busters session.  Paul always draws a crowd and puts on a great show.  Paul helps debunk common myths that are floating around.

There were several social events after day two of the sessions.  Idera had a happy hour at the Tap House, SQL Sentry put on an event at Pike Brewing Co, and Red-Gate had a dinner for the Friends of Red-Gate.  The Friend of Red-Gate dinner was at FareStart.  There is a wonderful story around FareStart where they take in homeless and nearly homeless folks and put them through culinary school.  The restaurant prepares 2500 lunches 7 days a week to be distributed around Seattle.  It is an amazing foundation.  This has inspired many of us to do something different within the SQL Community.  I spent a few hours with a couple of key people that have the resources to put together an amazing program.  I am looking forward to seeing something develop with them over the next several months.

Day two was a success for me.

The keynote started out with Rob Farley and Buck Woody singing an awesome song about a slow running query.  What a great way to start out the day.  Next Wayne Snider was recognized.  He spoke at his first summit in 1999.  Both Wayne and Rick Heiges are rolling off the PASS board this year.  Wayne gave a good roast of Rick and congratulated him on helping create the 24 hours of PASS.  Well done Rick.

Wayne got a bit emotional up on the stage which he said was either gratitude or intergesten .  Wayne gave a great quote “As you slide down the banister of life, may the splinters of success stick in your career”.  Here’s to Wayne for making such an impact on the SQL Community.  Thanks for all you have done.

May 10th – May 11th – SQL Rally.  SQL Saturdays are everywhere.  PASS Summit 2012 will be November 6 -9th in Seattle WA.  Two days of pre-cons starting on Nov 5th.  $995 for Summit $1395 includes both days of pre-con’s.  Free ebook by Manning on MVP Deep Dives collection.  This is a combination of both MVP Deep Dives books.  It is 96 pages and FREE.

Birds of a Feather lunch is today.  This is always a huge hit.

Last day to get the DVD set for $125 plus S/H.  That is only $.73 per session.

David Dewitt came on stage to the largest applause yet.  He is clearly a fan favorite.  David created two new hash tags so we can tweet which specs we want him to wear.  This data will be analyzed as big data.

What is big data?  Think pedabytes.  2700 nodes and 60 PD’s is what Facebook deals with.  Now that is BIG DATA.  Estimates for the worlds data is 35 ZB by 2020.  That is enough dvd’s to be stacked almost to Mars.  What is generating the increase?  More data, web searches, tweets, people realizing data is to valuable to delete, and the cost of storage is decreasing.  The old guard (ebay) uses 10 PB on 256 nodes, young turks (facebook) use 20 PB on 2700 nodes, Bing 150 PB on 40k nodes.

NoSQL does not mean NO to SQL.  It’s really to say Not Only SQL.  So why NoSQL?  More data model flexibility.  JSON as a data model, No “schema first”, relaxed consistence models.  They are willing to trade consistency for availability.  Low upfront software costs. The folks just don’t understand SQL.  (applause from the audience)

We now have two universes.  Structured and Unstructured.  Relational DB and NoSQL Systems.  ACID and NoACID.  Relation DB’s provide maturity, stability, efficiency.  NoSQL provides a large amount of flexibility.

This is not a shift to a new DB platform.  SQL is not going away.  RDBMS will dominate transaction processing and ALL small to medium sized data warehouses. Many business will end up with data in both universes.

Dr Dewitt started explaining how Hadoop came to be and how it stores data by splitting large files into smaller chunks and storing them across the cluster nodes.  They are stored in a file system.  Based on its method of storing the data on clusters in different racks with different switches fault tolerance and speed are great.  One of the nodes could actually be in another datacenter.  Sounds like some complicated algorithms making this happen.

When a datanode fails, the data that was stored on that node are then stored on other available nodes in the cluster.  When a new node is brought back online the file system will start spreading the data around to this new node.  These events are all down under the covers.

Pro’s – Highly fault tolerant, relatively easy to write, MR framework removes burde of dealing with failures from programmers.

Con’s – Schema embedded in application code, a lack of shared schema.

Dr Dewitt went on to say that Facebook created HIVE and Yahoo created PIG in order to query Hadoop data.  MapReduce jobs are difficult to write when you have to join data.  Tables in HIVE or more relation DBMS like with data stored in tables.

Connecting the universes – Sqoop.  Reasons were stated on why we would want to connect the universes such as being able to use procedural language to query.  You may also need to access data that is in both relational and NoSQL environments for the business need.  Makes sense to me.

Ok, so I got caught up in Dewitt’s speech and didn’t type as much.  He basically laid it all down on how Hadoop stores all the data.  He covered the pro’s and con’s of it all.  Very well done.  I suggest everyone who is interested to stream the keynote from the SQLPASS.ORG website.  It is recorded and available.  Really good stuff.

Basically to sum it up – NoSQL tools: Hive, Pig, and Sqoop. We learned their history and some of the things they are useful for. NoSQL = Not Only SQL.  Relational Databases are not going anywhere and there is a market place for both.  There are now TWO universes.  Structured and “Not So” Structured.

Watch the keynote.  One of the final slides is worth it alone.  Great visual aide.

The keynote started out with Bill Graziano taking to the stage in a kilt and declaring that the second day of the PASS Summit is now officially SQL Kilt day.  Bill asked everyone in a kilt to stand up.  It had to be a record number of men in skirts today.

Lori Edwards was recognized for her volunteer work and received the PASSion award and myself and Jack Corbett were highlighted as Outstanding PASS volunteers.  It was awesome being asked to stand and getting applause from 1000′s of data professionals.  It was very humbling.

Bill also went over some financials.  Looks like the community involvement spending has increased ALOT.  It is obvious to me that more funding into SQL Saturday’s, 24 HOP, and other events are helping to grow the PASS Community a great bit.

Quentin Clark took to the stage with a lot of iron sitting in racks behind him.  He kindly told all of us to drop the name “Denali” and refer to it as SQL Server 2012.

Bob Erickson – Executive VP of Interlink Transport Technologies – #2 transporter in the world.  Bob discussed how mission critical the data for his company is with having to route containers to port authority and the sheer volume of data.  Zero downtime is a huge requirement.  Downtime means shipments stop.  Microsoft implemented an always on solution for them to assist with there mission critical systems.

During the live demo of Always On which is one of the more favored new features of SQL 2012 the presenter was doing a great job covering the product but no one could see his slides.  Twitter lit up with everyone asking for them to use Zoomit to zoom into the areas of the slides so we can see what he is typing and highlighting.  When the presenter finally caught on and stated he was going to use Zoomit, the audience erupted into a very loud applause.  The SQL Community is awesome isn’t it.

ColumnStore Index was covered next.  Very slick technology that will really help out in the BI stack.
Lara Rubbelke took to the stage and teased us with more Excel demos.  When Lara came out a round of applause went out.  Still some issues with the color scheme of the slide deck for those of us in the audience but she quickly brought out Zoomit so we can all see what she is featuring and typing.  Well done Lara.  During her demo she highlighted data quality issues with Radio Shack being placed in the middle of a lake.  The purpose was to show how the knowledge base works.  Rather cool way to demo this feature.  With the columnstore index she drastically reduced the time the query for the report took to run.  47 seconds down to .3 seconds.  Not bad huh?

SQL Server Appliances are being optimized and pre-tuned.  Organizations being able to go out and purchase a BI appliance to implement an enterprise data warehouse is truly awesome.  Imagine purchasing a SQL 2012 Parallel DW appliance to implement a multi terabyte end to end BI solution.  No more guessing with configuration, hardware, installation.  Over 480 cores for scale out ability.  HP’s enterprise DW appliance was shown and stated you can scale to 700 TB of data.  1-5 TB of DW space can fit in a single 6U unit.  That is six inches of rack space.  This is a great solution for small to mid size companies.  From the box to DW is only 20 minutes for the setup.  This should really help everyone get a BI Solution up and in place.  An HP Business Decision Appliance is as small as a 1U server.

HP Database Consolidation Appliance.  Can order within the next month.  First private cloud appliance available on the market.  Extreme availability, enough spares that you don’t worry when a drive fails.  A full rack contains 2TB of ram, 192 cores, 400 disks with 60k IO’s available.  You can start with a half rack in order to be able to scale out to a full rack.  The max scale out is yet known.  HP has yet to reach the limit.  Want 10 units to build a POD, no problem.  WOW.  The speaker said “We have yet to build the biggest one”  Kinda funny and was pointed out on twitter by Brent Ozar, “The biggest one you have built is the biggest one”.  HA

Michael Rys – Principal Program Manager.
Started out showing his book collection and of course did a shameless plug for his own book.  If you got it, flaunt it I guess. Semantic Search results was highlighted by pulling out keywords and displaying them of different books.  Semantic Search looks at the language models of documents and will determine keywords.  Pretty neat stuff.

SQL Server Data Tools (formerly “Juneau”) will ship with SQL Server 2012.  This was met with lots of fan fair from the audience.  The tools are unified across database and BI.

A sweet new feature demo’d was connecting directly to SQL Azure storage from within SSMS.  SQL Azure Federations also were shown today.  Many folks got really excited as this enables more scalability.  A new Metro UI theme has been very dominant during the demo’s yesterday and today.  It appears this will be a new “feature”.  There is a mixed response to the new GUI.

New CTP’s for SQL Azure Reporting and SQL Azure Data Sync are available today.  Coming end of year SQL Azure Federation, New Management Experience, and data size increase to 150 GB.