Something I learned while the SQL Server 2008 Internals book was in tech edit (thanks to our *awesome* tech editor Ben Nevarez - who, unfortunately, does not have a blog or anything...yet! (well, I'm hopeful)), was that you can use a FOREIGN KEY constraint to reference a UNIQUE index - one without a PRIMARY KEY or UNIQUE key constraint. At first glance this might seem like something relatively insignificant but in terms of reducing indexes and/or consolidating indexes it offers something that constraints do not. When you create a UNIQUE index you can use INCLUDE to reference (and include) non-key columns in the leaf level of an index. This offers more choices for covering and if you want to cover a query using INCLUDE but also have a UNIQUE column(s) as the key - you can do that with a regular index but not with a constraint based index. So, that got me thinking even more - can I use a UNIQUE index with INCLUDE and even a filters - from a FOREIGN KEY. My guess was that it probably wouldn't work because it would be too costly to have to verify it on every referencing row BUT, I did have hopes that a filter of IS NOT NULL would work. However, it does not. ;-( 

So, you CAN reference a UNIQUE index with INCLUDEd columns but not filters. Even that's really cool!

And, when you start your spring cleaning - try and cleanup and/or consolidate some of those redundant indexes!!

Cheers,
kt 

Given the general state of the economy...many companies are looking to cut back. Going back over what we've done and "optimizing" things -> budgets, expenses, etc. is the norm right now. And, scaling back is not always a bad thing - unless the wrong things are cut. Unless the wrong things are used to motivate you. Prioritizing and/or really assessing what gives you the biggest gains for your dollars is hard. In fact, one of the things that always seems to be first on the cutting block is training. Training is hard to quantify. And, the results of good training are also hard to quantify. Instead of fixing a problem (which you can often see the exact improvement) you might instead avoid a problem. Avoid downtime. Avoid data loss. Process more rows - with the same hardware. But, how do you know the cost of what could have happened. Ugh. To be honest, if I could do that - Paul and I would be on a beach. ;)

But, I do have a reason for this post... what should you be thinking? Where should you focus your attention? What can you cut - safely, temporarily, permanently and what might you help to prioritize?

Should you upgrade software?

• Is there a feature that makes something easier? Some new features are really powerful "big" features. For example, Policy-Based Management (PBM) might help you to better centralize certain rules (in PBM-speak "policies") and then enforce them on many servers - even 2005 servers... so, you might be able to upgrade a smaller number of servers and still get some of the benefits. Many of the tools work against multiple versions so you might be able to minimize (and/or prioritize) which servers you upgrade and slowly migrate others. Potentially following an every-other-version upgrade strategy... upgrading some servers from 2000 to 2008 and leaving some of your 2005 servers to wait to upgrade until SQL11 (the next version after SQL10 - which is SQL Sever 2008).
• Are you starting a new project - architecting a new database? Wouldn't it be easier to start on the newer version and get better longevity (maybe?!)? For example, sparse columns might make a major difference in your base table's architecture...and be easier than if you were to architect (and write all of the code) for 2005 but then later need to do a major architectural change to move to 2008 (well, to *really* benefit from things like sparse columns). There are some really good features in 2008 and some *might* warrant upgrading... upgrading now. But, if you don't have a direct need then I'd argue that you could probably stay with 2005 (or even 2000) and then push this out a bit until you absolutely need to move forward.

Should you upgrade hardware?

• Again, are there features that will directly impact: performance, availability, manageability?
• Can you wait? I can't really answer this and - for everyone - the answer is going to be "it depends". There might be something that significantly reduces costs and/or minimizes downtime and as a result, you'll just have to do cost-benefit analysis. This is a tough one... but, maybe you can do rolling upgrades and let some of the lesser servers take the hand-me-downs. :)
• Can you do rolling upgrades moving the most critical to a new server and then a less critical server to the one freed up by the last upgrade...

Is there anything you can do to get more out of what you already have??

In my opinion, this is probably even more important than the two above. Upgrading hardware and software is something you will ALWAYS need to consider but if you could get better performance, scalability and availability out of the hardware/software you have now, then you'll benefit *now* without additional funds spent (actual outgoing funds) and you still be able to leverage what you do today when you do upgrade. So, what this really translates to (IMO) is tweaking and tweaking a bit more - what you already have? How? What can you look for? What can you do to help??

• Upgrade to the latest service packs/hotfixes (at least upgrade to the free stuff - you might see some gains and in some cases (like SQL Server 2005 SP2+) you might get some new features. (important note: test this on a non-production server FIRST!!)
• Update your hardware's firmware? You might have missed an update that improves performance (important note: test this on a non-production server FIRST!!)
• Bottleneck Analysis - Some good resources for this are: Performance Tuning Using Waits and Queues and the SQLCAT team.
• Workload Analysis - Some good resources for this are: Troubleshooting Performance Problems in SQL Server 2005, Working with Tempdb in SQL Server 2005, Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005...well, there are lots of good whitepapers that are specific to certain types of workloads and/or perf problems...check out our whitepapers page here: http://www.sqlskills.com/whitepapers.asp and the CAT team's whitepapers pages here: http://sqlcat.com/whitepapers/default.aspx and the general SQL Server on microsoft.com pages here: http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx and for 2005 here: http://www.microsoft.com/sqlserver/2005/en/us/white-papers.aspx
• Maintenance - often overlooked and incredibly important. A database that has solid maintenance practices (fragmentation analysis and cleanup, VLF analysis and cleanup, transaction log management, finding corruption in its early stages through automated CHECKDB executions...) performs better, is easier to recover, might naturally stay smaller (more compact) and therefore require less hardware. In fact, analyzing indexes - to get rid of unused indexes and to consolidate redundant indexes can end up saving disk space, backup space, cache, maintenance costs, etc. Both Paul and I have blogged quite a bit about many of these!
• Other tips and tricks
  • Blogs... which is why you're here and there are so many out there! Here's a link I recently found that lists a bunch of SQL-related blogs: http://technet.microsoft.com/en-us/sqlserver/bb671052.aspx and, of course, Paul's post on "So many blogs" and the PASS list of blogs here: http://www.sqlpass.org/Community/BlogDirectory.aspx.
  • Webcasts... there are lots out there and we now have a page which has most of ours listed on it (thanks to Paul for creating this!!) here: http://www.sqlskills.com/webcasts.asp and there are LOTS more on TechNet, MSDN, etc.
  • Conferences... OK, maybe a shameless plug for conferences like SQLConnections *but* in having put together the agenda (with Paul) where we specifically focused on best practices topics and performance tuning - I can tell you that some of the tips and tricks that we recommend can significantly improve performance, may minimize needed disk space (by creating more optimal and often fewer indexes), may improve availability with better design practices and/or maintenance and much more than that! And, in getting away from the office for a few days and focusing just on learning you might do two things. First, you might learn some tips and tricks that you never would have (or it would have taken *a lot* more time and/or been harder to really understand?). Second, you might come back with a whole new and renewed enthusiam for doing things - and with an ordered/prioritized list of things to try. And, this might even help to motivate you because it also shows that your company really is committed to you/your job (having spent money specifically on your learning) - and you to them.

So, I do think that there are SMARTER ways to save. A well trained employee is worth a lot more than a cheaper one. And, there are smarter things to cut. I hope this might help you think of things to do and/or places to look to get better performance with what you have! I think blanket "no training" or "no upgrades" statements are never good for anything - even the budget (the longer term effects can be much worse - but also much harder to quantify).

Really, the answer is always different. It depends............

kt

Well, I had wanted to come up with a clever reply to my husband's oh-so-romantic blog post (here). And, well, in all honesty, that *is* the best present for me... but, probably not what most would say is romantic ;-). We've both had quite an effect on each other's presentation styles. We constantly remind each other of what works and what doesn't work. In fact, we even take notes when we watch each other's sessions so that we can very clearly and concisely state what we think works and/or doesn't work in a session. So, that got me thinking that I'd like to add a few things - mostly about how to get started in presenting - as his list for tips/tricks in presenting was extremely thorough!

Presenting tips:

• Demos: OK, everyone says this - yes, your demo should work. A demo that fails is easily the most frustrating thing to happen on stage. Not only is it frustrating for you (as the presenter) but the attendees feel like their time is wasted. And, in all honesty, this should never happen. When it does it usually happens because of one of the following: the demo wasn't prepared, the demo was overly complicated or something changed recently (and you hadn't recently tested it). And, all of these are preventable.
• Passion: Paul mentioned that you need to find a topic for which you have a passion - and I completely agree. This is probably the most important part for me. I've often been asked to talk about X or Y and if I can't find something really compelling to focus on, then it's definitely not my best session. In fact, less and less am I presenting other people's content and/or other people's abstracts. I really want to construct the session, the content and the message (in general). Having said that, what I want to add is passion/excitement/fun. The more fun you have with a session (and, I don't mean jokes - I just mean good content that makes sense and that is interesting), the more fun it will be to present. If you're having fun and presenting something with passion (that you have specifically for that topic), then it's contagious. People will remember more and take more away with them.
• Mentor: Paul mentioned this in his post, Greg Low mentions this in his series Presenting at Large Events, and large conferences such as TechEd have people like Richard Klees on-site to help with this as well. But, if you can find a colleague or other presenter to really watch your actual session (and you watch theirs), then you will get the best feedback. The actual session - in front of real attendees - is the only place where everything counts (unfortunately, all of your presentation gotchas don't always show in a "test run"). So, it's here where you can get the best feedback and where you can really learn.
• Don't stop learning: I've presented in one way, shape or form since 1987 when I gave "short-courses" on WordPerfect for the local students and staff at my University computer center (where I worked). And, in over 22 years, I still read evaluations, still look for books on presenting, still read blogs, watch webcasts, etc. A session can *always* be better. A presenter *always* has things they can work on.

Getting Started (which can also translate into improving your own skills/knowledge and even your own position within the company):

• Create a work/study group: Something that can help to "find the right tool" for the job is to divide and conquer. In our technical fields, no one can ever know everything. And, sometimes we get stuck in a rut - solving problem after problem with the same solution. I always use the "tool" analogy because I think it works well. If you have a problem in your house you don't always grab the hammer, right? But, I don't know how to use every tool either - if I don't know exactly what tool to use, sometimes I'll ask someone else to look at something. Sometimes a second set of eyes is exactly the thing to do. They might see an easier and/or a quicker solution. So, since you can't know everything - know a lot about a lot of things. Have a small work/study group that meets weekly or bi-monthly where each of you tackles a topic, a feature, something. Or, maybe you all read different blogs or newsgroups - then come back together and each do a 10 minute presentation on what you found. This can keep you better informed about other tools you might need to use someday AND can help you to start presenting (on a small scale)

NOTE: Before you do any of the following, you might want to check to see what your company policies are with regard to blogs, user groups, conferences, etc. With an appropriate disclaimer and/or no direct references to the company, you might be fine. Just check to make sure! 

• Present at a local user group and/or consider creating one if one doesn't exist: these a typically 10-50 people who meet at least once a month. Some user groups are much larger... But, this is a great way to learn more AND present what you know to a bigger group but, with (usually) a bit less pressure. The PASS website has a large list of chapters: http://www.sqlpass.org/Chapters.aspx. Consider even working with the local user group to have a side group that meets solely for improving presenting skills - you can present to each other in the same way that the work/study group would but this is a group outside of your company.
• Create a blog: there are lots of sites you can join to blog on and my personal opinion is to see if you can write consistent posts for a month or so (without them being published publicly) and then slowly publish the ones you've written so that you can stay ahead of the game. Blogging is hard. I have a hard time keeping up with it but if you have a good message - that's useful to others - then it's worth it.
• Create podcasts to post on your blog: This can be another way to present but without the stress of an audience.
• Write for magazines (TechNet, MSDN, SQL Server Magazine): This is a bit harder but if it's a good article then it's likely to be published either in print and/or on their associated websites. This can give you more exposure and in turn, help you to hone your message and your presentation skills.
• Submit sessions for larger conferences: This is the hardest and will take the most time. It's really nothing personal if you're not chosen the first or second time around (and these days the large conferences are even more competitive so it's *really* hard - don't get discouraged!!). Most large conferences want people who have a name that attendees can recognize. So, this takes time. The previous bullets *will* get you to a point where submitting to a larger conference makes sense - but, even then it's still not a guarantee that you'll be accepted. The more you blog, write, present at user groups, etc. the more people will get to know your name and your presentation skills. This is what the larger conferences are looking for - people that can give a good, technical session and make those 75 minutes the attendee spends with you worthwhile. Consider getting into a speaker competition like Speaker Idol which is usually at conferences such as TechEd but I've also been seeing this in local areas as well (with local user groups).

Key point: Clear and Concise "Presentation" for every aspect of life...

So, in re-reading Paul's post, I also read the comments and followed the links (which is what got me to Greg Low's 4 part series on presenting for large conferences - which is a really good series btw). In his presentation he mentioned a flight attendant... which, then made me surf/read a few things there. And, somehow I go sidetracked and ended up:

here: Galley Gossip: Flight Attendant Pet Peeve #1: Answer please!
and here: A woman missed her flight at the boarding gate HKIA
and even here: An open letter to unsatisfied users on the newsgroups

And, the combination made me think of a few things that really can go back to presenting but also just in general about comments, criticisms, and everything in life. If you want something then you need to ask for it precisely. If you want to get somewhere or do something or improve your position (in life, in work, whatever), then you need to be specific, concise, and clear. It's all about the presentation. Paul says this occassionally to the girls: Stop, Think, Speak. It's somewhat frustrating when he says this to me but it's usually after I've had a couple of glasses of wine ;) but, I think it's really interesting in the context of user groups, forums, presenting, life... You need to *present* something clearly and concisely for people to understand.

