This is a quick answer to a question I was sent today by someone who'd read Kimberly's partitioning whitepaper - Partitioned Tables and Indexes in SQL Server 2005 - and is implementing a "sliding-window" scenario. (This is a mechanism to allow fast insertion and deletion of significant portions of data into/from a partitioned production table. Insertion is done by taking a table and making it a new partition of the production table - called switching-in. Deletion is done by removing a partition from the production table and making it into a stand-alone table - called switching-out.)

The question is - what indexes are required on the staging table to prevent the ALTER TABLE ... SWITCH PARTITION statement from failing with a message like that below:

Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'PartitionTest.dbo.StagingTable' for the index 'NC_Birthday' in target table 'PartitionTest.dbo.ProductionTable'.

The answer is that the staging table has to have the exact same indexes - clustered and non-clustered - as the production table. I asked Kimberly if it has to have the same constraints too - the answer is yes, plus the staging table has to have a trusted constraint on it such that SQL Server can tell (without checking all the data in the staging table) that all the data satisfies the partitioning function for the partition that you're switching-in (i.e. the partition that the staging table will become in the production table). If it doesn't, the switching-in will fail with the following error:

Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionTest.dbo.StagingTable' allow values that are not allowed by range defined by partition 4 on target table 'PartitionTest.dbo.ProductionTable'.

One thing that confuses people is that SQL Server does not create the target table for you when doing a switch-out of a partition. The target table has to exist and have the exact same schema as the production table. Also, it has to be completely empty - otherwise you'll get an error like:

Msg 4905, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The target table 'PartitionTest.dbo.StagingTable' must be empty.

The must-be-empty requirement also holds for switching-in operations - the partition that will be created has to be empty otherwise a similar 4904 error results.

Hope this helps!

There's a well known problem that every time a backup operation succeeds a message is written into the error log and Windows event log. If you're taking very frequent log backups (say every 5 minutes) of multiple databases, that's a significant amount of clutter in the logs. Well - now there's a fix!

Kevin Farlee, the Storage Engine PM responsible for (among many other things) BACKUP and RESTORE has just blogged about a trace flag - 3226 - that's been in the product since SQL Server 2000 that will suppress the success messages. He's planning to document this (and other) trace flags in this area starting in SQL Server 2008. Excellent!

Myth 1: A full database backup only contains the transaction log from the start of the backup to the end of the backup

When you restore a full database backup, you get a transactionally consistent database. Consider the case where there's an active transaction that doesn't commit until after the backup completes. If the backup only contained the log that occured while the database was being backed up, how would it roll back the active transaction. It *has* to include enough transaction log to roll back the active transaction. The start LSN of the log included in a database backup is the minimum of:

• LSN of the last checkpoint
• LSN of the start of the oldest active transaction
• LSN of the last replicated transaction

Let me prove it to you. I'm going to create a database, put it into FULL recovery mode, start a transaction, checkpoint, and then take a backup. The checkpoint ensures the page I've altered is flushed to disk.

CREATE DATABASE stopattest;

GO

ALTER DATABASE stopattest SET RECOVERY FULL;

GO

BACKUP DATABASE stopattest TO DISK = 'c:\sqlskills\stopattest.bck' WITH INIT;

GO

USE stopattest;

GO

CREATE TABLE t1 (c1 INT);

GO

BEGIN TRAN;

INSERT INTO t1 VALUES (1);

GO

Now in another connection I'll take another full database backup.

BACKUP DATABASE stopattest TO DISK = 'c:\sqlskills\stopattest.bck' WITH INIT;

GO

The msdb.dbo.backupmedia table will tell us the relevant LSNs in the backup (I added some spaces to delineate the prts of the LSN for clarity):

SELECT last_lsn, checkpoint_lsn, database_backup_lsn FROM msdb.dbo.backupset

WHERE database_name = 'stopattest';

GO

