Book Review – “SQL Server Transaction Log Management” by Tony Davis and Gail Shaw

A couple of questions and comments on the Ask.SQLServerCentral.com website have shown some misunderstanding about the use of SQL Server’s Transaction Log files. At about the same time, I was approached by Red Gate as part of their Friends programme to review their book “SQL Server Transaction Log Management“. Nice timing! Most of Red Gate’s books on SQL Server are available as free PDFs; unfortunately, this isn’t (yet) the case for this book…

And so, on with the review.

Chapter 1: Meet the Transaction Log

This first chapter covers a lot of ground – including brief mention of the important point that, although it is possible to have multiple physical log files, because SQL Server writes them sequentially rather than a page here and a page there, like it does with the data files,

…there is no advantage to having multiple files from the perspective of log throughput.

That point is made so early on, in fact, that it’s possible that the average skim-reader might miss it. And that’s a key factor with this book – there’s a lot of information in a relatively short (200-ish) page book, and this first chapter makes use of forward references. So go carefully.

Chapter 1 also covers how the transaction log is used by SQL Server (remember, it’s not just for data manipulation changes, but for data definition changes, such as index rebuilds), how to back it up & restore it, how to manage the size of the log file and how SQL Server organizes the log with VLFs.

Chapter 2: Some, But Not Too Much, Log Internals

Not too much? Let’s see… here we have discussion on VLFs (Virtual Log Files, the sections of your TLog file), LSNs (Logical Sequence Numbers), and how your transaction log backup uses these to backup the appropriate sections of the transaction log.

There’s a useful script that walks you through how these concepts hang together, making (as expected) liberal use of DBCC Loginfo to show the VLF information. This then leads onto a description of Log Fragmentation caused by inappropriate autogrowth settings.

Chapter 3: Transaction Logs, Backup and Recovery

At last! Discussion of backup/recovery models and how they affect the transaction log, including a reminder that a “full” backup doesn’t include the information from the logs…

It’s nice to see that, as well as pimping their own books and tools, they also discuss Ola Hallengren’s most excellent SQL Server maintenance scripts – and fail to mention that Ola has written these scripts in such a way that they can take backups using some third-party backup tools such as Red Gate’s SQL Backup. Another good thing about Ola’s scripts? They’re free.

Chapter 4: Managing the Log in SIMPLE Recovery Model

A very short chapter! Just remember, a database not in SIMPLE recovery is treated as such until the first FULL database backup is taken. Or, to look at it another way, you can’t do T-Log backups until a FULL backup has been made.

Chapter 5: Managing the Log in FULL Recovery Model

So this is the big one. What gets logged, minimally logged operations, Log backups (including tail log backups). And on to restore and recovery options, including point-in-time restores. Lots of scripts to show the various options.

Chapter 6: Managing the Log in BULK LOGGED Recovery Model

Amusingly (to my mind) this chapter is longer than the FULL chapter that went before… Which is entirely appropriate.

The chapter is a discussion of the BULK LOGGED recovery model, its limitations, the risks associated with it, and a summary the situations where it can be advantageous – a useful overview. There are comparisons between FULL and BULK LOGGED database logs, neatly scripted and explained. The Advantages / Disadvantages are further explained particularly with reference to crash/restart recovery and database restores. The chapter also contains a section on the best practices for use of the BULK_LOGGED recovery model (complete with a useful scenario to see how it works).

Chapter 7: Dealing with Excessive Log Growth

This is the one that crops up regularly on the Q&A sites such as Ask.SQLServerCentral. As well as looking at how to deal with the problem, there’s a discussion on how to size the log file in the first place – this may be something that needs to be revisited as your database usage changes.

There’s a major section on “Runaway Transaction Logs”, looking at several of the possible causes and how to mitigate these problems, such as switching to BULK_LOGGED mode when doing Index rebuilds. There’s also a discussion on how to investigate expensive / log-heavy transactions, complete with handy scripts to help identify these.

…the most common cause of a full or large transaction log, namely operating a database in the FULL recovery model (or less common, the BULK_LOGGED recovery model), without taking transaction log backups

Ah, if only I had a penny for every time I’ve seen that problem in the Q&A forums… Wait. I still wouldn’t have a fiver.

And why does this happen? As discussed in the book, because (generally), the model database (that acts as a template for all databases created on the server) is, by default, in FULL recovery model, and the inexperienced DBA hasn’t realised that the FULL backup only deals with data, and not transaction logs…

Other transactional options (long-running / active transactions, uncommitted transactions) are discussed at length, and there’s shorter discussion of some of the other possible causes (replication, active backups, mirroring), before moving into how to handle a full TLog that shows the wrong ways (some of which hadn’t occurred to me) & the right way, or “Proper Log Management”.