If you're posting a question on a forum and/or newsgroup - take time to research it first. Take time to present it clearly (what version, what's the EXACT syntax (can you provide a repro script?), what exactly are you doing, what is it doing that's wrong, what do you think it should be doing?). The better your question (i.e. the presentation), the easier it is to get people to understand. And, in the case of a forum/newsgroup, the easier it is to get a [useful] reply. Remember Tom Cruise/Cuba Gooding, Jr. in Jerry Maquire - "Help me, help you."

If you're presenting a session - present the problem (or idea), present the solution (code/demo/etc) and summarize why it works. Clear, concise and then follow through. And, this also reminds me of the most important presentation skills that everyone says but that I'm going to repeat one more time... your demo should WORK. Keep is simple. Straightforward (concise) and simple demos are really the most effective!

So, on that note, I'll remind you to look at one of the links I already posted above. This woman is very clearly stating the problem and reasonably coming up with a solution (to her missed flight): A woman missed her flight at the boarding gate HKIA. Yep, that's going to get someone to help you. Hmm. Clearly, she did not see Jerry Maguire.

Thanks for reading!
kt

Paul and I started discussing a comment that came up regarding the many issues surrounding logging & recovery. It's one of our favorite topics and in fact was the title to an article that Paul recently wrote for TechNet here: http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx. And, as a sidenote, depending on how much you already know about the transaction log - you might want to review that article first!

