tairways handbook SQL Server Transaction Log Management By Tony Davis and Gail Shaw SQL Server Transaction Log Management By Tony Davis and Gail Shaw First published by Simple Talk Publishing October 2012 Copyright Tony Davis and Gail Shaw 2012 ISBN: 978-1-906434-94-6 The right of Tony Davis and Gail Shaw to be identified as the authors of this work has been asserted by them in accordance with the Copyright, Designs and Patents Act 1988. All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written consent of the publisher. Any person who does any unauthorized act in relation to this publication may be liable to criminal prosecution and civil claims for damages. This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent, re-sold, hired out, or otherwise circulated without the publisher's prior consent in any form other than which it is published and without a similar condition including this condition being imposed on the subsequent publisher. Technical Review and Edit: Kalen Delaney Additional Material: Jonathan Kehayias and Shawn McGehee Cover Image by Andy Martin Typeset by Peter Woodhouse and Gower Associates Table of Contents Introduction 11 ___________________________________________ Chapter 1: Meet the Transaction Log 15 ______________________ How SQL Server Uses the Transaction Log 15 ________________________________ Write Ahead Logging and Transactional Consistency 16 _______________________ Transaction Log Backup and Restore 18 _____________________________________ Controlling the Size of the Log 20 _________________________________________ A Brief Example of Backing up the Transaction Log 22 ________________________ Summary 26 ___________________________________________________________ Chapter 2: Some, But Not Too Much, Log Internals 27 __________ Virtual Log Files and the Active Log 27 ______________________________________ Log Truncation and Space Reuse 31 ________________________________________ A Peek at How SQL Server Grows the Log 33 _________________________________ Summary 42 ___________________________________________________________ Chapter 3: Transaction Logs, Backup and Recovery 43 __________ The Importance of Backups 43 _____________________________________________ What Backups Do I Need to Take? 45 _______________________________________ Database Recovery Models 47 ______________________________________________ Choosing the right recovery model 48 ____________________________________ Setting the recovery model 49 __________________________________________ Discovering the recovery model 50 _______________________________________ Switching models 53 ___________________________________________________ Log Backup Logistics 54 __________________________________________________ Frequency of log backups 54 ____________________________________________ Preserving the log chain 55 _____________________________________________ Storing log backups 56 _________________________________________________ Automating and verifying backups 57 _____________________________________ Summary 58 ____________________________________________________________ Chapter 4: Managing the Log in SIMPLE Recovery Model 59 _____ Working in SIMPLE Recovery Model 59 _____________________________________ Pros and Cons of SIMPLE Recovery Model 61 ________________________________ Chapter 5: Managing the Log in FULL Recovery Model 62 ______ What Gets Logged? 62 ___________________________________________________ Basics of Log Backup 63 __________________________________________________ Are log backups being taken? 63 _________________________________________ How to back up the transaction log 64 ___________________________________ Tail log backups 65 ____________________________________________________ Performing Restore and Recovery 67 ________________________________________ Full restore to point of failure 68 ________________________________________ Restore to end of log backup 71 __________________________________________ Point-in-time restores 76 _______________________________________________ Tail log backups when the database is offline 84 _____________________________ Summary 85 ____________________________________________________________ Chapter 6: Managing the Log in BULK_LOGGED Recovery Model 87 ______________ Minimally Logged Operations 88 __________________________________________ Advantages of Minimal Logging and BULK_LOGGED Recovery 96 _____________ Implications of Minimally Logged Operations 100 ___________________________ Crash recovery 101 ____________________________________________________ Database restores 102 __________________________________________________ Log backup size 106 ___________________________________________________ Tail log backups 107 ___________________________________________________ Best Practices for Use of BULK_LOGGED 113 ________________________________ Summary 115 ___________________________________________________________ Chapter 7: Dealing with Excessive Log Growth 117 _____________ Sizing and Growing the Log 118 ____________________________________________ Diagnosing a Runaway Transaction Log 119 _________________________________ Excessive logging: index maintenance operations 120 ________________________ Lack of log space reuse 128 _____________________________________________ Other possible causes of log growth 140 ___________________________________ Handling a Transaction Log Full Error 144 __________________________________ Mismanagement or What Not To Do 147 ____________________________________ Detach database, delete log file 147 _______________________________________ Forcing log file truncation 148 ___________________________________________ Scheduled shrinking of the transaction log 149 _____________________________ Proper Log Management 150 ______________________________________________ Summary 151 ___________________________________________________________ Chapter 8: Optimizing Log Throughput 152 ___________________ Physical Architecture 152 _________________________________________________ You only need one log file 153 ___________________________________________ Use a dedicated drive/array for the log file 158 ______________________________ Use RAID 10 for log drives, if possible 159 _________________________________ Log Fragmentation and Operations that Read the Log 161 _____________________ Effect on log backups 164 _______________________________________________ Effect on crash recovery 170 ____________________________________________ Correct Log Sizing 177 ___________________________________________________ What To Do If Things Go Wrong 180 _______________________________________ Summary 184 ___________________________________________________________ Further Reading 185 _____________________________________________________ Acknowledgements 185 ___________________________________________________ Chapter 9: Monitoring the Transaction Log 186 _______________ Monitoring Tools 187 ____________________________________________________ Windows Perfmon 187 _________________________________________________ Red Gate SQL Monitor 193 _____________________________________________ Dynamic Management Views and Functions 194 ______________________________ Using sys.dm_db_log_space_usage (SQL Server 2012 only) 195 ________________ Using sys.dm_io_virtual_file_stats 195 ____________________________________ Using sys.dm_os_performance_counters 200 _____________________________ T-SQL and PowerShell Scripting 201 ________________________________________ T-SQL and SSIS 202 ___________________________________________________ PowerShell 202 _______________________________________________________ Summary 207 __________________________________________________________ Further Reading 207 ____________________________________________________ Acknowledgements 208 __________________________________________________ About the Authors Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form written wisdom at either his Simple-Talk.com blog (http://www.simple-talk.com/blogs/author/2130-tony-davis/) or his SQLServerCentral.com author page (http://www.sqlservercentral.com/Authors/ Articles/Tony_Davis/295097/). As the editor behind most of the SQL Server-related books published by Red Gate (see http://www.simple-talk.com/books/), he spends much of his time helping others express what they know about SQL Server in as clear and concise a manner as possible. This is his first step into the relative limelight of book authoring. It's been emotional. Tony was the author of Chapters 1–5, and Chapter 9, co-author of Chapter 8, and contributed some additional material to Chapters 6 and 7. Gail Shaw is a senior consultant with Xpertease and is based in Johannesburg, South Africa. She specializes in database performance tuning and database recovery, with a particular focus on topics such as indexing strategies, execution plans, and writing T-SQL code that performs well and scales gracefully. Gail is a Microsoft Certified Master for SQL Server 2008 and a SQL Server MVP. She is a frequent poster on the SQL Server Central forum, writes articles for both SQLServer- Central.com and Simple-Talk.com and blogs on all things relating to database perfor- mance on her blog at http://sqlinthewild.co.za. She has spoken at TechEd Africa, the 24 Hours of PASS web event and, on multiple occasions, at the PASS Community summit. Gail is an Aikido Shodan (1st degree black belt), an avid archer and, in her copious spare time, is pursuing a Master's degree in Computer Science at the University of South Africa. Gail was the author of Chapters 6 and 7, co-author of Chapter 8, and contributed additional material in various places throughout the book. viii About the Technical Reviewer Kalen Delaney has been working with SQL Server for 25 years, and provides performance consulting services as well as advanced SQL Server training to clients around the world, using her own custom-developed curriculum. She has been a SQL Server MVP since 1993 and has been writing about SQL Server for almost as long. Kalen has spoken at dozens of technical conferences, including every US PASS conference since the organization's founding in 1999. Kalen is a contributing editor and columnist for SQL Server Magazine and the author or co-author of several of the most deeply technical books on SQL Server, including SQL Server 2008 Internals and the upcoming SQL Server 2012 Internals, both from Microsoft Press. Kalen blogs at www.sqlblog.com, and her personal website and schedule can be found at www.SQLServerInternals.com. ix Acknowledgements Tony Davis would like to thank: • Gail Shaw. It's been a pleasure to work with Gail on this book. She was tireless in keeping me honest and accurate, and commendably patient in helping me understand some of the finer points of transaction log mechanics. • Kalen Delaney. It was both reassuring and slightly intimidating to have Kalen edit and review my efforts, given how much she knows about this topic, and SQL Server in general. Her efforts have undoubtedly made this a far better book than it would have been otherwise. • All of the people who give their time to contribute to the SQL Server community knowledge base, in the form of books, articles, blogs and forum posts, as well as technical presentations at events such as SQL Saturday and the PASS Summit. I've worked with many of these people over the years, and learned from all of them. • Sarah, my wife. For her tolerance and patience during the trials and tribulations of writing this book, and for far too many other things to mention here. x