Chapter 8: Optimizing Log Throughput

This chapter concentrates on the architecture of your system to make sure that your transaction logs are running nice and quickly, and a part of that is making sure that the size and growth settings are properly configured. So there’s a discussion on this, on VLFs, Instant File Initialisation (tip: TLog files don’t do IFI), sizing, recovery / undo timings etc.

Chapter 9: Monitoring the Transaction Log

A quick look at keeping an eye on your T-Log using monitoring tools usch as PerfMon or Red Gate’s SQL Monitor, DMOs, T-SQL & PoSh scripts.

That’s all, folks

A lot of information is presented in this compact book, and there are plenty of scripts, and links to further articles to read for deeper knowledge.

RESTORE failure – error 3633… ‘DeleteTree’ … ‘fulltext.cpp’

Here’s a little bit of the pain that I’m facing today.

I was trying a SQL Server RESTORE (using Quest’s Litespeed for SQL Server) of one of our databases, one that has a fulltext index. The restore was consistently failing with the following error message:

Msg 62301, Level 16, State 1, Line 0
SQL Server has returned a failure message to LiteSpeed for SQL Server which has prevented the operation from succeeding.
The following message is not a LiteSpeed for SQL Server message. Please refer to SQL Server books online or Microsoft technical support for a solution:
RESTORE DATABASE is terminating abnormally.
The file "sysft_CallLog_FullText" failed to initialize correctly. Examine the error logs for more details.

I had a look through the EventLogs, as suggested, and found this in the Application Log at the right time:

2013-04-04 08:57:45.85 spid53 Error: 3633, Severity: 16, State: 1.
2013-04-04 08:57:45.85 spid53 The operating system returned the error '32(error not found)' while attempting 'DeleteTree' on 'K:\MSSQL\FTData\CallLog_FullText\MssearchCatalogDir' at 'fulltext.cpp'(1747).

Running Sysinternals HANDLE to see what else was poking round that directory that would prevent it from being deleted gives the following:

Handle v3.5
Copyright (C) 1997-2012 Mark Russinovich
Sysinternals - http://www.sysinternals.com
pdrai.exe pid: 9208 type: File 3C8: K:\MSSQL\FTData\CallLog_FullText\MssearchCatalogDir

pdrai.exe? That’s a new one on me. Turns out that the support boys have installed PureDisk (from Symantec’s NetBackup division or acquisition – it’s easy to lose track), and the pdrai.exe program is the backup agent.

Now to have a word with them about this. There’s no way it should require a handle to be held for 6 hours (or more) to backup a mere 4MB of data. I may be some time.  I’ll let you know if I find out how to prevent this sort of thing.

VirtualBox, Windows Server 2012, BSODs

Those of you who are following me on Twitter may have noticed I’ve had a few BSODs (Blue Screens Of Death) during my recent fiddlings with Windows Server 2012 (both Core & GUI versions) under VirtualBox.

Kevin Chant (@kevchant) mentioned that he had had problems with the CTP version of Windows 2012 running with only one virtual processor (vCPU). I’d been using VirtualBox’s default settings – but hadn’t noticed that these virtual machines were, by default, only set up to use one vCPU.

I don’t know about you, but I’m always a bit suspicious about working from a template that has BSOD’d several times during the initial install & patch routine, so I scragged them and started over. This time I was careful to go into the appropriate part of the Settings dialog:

and set that to use 2 CPUs instead of the default 1. Seems to work; mind you, my laptop isn’t as happy with having multiple VMs running. Time for an upgrade?

I’ve now successfully built template VMs for Windows 2012 Server Standard Core & GUI versions with 2 vCPUs, and these have so far been stable. Yay. Onwards and upwards!

Windows Server 2012 Core – Server Configuration, Add/Remove Features, SysPrep

Recently, I started building a Windows 2008 R2 / SQL 2008 R2 environment as per Jonathan Kehayias’s (blog|@SQLPoolBoy) series of articles on “Building A Free Test Environment“, I found myself wondering about doing similar in Windows 2012, specifically in Windows 2012 Core.

So, I started up a new VM and installed Windows 2012 Server Standard Core. And I was, as expected, greeted by a *gasp* command line interface. Yes, another reason to start learning PowerShell! When building a template along the lines of those mentioned by Jonathan in the first post of his series, there are a few things to do:

Server Configuration in Windows Server Core

Handy tip: there’s a utility for Server Configuration called, erm, “sconfig“. Remember that command – “sconfig”. It’s very helpful. However, it doesn’t do everything for you.

Feature Management in Windows Server Core

See the TechNet pages on Installing Windows Server 2012 Core for full information; the useful bits are:

• get-windowsfeature – does what it says on the tin. Gives you a tree-like list of the windows features, with a [x] next to any that are installed:
  
  See that “Name” column? You’ll need that for adding and removing features.
