O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

SQL Server Transaction Log Management

Book Description

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Table of Contents
  5. About the Authors
  6. Acknowledgements
  7. Introduction
  8. Chapter 1: Meet the Transaction Log
    1. How SQL Server Uses the Transaction Log
    2. Write ahead Logging and Transactional Consistency
    3. Transaction Log Backup and Restore
    4. Controlling the Size of the Log
    5. A Brief Example of Backing up the Transaction Log
    6. Summary
  9. Chapter 2: Some, but not too Much, Log Internals
    1. Virtual Log Files and the Active Log
    2. Log Truncation and Space Reuse
    3. A Peek at how SQL Server Grows the Log
    4. Summary
  10. Chapter 3: Transaction Logs, Backup and Recovery
    1. The Importance of Backups
    2. What Backups do I Need to Take?
    3. Database Recovery Models
      1. Choosing the Right Recovery Model
      2. Setting the Recovery Model
      3. Discovering the Recovery Model
      4. Switching Models
    4. Log Backup Logistics
      1. Frequency of Log Backups
      2. Preserving the Log Chain
      3. Storing Log Backups
      4. Automating and Verifying Backups
    5. Summary
  11. Chapter 4: Managing the Log in Simple Recovery Model
    1. Working in Simple Recovery Model
    2. Pros and Cons of Simple Recovery Model
  12. Chapter 5: Managing the Log in Full Recovery Model
    1. What Gets Logged?
    2. Basics of Log Backup
      1. Are Log Backups being Taken?
      2. How to Back up the Transaction Log
      3. Tail Log Backups
    3. Performing Restore and Recovery
      1. Full Restore to Point of Failure
      2. Restore to End of Log Backup
      3. Point-In-Time Restores
      4. Tail Log Backups when the Database is Offline
    4. Summary
  13. Chapter 6: Managing the Log in Bulk_Logged Recovery Model
    1. Minimally Logged Operations
    2. Advantages of Minimal Logging and Bulk_Logged Recovery
    3. Implications of Minimally Logged Operations
      1. Crash Recovery
      2. Database Restores
      3. Log Backup Size
      4. Tail Log Backups
    4. Best Practices for Use of Bulk_Logged
    5. Summary
  14. Chapter 7: Dealing with Excessive Log Growth
    1. Sizing and Growing the Log
    2. Diagnosing a Runaway Transaction Log
      1. Excessive Logging: Index Maintenance Operations
      2. Lack of Log Space Reuse
      3. Other Possible Causes of Log Growth
    3. Handling a Transaction Log Full Error
    4. Mismanagement or what not to do
      1. Detach Database, Delete Log File
      2. Forcing Log File Truncation
      3. Scheduled Shrinking of the Transaction Log
    5. Proper Log Management
    6. Summary
  15. Chapter 8: Optimizing Log Throughput
    1. Physical Architecture
      1. You only Need One Log File
      2. Use a Dedicated Drive/Array for the Log File
      3. Use Raid 10 for Log Drives, if Possible
    2. Log Fragmentation and Operations that Read the Log
      1. Effect on Log Backups
      2. Effect on Crash Recovery
    3. Correct Log Sizing
    4. What to do if things Go Wrong
    5. Summary
    6. Further Reading
    7. Acknowledgements
  16. Chapter 9: Monitoring the Transaction Log
    1. Monitoring Tools
    2. Windows Perfmon
    3. Red Gate SQL Monitor
    4. Dynamic Management Views and Functions
    5. Using sys.dm_db_log_space_usage (Sql Server 2012 only)
    6. Using sys.dm_io_virtual_file_stats
    7. Using sys.dm_os_performance_counters
    8. T-Sql and PowerShell Scripting
    9. T-Sql and SSIS
    10. PowerShell
    11. Summary
    12. Further Reading
    13. Acknowledgements