last_lsn              checkpoint_lsn        database_backup_lsn
--------------------- --------------------- ---------------------
21 0000000190 00001   21 0000000174 00037    21 0000000058 00037

[Edit: After swapping some email with Kalen Delaney, I realized that when I originally put this together I had more log records in the post and when I removed them I messed up the description of the (21:174:37) LSN - its now corrected below)

So you can see the checkpoint that begins the backup was at (21:174:37). The LSN of the first log record that the backup contains is (21:58:37), which is before the start of the backup. And the backup contains all the log from then until (21:190:1). Now let's look at the actual transaction log to see what these LSNs correspond to.

SELECT [Current LSN], Operation, [Transaction Name] FROM fn_dblog (null,null);

GO

Here's some selected output:

Current LSN              Operation        Transaction Name
------------------------ ---------------- ------------------
00000015:0000003a:0025   LOP_BEGIN_CKPT   NULL
                (this is the calculated minimum LSN the backup must contain (21:58:37) - which is (15:3a:25) in hex)
.
.
00000015:00000061:0001   LOP_BEGIN_XACT   user_transaction
                (here's my transaction starting - before the backup started but within the LSN range contained in the backup)
.
.
00000015:000000ab:0004   LOP_BEGIN_XACT   Backup:InvalidateDiffMaps
                (this is the backup clearing the differential bitmaps)
.
.
00000015:000000ae:0025  LOP_BEGIN_CKPT    NULL
                (this is the checkpoint that BACKUP does - matching the checkpoint LSN above)
.
.

So - this clearly shows that the backup contains more than just the log from the time the backup was running.

Myth 2: It's possible to do a STOPAT with only a full database backup

This myth is that its possible to use STOPAT with a full database backup to stop during the time the backup was being taken. The argument FOR this myth is that the backup contains the log for all the changes that happened while the backup was being taken, so it must be possible to stop at any point in time. Technically, that's correct, but in practice it's wrong - you cannot stop at a point while the backup was running, using only the database backup.

This one's more complicated to explain. Doing a STOPAT operation means getting the database to a state where operations later than the time or LSN specified in the STOPAT clause haven't affected the database yet. A database backup reads pages that may or may not have been changed while the backup was running. If they are changed, it could be at any point while the backup is running.

Consider the case where page X is changed at LSN (10:45:12), *just* before the backup completes and is read by the backup at the time equivalent to LSN (10:45:13). The backup will contain the changed page image, plus the log record for the change. What if I want to stop at a point while the backup was running but *before* the change to page X, say at LSN (10:44:00). The backup only contains the image of page X at LSN (10:45:12) - how can it be put back to the image at the time we want to stop at? The argument is that we have the log record for the change - can't SQL Server just undo it?

No. It won't even see it. STOPAT works by recovering the database up to the point that the STOPAT specified. If we ask to stop at LSN (10:44:00), then the log will only be read and recovered up to that point. However, because the database backup didn't read page X until LSN (10:45:13), it only has the image of it from when it was altered at (10:45:12). This clearly won't give a database image as of (10:44:00).

The only way to stop at a particular time/LSN, is to have images of *all* database pages from before that time/LSN (i.e. from the *previous* database backup) and then restore all the transaction logs up to and including the time/LSN to stop at.

Hopefully that makes sense.

(Been a few days since I posted - had some real work to do Today I'll post a few things from the queue that's been building up)

This is part Q&A and part follow-on from my last post about running index maintenance when a database is mirrored.

A customer has a maintenance plan that involves running regular ALTER INDEX ... REORGANIZE on a 100GB clustered index to remove fragmentation. Three weeks ago they added database mirroring, with the database setup for synchronous mirroring. Every so often, they see the state of the mirror change from SYNCHRONIZED to SYNCHRONIZING and then a bit later back to SYNCHRONIZED. What's going on? Once a synchronously-mirrored database is synchronized, it should ever get out of sync, right?

Well not quite - if the communication link between the principal and the mirror is broken, then the mirror becomes unsynchronized. The exact behavior in this situation depends on how mirroring is setup and what's failed:

1. If there's no witness instance, then transactions will continue on the principal database but the transaction log starts to grow, because the transactions can't be cleared from the principal's log (even after a log backup) until they've been sent to the mirror. The database is running 'exposed'. Once the link is reestablished, the mirror while synchronize again.
2. If there's a witness, and the witness can still talk to the principal, then everything continues as in #1
3. If there's a witness, and the communication link between it and principal is also broken, the the principal will stop serving the database - transactions will stop. In this case, if the mirror and the witness can still see each other, then a failover will occur.

There are some great Books Online entries that describe all of this - see http://msdn2.microsoft.com/en-us/library/ms179344.aspx to start with.

The customer had situation #1. Every so often the mirror would change state and it seemed to coincide with the defrag job. Looking in the error log shows messages like:

2007-10-24 11:43:36.21 spid23s     Error: 1474, Severity: 16, State: 1.

2007-10-24 11:43:36.21 spid23s     Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://roadrunnerpr.sqlskills.com:5022'.

So the network link was dying sometimes when the defrag was running - that explains the switch between SYNCHRONIZED and SYNCHRONIZING. Why the network link was dying is still under investigation but it seems like the additional transaction log generated by the defrag job was causing the network to become overloaded and some component of it wasn't behaving correctly under load.

There are a few things to learn from this:

1. Not only do you need to make sure that your IO subsystem can handle the load on it correctly, you also need to make sure your network can handle the load on it. There are a bunch of tools available to stress-test network paths - one simple one is TrafficEmulator.
2. When you're running on your test system before going into production, make sure you test *everything* as if you were running in production - including maintenance jobs because they can add significant load to a production system.
3. When you implement an HA solution such as mirroring, consider all the ways that transaction log will be generated when figuring out the required network bandwidth to support your HA configuration - something like a defrag or rebuild can cause an enormous spike in log generation

Two weeks to go until the conferences start...

I've revamped the main Upcoming Events page on SQLskills.com to list all the DBA/IT-Pro, Developer, and BI sessions we're collectively doing at SQL Connections Fall, Microsoft TechEd Developers, Microsoft TechEd IT Forum, and a Microsoft TechNet Deep-Dive in November. (Now that I've finished my Lego model, what else am I going to do on a wet Sunday afternoon?)

And there's a lot of it - 4 full-day workshops, 17 sessions, 9 chalk-talks, 3 instructor-led labs, and 1 lunchtime demo session - but they're filling up fast and it looks like we'll be doing a few repeats at IT Forum. Anyone coming to any of our full-day workshops will get one of our DVDs packed full of Hands-On labs - incidentally, the Always-On DVD will be the base for our post-con workshop at SQL Connections.

One of the events I haven't mentioned before - the TechNet Deep-Dive. This is being organized by Microsoft in Switzerland and will be a full-day workshop taught by Kimberly and I on database maintenance. Checkout the link above for details on how to register.

We've had some questions about which of the full-day workshops are applicable to customers running SQL Server 2000. Well, a lot of the technology we talk about was introduced in SQL Server 2005, but the concepts and best-practices remain the same. I'd say that the database maintenance workshop probably has the most content that will transfer to SQL Server 2000. However, if you're planning to upgrade to SQL Server 2005 or 2008, these workshops are packed full of info on new features and syntax that you'll need to know.

Look forward to seeing you at one of the events!

Two of the cool features in SQL Server 2005 are CROSS APPLY and DMVs (Dynamic Management Views). Now, far be it for me to get my hands dirty explaining developer stuff like CROSS APPLY but I was having a discussion with Colin Leversuch-Roberts in the UK about the composability limitations of the sys.dm_db_index_physical_stats DMV. (Btw - you should check out Colin's blog post series on Analysing Indexes - lots of useful stuff).

So CROSS APPLY lets you do join-like functionality with table-valued functions that take parameters - which you can't do using JOIN. This works for most of the DMVs, but some of them are written to an older internal implementation that doesn't support CROSS APPLY, and sys.dm_db_index_physical_stats is one of them. If you try it you'll get an error like:

During SQL Server 2005 development I did a comprehensive rewrite of the Books Online entries concerned with estimating index, table, and database size (see http://msdn2.microsoft.com/en-us/library/ms187445.aspx). I was recently having a discussion with Øystein Sundsbø about a bug in one of the formulas I wrote and it turns out he's written a neat tool that codifies all the formulas.

In case you're interested, the part of the formula I had incorrect was for figuring out how many pages would be needed for the upper levels of an index b-tree. My formula was:

Num_Index_Pages = ∑_Level (Index_Rows_Per_Page)^{Level – 1}
where 1 <= Level <= Levels

and Øystein came up with a better formula:

Num_Index_Pages = ∑_Level (Total_Num_Rows/(Index_Rows_Per_Page)^Level )
where 1 <= Level <= Levels

where in both formulas:

Levels = 1 + log _{Index_Rows_Per_Page} (Total_Num_Rows / Index_Rows_Per_Page)

Check out Øystein's cool tool at http://dbgoby.blogspot.com/2007/10/db-goby-v10.html!

Today I finally managed to hook-up with SQL Server MVP and Regional Director Greg Low so he could interview me for his podcast show - we'd spent the last few months juggling schedules and time-zone differences (he's 17 hours ahead of Redmond) but today the stars aligned and we made it. You can download the interview at www.SQLDownUnder.com - show #24. Thanks Greg!

Ok - this is really geeky, but I love making models, and especially the harder Lego models. Lego is one of the coolest toys ever and I've been a big fan since I was a small child. Lego announced in the Spring their biggest Lego model ever - a large scale Ultimate Collector's Millenium Falcon^TM with 5195 pieces (check it out here on the Lego site). I pre-ordered mine right away!

Kimberly was away teaching for Microsoft in India the first two weeks of October this year. So what does Paul do when Kimberly's away? Well, apart from feverishly blogging on my new blog, I made the Falcon, which arrived the day before she left. I reckon it took me about 25-30 hours total time over the course of two weeks.

Here's a picture of the finished model, with a Diet Pepsi can alongside to give you an idea of the scale (click on it to get to a hi-res image you can scroll around to see the detail). Below is a bit of detail blow-up featuring my nick-name-sake Chewie.

      Pretty cool eh?

There's been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs.

Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down to the amount of transaction log generated and whether this causes a problem.

• In synchronous mirroring, the additional log being generated could overload the network link between the principal and mirror. This will slow down the hardening of regular transactions in the mirror's transaction log, and thus led to a decrease in transaction throughput on the principal.
• In asynchronous mirroring, the additional log being generated could again overload the network link - but this time there's no requirement for transactions to harden in the mirror before they can commit on the principal, so instead the SEND queue on the principal gets really large. Now, in asynchronous mirroring the SEND queue represents the amount of work that will be lost if a failover occurs, as its all the transaction log that hasn't yet been sent to the mirror. So, in asynchronous mode, a large index rebuild operation could lead to increase exposure to data loss in the event of a failover.

In SS2008, the log stream compression I blogged about here should go a long way to alleviating this problem. There are three ways I can think of to reduce the amount of transaction log generated by and index maintenance plan in SS2005:

1. Use a potentially less expensive (in terms of logging) solution for removing index fragmentation. Doing an index rebuild is guaranteed to generate an equivalent amount of transaction log to the size of the index being rebuilt, no matter how much fragmentation there is - because an index rebuild always rebuilds the entire index. The alternative is to do an index reorganize, either using my old DBCC INDEXDEFRAG or the new ALTER INDEX ... REORGANIZE. These will only generate transaction log when index pages are compacted and reorganized - so for less heavily fragmented indexes. There's no hard and fast rule here but I generally say where Logical Scan Fragmentation/Average Fragmentation in Percent from DBCC SHOWCONTIG/sys.dm_db_index_physical_stats, respectively, is less than 30%. You also need to consider page density too - but really this is a topic for a whole other post.
2. Be very selective on which indexes you choose to rebuild/reorganize. A lot of people have a maintenance plan that does this for every index every week, without checking whether the index is even fragmented or whether removing fragmentation for an index improves workload performance. Again, this a whole other topic but there is an old whitepaper for SS2000 I helped with that describes some of this - Microsoft SQL Server 2000 Index Defragmentation Best Practices.
3. Partition the tables/indexes so that the changing portion of the data is the only portion that's affected by index maintenance. If most of your data is read-only, there's no point in having it included in reindex/reorganize operations, right? Kimberly recently wrote a blog post about such an architecture here.

For more info on database mirroring performance considerations, checkout the whitepaper Database Mirroring: Best Practices and Performance Considerations. There's also a slide deck presentation based on this whitepaper that's been presented at various TechEds (I did it in China and Hong Kong last year and Kimberly did it in South Africa last year) - you can download it from the Hong Kong website here.

The bottom line is when mirroring is in the mix, you need to be more intentional with your database maintenance.

Thanks for your patience and to all those who emailed to let me know. All the SQLskills.com blogs have been updated to the latest dasBlog version and everything's working again. I'd appreciate you taking the time to go back and re-enter any comments you tried to over the last few days.

Thanks!

(I've heard from many of you that the Comments feature of my blog isn't working. I know - there's an issue with our blog engine that we're fixing. My apologies - I'll post a quick note when it's fixed.)

After posting last week about a BACKUP feature that I don't like (WITH NO_LOG - see here), I thought I'd do a quick post this week about a feature that was introduced in SS2005 for BACKUP that I DO like - the COPY_ONLY option to BACKUP DATABASE and BACKUP LOG.

Here's a situation I've seen several times that really screws people up. A savvy DBA of a busy web-fronted sales business has a rigorous backup schedule setup - daily full backups at midnight and differential backups every 4 hours. Everything's working perfectly. One day a disaster strikes at 7pm and the storage for the database is destroyed. The DBA starts restoring the backups using WITH NORECOVERY, gets to the noon differential backup and gets the following message:

RESTORE DATABASE production FROM DISK = 'c:\sqlskills\production-diff12pm.bck' WITH NORECOVERY;

GO

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Uh-oh. That's not good. That says that the 12pm differential backup does not have the same differential base as the 4am and 8am ones. How can that have happened? The DBA takes a look in the backup history tables in msdb (stripping out all the log backups):

SELECT name, backup_start_date, type, first_lsn, database_backup_lsn

FROM msdb.dbo.backupset WHERE database_name = 'production';

GO

name                           backup_start_date       type first_lsn            database_backup_lsn
------------------------------ ----------------------- ---- -------------------- --------------------
production Full 10/14/07       2007-10-14 00:00:00.000 D    88000000025300001    0
production Diff 4am 10/14/07   2007-10-14 04:00:00.000 I    118000000003000160   88000000025300001
production Diff 8am 10/14/07   2007-10-14 08:00:00.000 I    144000000070500160   88000000025300001
NULL                           2007-10-14 10:29:50.000 D    161000000056100147   88000000025300001
production Diff 12pm 10/14/07  2007-10-14 12:00:00.000 I    161000000062800034   161000000056100147
production Diff 4pm 10/14/07   2007-10-14 16:00:00.000 I    173000000054100144   161000000056100147

Aha! Look at the highlighted date in the output - someone took a full database backup of the database at 10.29am. The DBA checks and finds that one of the developers wanted a copy of the production database to play with so took a database backup. He restored the backup and then deleted both it and the database. Looking at the database_backup_lsn field, we can see that all the backups up till the accidental backup (look at the LSNs highlighted blue) have the differential base equal to the first_lsn of the full backup from midnight. The two backups after that have the differential base equal to the first_lsn of the accidental full backup (the LSNs highlighted red).

Oops! That means that the production database cannot be rolled forward any further than the last log backup before the accidental full backup was taken - losing more than 8 hours of data completely. Even though all the subsequent backups are intact, the initial full backup for them no longer exists so they're useless!

So how can a developer get a copy of the database without screwing up a recovery from a potential disaster? Using the new COPY_ONLY option. Taking a full backup with this option does not make the new backup a differential base - it does not clear any of the differential bitmaps and basically doesn't interfere with the regularly scheduled backups. Apart from that, it's a regular full backup of the database. One thing to bear in mind is that it's a one-off - you can't use one of these backups as a differential base, so you can't take COPY_ONLY differential backups. If you specify COPY_ONLY with DIFFERENTIAL, the COPY_ONLY option is ignored.

One other cool thing is that you can specify this option for a BACKUP LOG command too. This behaves the same way - it takes a log backup, but does not change the transaction log at all (i.e. it doesn't make any portion of the log inactive and permit log truncation). This is useful for doing online file restores without having the necessary backup of the tail of the log affect the log backup chain. More on that in a later post...

[Edit: In the initial version of this post, the DBA's backup strategy included log backups. I went through a couple of versions of this post before settling on full + diffs, but I forgot to remove the reference to log backups. In the first comment, Mark House correctly points out that an accidental full backup doesn't prevent a DBA with a complete log backup chain from recovering to any point in time. Apologies for the confusion!]

All the razzamatazz about new releases go on about what's new but hardly ever is there discussion of what's been removed. So that's the topic of this short post.

In the Books Online that comes with the July CTP of SS2008 (here's a link to the download page), the topic Discontinued Database Engine Functionality in SQL Server 2008 lists the following:

This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture - the use of BACKUP LOG WITH NO_LOG (or TRUNCATE_ONLY - they're synonymous) to allow log truncation.

How is it used?

The common use is when the transaction log grows to be inordinately large on a database that's in FULL (or BULK_LOGGED) recovery mode. This happens after a database backup has been taken, which switches the log into a mode where it won't truncate until its been backed up. In these circumstances, if you don't take a transaction log backup, the log will continue to grow until it runs out of disk space.

The alternative to taking a real log backup is to issue a BACKUP LOG dbname WITH NO_LOG command. Let's see this in action - note that I'm not advocating its use but I want to show you what it does. First off I'll create some transaction log after taking a full database backup:

USE nologtest;

GO

BACKUP DATABASE nologtest TO DISK = 'c:\sqlskills\nologtest.bck' WITH INIT;

GO

CREATE TABLE t1 (c1 INT, c2 VARCHAR (8000))

GO

SET NOCOUNT ON

GO

DECLARE @a INT;

SELECT @a = 1;

WHILE (@a < 10000)

BEGIN

INSERT INTO t1 VALUES (@a, REPLICATE ('a', 8000));

SELECT @a = @a + 1;

END;

GO

Success!

Hold on, are you sure?

Why is it bad?

Do you realize what just happened? We discarded the contents of the transaction log since the last full/differential/log backup. That means that until the next full or differential backup completes, all work since the last backup completed will be lost if the database is destroyed. So if your business relies on not losing data/work in the event of a disaster - then what I just did is sacrilege!

The whole point of FULL (and BULK_LOGGED) recovery modes is to preserve the transaction log so that the database can be recovered right up to the point of a failure, or to do point-in-time restores. Using BACKUP LOG WITH NO_LOG negates all of that. (For completeness, note that you can't do a point-in-time restore to any time in a transaction log backup containing a bulk-logged transaction).

What are the alternatives?

If you don't want the FULL recovery mode behavior, then don't use FULL recovery mode - switch to SIMPLE mode. But be aware that you lose the ability to restore to any point in between your full or differential backups.

If you want the FULL recovery mode behavior, but don't want to run out of log space - then the answer is again simple (pun intended , take log backups! If your log is growing too quickly, take more frequent log backups! If you don't have enough disk space to store the log backups, talk to your management about the options - buying more disks and continuing to use the FULL recovery options, or being forced to move to SIMPLE recovery mode.

One thing to bear in mind - you actually have to monitor the size of your log to tell whether its growing. That's what gets people into trouble in the first place - a combination of:

• Not knowing that the database is in FULL recovery mode
• Not tracking the size of the log

The problem comes for those people who just use an app and have no idea that the developer put the database (maybe for a web application log) into FULL recovery mode, or even that they have SQL Server on their system. For these people there is no good answer unfortunately apart from educating developers.

How to prevent it being used

If you're a sysadmin who wants to stop your database owners and backup operators from using the NO_LOG option, there is way to do it. Trace flag 3231 in SS2000 and SS2005 will turn the NO_LOG and TRUNCATE_ONLY options into no-ops in FULL/BULK_LOGGED recovery mode, and will clear the log in SIMPLE recovery mode. Trace flag 3031 in SS2005 turns them in checkpoints in all recovery modes. These trace flags are undocumented but they're allowed to be publicized.

This way no-one can truncate the log unless you allow them to, and when they come to you to complain, you can educate them!

As I mentioned in a previous post, one of the new features for database mirroring in SQL Server 2008 is log stream compression. There's a good explanation of what this is (along with some example workloads and performance graphs) on the SQL Customer Advisory Team blog so I'm not going to duplicate all that here.

Basically, the way mirroring works is by shipping all the transaction log from the principal to the mirror, and then replaying the log records on the mirror database. Network bandwidth can be a bottleneck for mirrored databases where the rate of transaction log generation is high enough to saturate the network. On synchronously mirrored databases (where a transaction cannot commit on the principal until all the transaction log for it has been hardened in the log of the mirror database), this can even lead to workload throughput degradation.

Log compression is a way to alleviate this problem. SS2008 Books Online states that compression rates of at least 12.5% are achieved - obviously the compression ratio could be much higher than that and is dependant on what's being compressed - i.e. the data that is being processed by the application. One of the tests they did for the post above shows a 5x compression ratio - that's pretty good.

The downside of this is that compression is not free - extra CPU must be used. The obvious CPU load increases comes from having to compress the transaction log on the principal and then decompress it again on the mirror before hardening it in the log. The non-obvious increase will come because the principal and mirror should end up being able to process more transactions/second, which means more CPU is needed.

This extra CPU could be a problem for some systems that are already pegged in terms of CPU load, and so they may actually see a drop in performance when log stream compression is enabled. Now, it's on by default when you upgrade to SS2008 but there is a way to turn it off. The blog post above divulges that there is a trace flag, 1462, that turns off log stream compression and effectively reverts the behavior back to SS2005.

Apart from the obvious benefits of this feature for systems that can afford the extra CPU, I'm excited because it may allow more mirrored databases to perform effective database maintenance - in the form of fragmentation control and index maintenance. When database mirroring is enabled, the database has to be running in the full recovery mode. This means that operations such as index rebuilds are fully logged - that's a lot of extra log being generated if you're used to switching to bulk-logged recovery mode to perform index operations. Of course, this isn't an issue for reorganizing an index (with ALTER INDEX ... REORGANIZE) as this has been fully logged (and has to be that way because of how it works) since I wrote the old DBCC INDEXDEFRAG for SS2000.

Anyway, for some customers the fact that these two operations are fully logged means that running them produces so much additional log that, in conjunction with the log from the workload itself, the network becomes a bottleneck for mirroring and it slows down both the maintenance and reduces the workload throughput. The addition of log stream compression means that for these systems, the log may be compressed enough so that the network is no longer a bottleneck and more regular fragmentation control can take place. This in turn will increase workload throughput as well - a double benefit!