• install-windowsfeature featurename – installs the feature named “featurename” and subfeatures. In this case, I’m installing featurenames “Multipath-IO”, “Failover-Clustering” and “NET-Framework-Features”
• uninstall-windowsfeature featurename – uninstalls the feature named “featurename” and sub-features.

Oh, and that reminds me:

Help with PowerShell

Need more help with PowerShell commandlets? Try running update-help – downloads a whole slew of updates and more detailed help files.

SysPrep

The SysPrep utility is in (by default) C:\Windows\System32\SysPrep.

Wrangling Conference Speakers

No, not PA systems, but people.

As you may recall, I was the speaker liaison for SQL Saturday 194, being hosted by those lovely SQL South West people. Things went pretty smoothly; however, I do have a few observations on things that need to be done to make the process smoother for the next time.

Conference PowerPoint Template, Logos etc

Do you have a standard PowerPoint Template? What about a conference Logo? What about a standard slide full of sponsor information, or Health & Safety-esque Housekeeping? If so, these should be made available early on, ideally as part of the notes that you send to the speakers to confirm that they have been successful in applying to speak at your conference. Or at least on a “resources” page that’s accessible to all speakers.

PowerPoint slides and other documents

Do you have an upload site for these? Do you even have a requirement for this?

Techie bits

It helps to know well in advance what the conference venue provides in terms of visual aids – ie Projectors. What video inputs / outputs / cables are provided? Again, if this is available early, then speakers can either ask for something special, or know what they need to bring in order to successfully connect. Ditto for power supplies for those coming from overseas.

T-Shirts

When asking “what size T-Shirt”, you never know what people are going to say. There’s one chap here who said “XL” – and he’s half the size I am! It’s an idea to send out, with the email requesting T-Shirt sizes, the sizing information from the supplier, so your delegates know what that supplier’s version of XL is supposed to be.

Hotel, Accommodation, Travel

Again, provide these details up front. Are you handling the hotel bookings for the speakers, or are they? If it’s up to the speakers, and there are special discount codes, provide these up front and in big letters so they don’t get lost? You’ll also need to provide details of public transport and local car parks.

Email format

Keep emails going to presenters well organised and clearly laid out. Don’t just send a slew of text – format it properly, use headings, make it clear what information is required back from them and by when, and what they need to do for themselves. I had one speaker email me in a panic because he couldn’t get a room at the hotel (he’d failed to quote the booking reference number); I had to chase a couple of others because they’d failed to notice something requiring their response.

On the day

Count them in.

Have an area for them to relax and prepare away from the throng of delegates. Make sure that this area has plenty of power, space to work, and a separate supply of tea & coffee.

Make sure that they have everything they need, know which room they’ll be speaking in. Have someone assigned to the room to deal with problems such as Audio/Visual, lack of pencils etc. Have that person also responsible for timekeeping in the room and for making sure that the room is kept tidy at the end of each session.

Disaster Planning

So, everything is prepared. However. What do you do about the speaker who falls ill a couple of days before they are due to present? Or, worse, the one who can’t get there on the day? We were fortunate, in that we could make use of our other speakers to fill in any gaps. And we were even more fortunate in finding that our guys were more than happy to do that if required.

 

Disclaimer

This is an on-going document, but is being put out quickly to help others. There are bound to be things I’ve forgotten to put in, and I’ll add them as & when I get round to it.

Restoring into a different environment? Security problem? Orphaned Users

Oh, man, it’s been a fun ten days or so. Mostly not of my doing, thankfully. However, one thing that was definitely down to me was a minor little hiccup with SQL Server security.

Y’see, when you RESTORE a database into an environment other than that from which you created the BACKUP, chances are you’re going to have some security issues. In this case, we found that the UAT version of our applications was throwing the following error message:

Cannot open database "foo" requested by the login. The login failed. Login failed for user 'bar'.

This is down to the way our non-production environments had been built – in a hurry, and without doing the SQL Logins necessarily in The Right Way.

So now, every time I rebuild this environment from Production (quarterly), I have to remember to go through checking for “orphaned users” – users which exist in the SQL Server database I’ve just restored, but not necessarily in the server itself. Or (more likely in my current situation) do exist at the server level, but with the wrong SID (security identifier – a nasty-looking string of hex garbage).

For extra fun, I was also fighting against inconsistent collations. A pox on non-existent standards. (Is that even possible?)

Identifying Orphaned Users

This is obviously enough of a routine problem that MS has kindly provided a stored procedure to do the job for us. Yay.

sp_change_users_login is your friend. Run this, with the @Action parameter (first) set to ‘report’, and you get a list of user IDs that exist within the current database but don’t exist with the same SID. Hurrah.

Capturing that output