The comment that came up was related to a common misunderstanding on what is and what is not required to make a backup transactionally consistent when restored. And, in my opinion, some of the confusion as to whether or not log backups are "required" is because many changes have occurred release to release. Also, a lot of us say "log backups are required for better recovery" and while restoring log backups is what allows features like up-to-the-minute recovery and point-in-time recovery, not all strategies or recovery procedures actual require additional and/or separate log backups (some backups actually backup part of the log during their backup - and this is actually something that has changed release to release). And so, this is the reason for this post, I want to try and clear up a few of the many misconceptions about what happens with regard to the log during backup and restore. What's really interesting is that some of the best features (seemingly minor) have been around exactly this - the behavior of the transaction log during other backups and the requirement during restore. So, I thought I'd give a play by play from 2000 to 2005/2008 to discuss the differences and what's changed and why those changes were significant. The biggest changes were between 2000 and 2005.

First and foremost, the log portion of a database is required to make that database transactionally consistent. The transaction log is the key to SQL Server's durability (data integrity even after power loss). Transaction log backups are the key to our being able to recover from more catastrophic failures (possibly even point-in-time recovery if the right backup strategy exists). Inside the database, SQL Server doesn't really need all of the transaction details after they've guaranteed a transaction's durability (or, more simply put, once the effect of the change has been reflected in the data portion of the database then the details of that change are no longer needed in the log portion of the database). As a result, you can have SQL Server clear the "inactive" portion of the log by setting the database's recovery model to the SIMPLE recovery model. Loosely translated the SIMPLE recovery model means "when SQL Server no longer requires the transaction information to guarantee durability - then the log information can be removed from the log". Setting the recovery model to SIMPLE limits your backup options and makes administration easier (i.e. simple :)); however, it does not offer any other protection in the event of a more catastrophic disaster (because the log is being regularly cleared then there's no transactional information to backup). For some development/test databases and databases where data loss is not a major concern, then this can be an easy choice because log management (i.e. backups) does not need to be performed. However, if you want to minimize data loss - you can't choose the SIMPLE recovery model; you must choose either the FULL (which is the default) or the BULK_LOGGED recovery model. However, the discussion on when/why to choose BULK_LOGGED is a lengthy one and it does NOT impact the rest of this blog post. However, I did write a chapter for a SQL Server 2000 HA book and I described in detail the best uses for the BULK_LOGGED recovery model as well as the benefits and concerns. While this was written for SQL Server 2000, most of it *still* applies (and there are a few timeline based examples as well). You can download a pdf of this chapter here: http://www.sqlskills.com/resources/SQLServerHAChapter9.pdf.

