Fed up with BACKUP success messages bloating your error logs?

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 and he’s planning to document this (and other) trace flags in this area starting in SQL Server 2008.

The trace flag stops successful backups from printing messages, so you only see messages when a backup fails. Backups always print failure messages when they fail, so this trace flag is safe to enable.

You can enable it using DBCC TRACEON (3226, -1), where the -1 turns on the trace flag for all connections without having to restart SQL Server, and you can make it a startup trace flag too.

I strongly recommend that everyone enables this trace flag – there are no downsides.

Excellent!

Debunking a couple of myths around full database backups

I’ve been involved in a few conversations today that have highlighted some big misconceptions about how backups work. I’d like to use this blog post to debunk them. I checked everything I say here with my friend Steve Schmidt, the developer on the Storage Engine team responsible for BACKUP/RESTORE for the last ten years.

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.

Just how long should you make character fields? What’s the longest word?

Ok – this post is a little strange and fun. I was thinking about word length and how it relates to designing software/schemas to support multiple-languages. How far do you have to go in your research to figure out the maximum string length to support? So I started digging about and found some interesting things about words. Here are some examples.



  • If you’re putting together a schema to support hospital patient records, you might have a field for disease name. In that case, you’d have to allow for pnuemonoultramicroscopicsilicovolcanoconiosis which has 45 letters (caused by breathing in siliceous volcanic dust). A field for surgical procedure would have to support hepaticocholangiocholecystenterostomies which has 37 letters (creating a connection between the gall bladder and the hepatic duct). What about a field for how a measurement was obtained – electroencephalographically with 27 letters (using an electroencephalograph).
  • A schema to support chemical names could really be unlimited given the nature of systematic names for chemicals. The longest one in the dictionary is an acid called tetramethyldiaminobenzhydrylphosphinous with 39 letters (and given a few minutes I could probably draw its chemical structure by following the systematic method I learned at school :-)). The longest published chemical name is a kind of tobacco mosaic virus – ACETYLACETYL-SERYL-TYROSYL-SERYL-ISO-LEUCYL-THREONYL-SERYL-PROLYL-SERYL-GLUTAMINYL-PHENYL-ALANYL-VALYL-PHENYL-ALANYL-LEUCYL-SERYL-SERYL-VALYL-TRYPTOPHYL-ALANYL-ASPARTYL-PROLYL-ISOLEUCYL-GLUTAMYL-LEUCYL-LEUCYL-ASPARAGINYL-VALYL-CYSTEINYL-THREONYL-SERYL-SERYL-LEUCYL-GLYCYL-ASPARAGINYL-GLUTAMINYL-PHENYL-ALANYL-GLUTAMINYL-THREONYL-GLUTAMINYL-GLUTAMINYL-ALANYL-ARGINYL-THREONYL-THREONYL-GLUTAMINYL-VALYL-GLUTAMINYL-GLUTAMINYL-PHENYL-ALANYL-SERYL-GLUTAMINYL-VALYL-TRYPTOPHYL-LYSYL-PROLYL-PHENYL-ALANYL-PROLYL-GLUTAMINYL-SERYL-THREONYL-VALYL-ARGINYL-PHENYL-ALANYL-PROLYL-GLYCYL-ASPARTYL-VALYL-TYROSYL-LYSYL-VALYL-TYROSYL-ARGINYL-TYROSYL-ASPARAGINYL-ALANYL-VALYL-LEUCYL-ASPARTYL-PROLYL-LEUCYL-ISOLEUCYL-THREONYL-ALANYL-LEUCYL-LEUCYL-GLYCYL-THREONYL-PHENYL-ALANYL-ASPARTYL-THREONYL-ARGINYL-ASPARAGINYL-ARGINYL-ISOLEUCYL-ISOLEUCYL-GLUTAMYL-VALYL-GLUTAMYL-ASPARAGINYL-GLUTAMINYL-GLUTAMINYL-SERYL-PROLYL-THREONYL-THREONYL-ALANYL-GLUTAMYL-THREONYL-LEUCYL-ASPARTYL-ALANYL-THREONYL-ARGINYL-ARGINYL-VALYL-ASPARTYL-ASPARTYL-ALANYL-THREONYL-VALYL-ALANYL-ISOLEUCYL-ARGINYL-SERYL-ALANYL-ASPARAGINYL-ISOLEUCYL-ASPARAGINYL-LEUCYL-VALYL-ASPARAGINYL-GLUTAMYL-LEUCYL-VALYL-ARGINYL-GLYCYL-THREONYL-GLYCYL-LEUCYL-TYROSYL-ASPARAGINYL-GLUTAMINYL-ASPARAGINYL-THREONYL-PHENYL-ALANYL-GLUTAMYL-SERYL-METHIONYL-SERYL-GLYCYL-LEUCYL-VALYL-TRYPTOPHYL-THREONYL-SERYL-ALANYL-PROLYL-ALANYL-SERINE – with 1185 letters.
  • Probably the one that’s going to catch most people out is place names. The bank Kimberly and I use won’t allow a town/city name of more than 30 characters. That’s fine for the USA, where the longest place name has 24 letters (Winchester-on-the-Severn in Maryland or Washington-on-the-Brazos in Texas). However, if the back-end database is coded to only support 30 characters, that wouldn’t work around the world:


    • In Wales, there are two longest names are Llanfairpwllgwyngyllgogerychwyrndrobwyllllantysiliogogogoch with 58 letters and Gorsafawddachaidraigodanheddogleddolonpenrhynareurdraethceredigion wth 66 letters.


    • In New Zealand, there’s a hill called Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu – 85 letters and that name used to be in general use.

Pretty interesting – or as my kids like to say supercalafragalisticexpialidocious! (34 letters :-))


I’d be interested to hear of longest words in other languages apart from English – please leave a comment. Thanks