DECLARE @OrphanedUsers TABLE (UserName sysname, UserSID varbinary(85))
INSERT INTO @OrphanedUsers
EXEC sp_change_users_login 'report'

Processing the list

Given that, in my case, I’m only interested in fixing those users who already exist in my environment, I can identify them by:

SELECT * FROM @OrphanedUsers
WHERE UserName IN (
    SELECT name FROM master.dbo.syslogins
    )

Fixing one orphaned user

The sp_change_users_login SP takes, as one option for the @Action parameter “Update_One“. This is what I’ll use to fix each individual item identified in the table above. However, as there are several users in the database for which I wish to do this, and I’m lazy and don’t want to have to type too much, I’ll do this:

DECLARE @FixUsers varchar(max)
SELECT @FixUsers = ''
SELECT @FixUsers = @FixUsers + 'exec sp_change_users_login ''Update_one'', ''' + UserName + ''', ''' + UserName + ''' '
FROM @OrphanedUsers
WHERE UserName IN (
    SELECT name FROM master.dbo.syslogins
    )

That gives me a variable, @FixUsers, that contains the right series of EXECUTE statements to fix those users that I need to fix. I can then lob this big lot of EXEC statements at an, erm, EXEC statement:

EXEC (@FixUsers)

And the job’s a good ‘un.

Doing this for all databases

In my case, though, this isn’t sufficient. I have multiple databases in this environment (30-odd), several of which suffer this embarrassing problem. Rather than go through each DB manually running this script, why not make use of the undocumented (and therefore highly dangerous) sp_MSforeachDB stored procedure? This does what it says on the tin – runs a command once for each database on the system, substituting the name of the database wherever you’ve got a question mark (?) in your script.

Given that I’m therefore having to build up dynamic SQL, the apostrophes can get a little out of hand. However, here’s the script I ended up writing and running, run through Red-Gate‘s “format-SQL” site.

sp_msforeachdb 
'USE [?]
DECLARE @OrphanedUsers TABLE
    (
      UserName SYSNAME ,
      UserSID VARBINARY(85)
    )
INSERT  INTO @OrphanedUsers
        EXEC sp_change_users_Login ''report''
IF EXISTS ( SELECT  *
            FROM    @OrphanedUsers ) 
    BEGIN
        DECLARE @FixMe NVARCHAR(MAX)
        SELECT  @FixMe = ''''
        SELECT  @FixMe = @FixMe + ''
    exec sp_change_users_login ''''update_one'''', '''''' + UserName + '''''' ,
                '''''' + UserName + '''''' ''
        FROM    @OrphanedUsers
        WHERE   UserName IN (
                SELECT  name 
                FROM    master.dbo.syslogins )
        PRINT @FixMe
        --EXEC (@FixMe)
    END
'

Uncomment the EXEC (@FixMe) line when you’re ready.

Now, I had collation issues, so the WHERE clause of that innermost SELECT statement had to be

...
        WHERE   UserName COLLATE SQL_Latin1_General_CP1_CI_AS IN (
                SELECT  name COLLATE SQL_Latin1_General_CP1_CI_AS
                FROM    master.dbo.syslogins )
...

Deep joy.

Caveat – sp_MSforeachDB

Now, a word of warning. sp_MSforeachDB is an undocumented stored procedure from Microsoft. Because it is undocumented, it is unsupported, and may not work, and may be changed (or dropped) at any point. You’re best off using something else, such as this more reliable and flexible replacement for sp_MSforeachDB from Aaron Bertrand (blog|twitter).

Still to do

Two things outstanding:

1. Document the proper procedure – if you’re impatient, just google “bcp out syslogins sql server” to get you started

2. write a version of this for SQL Server 2000 – yes, I know it’s ancient, but some of us still have to support it…

SQL Saturday 194 Exeter Pre-Con – PowerShell 3.0 for SQL Server Administrators and Developers

OK, Yes, it’s (yet) another post pimping a SQL Saturday 194 Exeter Pre-Con session. This time, André Kamman (blog|twitter)and his day of PowerShell.

I’ve seen André perform at a SQLBits conference, where he gave a session “RoboDBA” about PowerShell, and the joys of administering a SQL Server environment of several hundred instances. My entire collection of notes from the session is as follows:

Takeaways

• Learn PowerShell
• Build own environment to test / play, using:
  • HP MicroServer
  • MS TechNet
  • vmWare or MS HyperV
• Learn to use LogParser
• Do as much as possible with scripting rather than GUIs

“There are production DBAs who will be outsourced by PowerShell scripts” – AK

Helpful.

Fortunately, the video for his session *is* available on the link above.

His views on PowerShell have been instrumental in getting me started with learning it (albeit slowly). If you’re starting out, or still learning, you could do worse than start with a day with André!