As for the main purpose of this post - there are basically a few key questions that I want to answer/clarify by version:

• Is the log backed up as part of the other backups?
• Is it cleared?
• Is there anything else that's affected?
• What happens to the log during other database, filegroup, file, database-differential, filegroup-differential and file-differential backups? And, since the behaviors and internals seem to be grouped into two groups, I will differentiate between these two different groups of backup strategies with the following types:   
  • Database-level backup strategies are backups that use database and optionally database-differential backups
  • Granular backup strategies are backup strategies that use file and/or filegroup backups and optionally file-differential/filegroup-differential backups

SQL Server 2000
Database-level backups cannot occur simultaneously with log backups. However, granular backups *CAN* occur concurrently with log backups.
If a log backup is attempted while a database-level backup is running, then the log backup is paused. This can have the following affects:

• the transaction log may require auto-growth and become very large
• the transaction log for a secondary server (i.e. through log shipping) can fall *very* far behind the primary server. And, this is a HUGE concern for high availability. If a full backup takes 4 hours to run, then logs cannot be shipped for 4 hours. As a result of this limitation, some chose to use a granular backup strategy. The reason why log backups CAN occur concurrently in SQL Server 2000 is because, in implementation, SQL Server does NOT to backup the log as part of these more granular backups. As a result, transactional integrity is not guaranteed until the appropriate log chain is rolled forward. This has the following affects:
  • Granular backups only support the BULK_LOGGED or FULL recovery models (somewhat negative but not really)
  • The transaction log backups could run and even clear the inactive portion of the log while these granular backups were running (this is a huge benefit because it limits the need to auto-grow during these backups)
  • Recovery during restore is required (for transactional integrity) which means that all logs need to be restored to cover the time of the granular backup (and then all of those up-to-the-minute or to the desired point-in-time). And, even if a filegroup is set to READ_ONLY - *all* transaction logs need to be restored (this is a big negative but there is a trick: perform periodic file/filegroup differentials (after setting the filegroup to READ_ONLY) so that you can avoid having to perform numerous transaction log restores).

