Las Vegas February 2013 UG

Categories: Corner, News, Professional, SSC
Comments: No Comments
Published on: February 12, 2013

I know it is right in the middle of TSQL2SDAY when this post is to go live.  If you don’t know what that is, you should go check out this months edition here.  The topic this month is good and should drive up a lot of participation.

It also happens that this week we have a group meeting for the Las Vegas User Group. We normally meet on the second Thursday of each month.  It just so happens that this month that lands on the 14th of February.  If you can’t fathom why we changed our meeting date, maybe you should Google the date or something (just joking).

This month we have a first time presenter.  I have been bugging her for months to try to get her to come out of her shell to present.  And now she is doing it so be gentle and check out the meeting.

Presenting this month is Terrie White.  She will be presenting on Replication Technologies and High Availability solutions.

Here are some of the details for the meeting.

Presenting is Terrie White

Terrie White Will be presenting to us on replication technologies and high availability.

 

 

 

 

LiveMeeting Information:

Attendee URL: https://www.livemeeting.com/cc/UserGroups/join?id=WD4TSW&role=attend
Meeting ID: WD4TSW

 

VERY IMPORTANT

The meeting location has changed.  We will no longer be meeting at The Learning Center.  New meeting location is M Staff Solutions & Training / 2620 Regatta Drive Suite 102 Las Vegas, NV 89128.
We are also moving to meetup for meeting management. Please join us for free at http://www.meetup.com/SQL-Server-Society-of-Las-Vegas/

Is your LOG backed up?

You have been doing a fantastic job of late.  You have all of your databases being backed up on a regular schedule.  Now you get an alert that your transaction log just keeps growing.  Why is that?

You decide to do a little investigation and find that you have your recovery model set to full and you are performing full backups.  That should cover it right?  No, that is not right!

What is this?  Now you have to do something more?  Yes, that is correct.  Performing full backups is not always enough to recover your critical data.  You need to know the recovery requirements for the database / server in question.  But since you have the recovery model set to full, let’s just talk about what else you should be doing.

The first step should be to run a quick script to determine what databases you have in full recovery and which of those databases do not have a LOG backup.  You see, when a database is in full recovery, you should also backup your transaction log on a regular schedule too.  That schedule is to be determined as a part of the second step (and I will only talk about the first two and only briefly about the second step).

In that first step, you can query your msdb database to help generate a report of which databases have had a transaction log backup.  That should be easy enough to do.  Despite the ease, it should not lessen the importance by any degree.  Here is the script that I wrote recently to help determine which databases were in need of a log backup.

[codesyntax lang="tsql"]

[/codesyntax]

You will likely notice that I am querying both sys.databases out of the master database as well as dbo.backupset out of msdb.  Look more closely and you will see the employ of a Left Outer Join with two conditions on the Join.  In this case, both conditions are required to produce the Outer Join effect that I was seeking.  Had I used a script like the following:

[codesyntax lang="tsql"]

[/codesyntax]

You would see a considerably different result set.  The reason for this different result set is tied to the predicate used and the Join conditions.  And when one examines the execution plan, the difference becomes a little more evident.

That WHERE predicate converted our Left Outer Join to an Inner Join.  Now, if I had only wanted to return results for databases that had log backups, that might be fine.  I want to report on all databases and find not only the last log backup for a database, but I also want to find if a log backup is missing.  Therefore, I need to ensure that both conditions are declared as part of my Left Outer Join.

Running a query such as this will now provide us with some knowledge as to the database recovery models as well as which databases have had a log backup, have not had a log backup, and which do not need a log backup (based on recovery model).

Armed with this information, it is imperative to do a bit more digging now.  Knowing what the business deems as an acceptable data loss is important.  Find that out and then create transaction log backups accordingly so the business can recover appropriately in the event of a disaster.

page 1 of 1




Calendar
February 2013
M T W T F S S
« Jan   Mar »
 123
45678910
11121314151617
18192021222324
25262728  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, July 29, 2014