So, to answer the questions for SQL Server 2000:

• Is the log backed up as part of the other backups?
  • for Database-level backups: YES
  • for Granular backups: NO
• Is it cleared?
  • When a transaction log backup is performed then the default behavior is to clear the inactive portion of the log
  • When a database-level backup is performed AND there's no corresponding log chain (meaning the first time you backup the database OR the first time you backup the database after the transaction log chain was broken), then the transaction log is cleared. NOTE: Breaking the continuity of the log is relatively easily done in SQL Server 2000 when someone executes a BACKUP LOG with NO_LOG or a BACKUP LOG with TRUNCATE_ONLY command. To disable these from execution (for the FULL or BULK_LOGGED recovery model, use TRACE FLAG 3231). This is a VERY COOL and *SAFE* trace flag. I blogged about this trace flag in a "MSDN webcast Q&A" here. An important side note here is that in SQL Server 2000, log backups can be performed AFTER the continuity of the log has been broken. So, if someone manually cleared the log (using NO_LOG or TRUNCATE_ONLY) and did NOT follow that with a database-level (or appropriate granular backups), then scheduled log backups could continue to run without failure or errors. However, log backups performed AFTER the continuity of the log has been broken CANNOT be restored. So, during recovery you might receive an error that a log backup cannot be applied because it's too "late" to apply. Using Trace Flag 3231 reduces this possibility. However, SQL Server 2005 fixes some of these issues.
• Is there anything else that's affected?
  • Log backups are paused during database-level backups
  • When restored, database-level backups are transactionally consistent (and can be recovered directly - without restoring additional logs)
  • When restored, granular backups require transaction log backups to guarantee transactional integrity (note: this can be complex to determine the "minimum effective log sequence" and I wrote a series of articles for SQL Server Magazine on how to determine the appropriate log sequence here)
• What happens to the log during other database, filegroup, file, database-differential, filegroup-differential and file-differential backups?
  • for Database-level backups: log backups cannot occur concurrently 
  • for Granular backups: log backups can occur concurrently and are required for recovery

SQL Server 2005
The biggest improvement in SQL Server 2005 was that log backups are no longer paused by database-level backups - they *can* occur simultaneously; however, this change to database-level backups also applied to granular backups. While 2000 did allow log backups at the same time as a granular backup, they did so by NOT maintaining transactional integrity in the backup. In SQL Server 2000, you need to restore logs to make the granular backup transactionally consistent. In SQL Server 2005, they changed ALL backup strategies to follow the same behavior - database-level and granular backup strategies ALL backup the required log information needed to recover the backup to a transactionally consistent point in time which is essentially when the backup completes (this is a lot more complex than it sounds but Paul wrote a comprehensive post on exactly what this means here). Simply put, this requirement means that transaction log backups CAN occur concurrently; however, the log CANNOT be cleared until the backup completes. The primary negative effect is that the transaction log may require auto-growth and become very large. However, the positives are that you can do granular backups in any recovery model (although there are still some limitations to how this works in the SIMPLE recovery model but they added a new option during backup to allow a backup of ALL of the READ_WRITE_FILEGROUPS as a unit - separately from the read-only file groups which could be backed up at any time after they are set to READ_ONLY).

So, to answer the questions for SQL Server 2005:

• Is the log backed up as part of the other backups?
  • for Database-level backups: YES
  • for Granular backups: *YES*
• Is it cleared?
  • When a transaction log backup is performed then the default behavior is to clear the inactive portion of the log
  • When a database-level backup is performed AND there's no corresponding log chain (meaning the first time you backup the database), then yes, the inactive portion of the log is cleared. As far as breaking the continuity of the transaction log... In SQL Server 2005, they significantly reduced the problems that occur after the log chain is broken by NOT allowing log backups to continue. If a log backup is attempted after the continuity of the log is broken then you will receive error: 
      Msg 4214, Level 16, State 1, Line 1
      BACKUP LOG cannot be performed because there is no current database backup.
    So, this means that you don't necessarily need the trace flags. However, I still recommend using the trace flag because it would be better to not break the continuity of the trace flag to begin with! And, in fact, in SQL Server 2005, there are two trace flags: 3231 and 3031. They are both safe and here's how the two differ:
    • Trace Flag 3231 (same as 2000): When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG do not allow a log backup to run if the database's recovery model is FULL or BULK_LOGGED.
    • Trace Flag 3031 (new in 2005): When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG run as a CHECKPOINT - regardless of recovery model.
• Is there anything else that's affected?
  • Log backups are *NOT* paused during database-level backups
  • When restored, database-level backups are transactionally consistent (and can be recovered directly - without restoring additional logs)
  • When restored, granular backups are transactionally consistent (and can be recovered directly - without restoring additional logs). However, you must always remember that the database cannot be brought online until the entire database is at a single transactionally consistent point in time. All read-write-filegroups must be restored as a unit (if in the SIMPLE recovery model) OR you must use transaction log backups to recover the entire database up to the SAME point in time.
• What happens to the log during other database, filegroup, file, database-differential, filegroup-differential and file-differential backups?
  • for Database-level backups: log backups *can* occur concurrently (but the log will not be cleared until the backup completes) 
  • for Granular backups: log backups can occur concurrently (but the log will not be cleared until the backup completes) 

SQL Server 2008
Almost everything is the same in SQL Server 2008 as it was in 2005 - they made the largest number of improvements in 2005. However, one thing did change. In SQL Server 2008, the BACKUP LOG with NO_LOG and BACKUP LOG with TRUNCATE_ONLY options are not allowed at all. There is no need for the trace flags (3231/3031) because breaking the continuity of the log is not allowed (well, there is still a way... I'll get to that in a moment :)). In SQL Server 2008, if BACKUP LOG with NO_LOG or BACKUP LOG with TRUNCATE_ONLY are attempted, you will receive this error:
     Msg 3032, Level 16, State 2, Line 1
   One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options.
But, what if you really don't want to backup the log? Why? Take this scenario (from a real customer!)... You have a 10GB database that's been around for quite some time AND you're doing regular full database backups... then, all of a sudden you run out of disk space. In looking around for large files (to investigate why you ran out of space), you find that this 10GB database's log is 987GB... so, you wonder - what happened? A database that is in the FULL recovery model (remember, this is the default) requires transaction log management. The easiest way to manage the log is with regular log backups; however, you're only doing full database backups (which do NOT clear the log). As a result, the transaction log grows and grows and grows and grows - until you're out of disk space (Paul demo'ed this in a TechNet Podcast here). At this point, how do you get rid of this 987GB transaction log? In prior releases, you can "clear" the log by using TRUNCATE_ONLY or NO_LOG but in 2008, what do you do? Switch to the SIMPLE recovery model. And, if you only want to do full database backups, stay there. And, if you want to physically shrink down the size of the transaction log file to a reasonable size - check out these two related blog posts: 8 Steps to Better Transaction Log Throughput and Transaction Log VLFs - Too many or too few?. And, in related news, Linchi Shea posted a good post on some tests he ran related to too many VLFs here and a second post that shows that some workloads don't see any issues wrt to lots of VLFs here. But, the long story short is that you still want to be proactive about creating a reasonably sized transaction log (my two other previously mentioned posts). Significant auto-growth can cause problems and backup operations (and managment in general) can be more difficult with lots of VLFs.

Wow, that was much longer than I was expecting... and, in writing it all down - pretty complex (I had a hard time trying to section things but I think this works?!). Regardless, all the facts are there so this should help to clarify what happens the when, where and why - wrt to the transaction log. Let me know if you have more questions!

Thanks for reading,
kt

I know where I go but where the heck does the time go (it seems to move much faster than I)? First off, I hope everyone had a truly lovely holiday season. I know they've long since past for everyone but maybe this reminder will bring back a nice memory from the holidays as it does for me. Paul and I escaped the world of SQL and focused on finding frogfish. Unfortunately, it just wasn't the right time of year to find them though. Oh well - we did see some very coolstuff including a batfish (the odd-shaped-bottom-dweller kind) and, we also read a ton of books (a completely strage combination of books - list below) and we relaxed.  It was really quite lovely.

Once the holidays were over, we were back into the fire... Paul worked from home (mostly writing) and did a lot of work from there - including blogging (as always) and I was back in the classroom. We were only home for 10 days and then we were off again. Right now, we're both on the road - in Hyderabad, India this week (and last) and Bangkok, Thailand next week - teaching some classes on performance tuning, maintenance and high availability. I thought I'd do a quick post to remind you that I do exist (yes, I know - I really do *want* to blog more but it's not easy for me... first of all, before I publish a blog post I probably read it 50 times and that's after I even get the courage to write one... blogging is just different for me than it is for Paul... for that, I'm sorry. I'm *really* going to try... no, really. I will try to get a really good post out there as often as possible but for right now, I'm not going to give a number or a goal - just know that it drives me nuts that I don't blog all that often.)

In the interim, interviews/podcasts - for some reason - are a lot easier for me. Paul and I did a few interviews with TechNet in December and these are being made available for download as they're edited for publishing. The first one is available now and it's titled: New Features in Partitioning and Indexing for SQL Server 2008. Here are the download links:

• WMA - http://download.microsoft.com/download/F/5/2/F52567BB-D32D-437E-A9A7-60D898AA03C7/TechNetRadio-01132009-web.wma
• MP3 hi-bandwidth - http://download.microsoft.com/download/F/5/2/F52567BB-D32D-437E-A9A7-60D898AA03C7/TechNetRadio-01132009-hi-web.mp3
• MP3 lo-bandwith - http://download.microsoft.com/download/F/5/2/F52567BB-D32D-437E-A9A7-60D898AA03C7/TechNetRadio-01132009-lo-web.mp3

OK, that's it for me for today. But, I do have a couple of other posts brewing. I will work to get these out as soon as possible as I have a small break from teaching, writing, and conferences (yes, I've been writing too!). Oh, speaking of which, we've *all* finished the SQL Server 2008 Internals work (Kalen doing the large majority) and it's getting really close! You can pre-order it on Amazon here: http://www.amazon.com/Microsoft%C2%AE-SQL-Server%C2%AE-2008-Internals/dp/0735626243/ref=sr_1_1?ie=UTF8&s=books&qid=1233824791&sr=1-1.

Thanks for reading - I'll be back soon!
Kimberly

As for books, in no particular order:  

• Robin Maxwell's Mademoiselle Boleyn
• Josh Grogan's Marley & Me
• Sheridan Hay's The Secret of Lost Things
• Tucker Max's I hope They Serve Beer in Hell
• J.K. Rowling's Harry Potter books 2, 3, 4, 5 (these were re-reads - always a safe bet to bring with me and a fun re-read as Kiera (7) goes through the series now too) and where is the 6th movie? Ugh. ;)
• Tom Philbin's The Killer Book of Serial Killers: Incredible Stories, Facts and Trivia from the World of Serial Killers
• Stephenie Meyer's Twilight (last, but not least!)

OK, I told you it was a very wide-ranging book list; a few were purchased at the airport on the way. I've since read the entire Twilight series by Stephenie Meyer: New Moon, Eclipse, and Breaking Dawn (and I have to admit that I loved the series (especially Breaking Dawn))... I really hope she completes Midnight Sun; I absolutely loved Twilight from Edward's perspective (maybe even more than from Bella's). And, if she continues writing in this world - I really hope she writes as Renesmee. The Twilight movie was not spectacular - defnitely read the book before going to the movie - but, I loved the casting of Bella and Edward. And, I'm hopeful that the next movie will be *a lot* better. Next on my reading list, I'm planning to read many Robin Maxwell books starting with The Secret Diary of Anne Boleyn. In the end, the holidays were really relaxing.

Oh, and here's a great shot from India... At least he has a helmet??