In SSWUG.ORG’s virtual class, you will learn how to manage and maintain your SQL Server transaction log, as the log is critical to optimizing operability and functionality. By the end of the six sessions, you will know why the transaction log is considered to be one of the most important components of a database, as it records all changes to your database, to allow recovery of transactions in the event of a crash.
Kalen Delaney, course instructor and celebrated author of SQL Server 2012 Internals, will start by examining how SQL Server manages the transaction log internally and looking at some undocumented commands for examining log usage and low growth. Then, Kalen will discuss what happens as the log grows and shrinks, exploring best practices for physical placement of your log file(s). She will also review various SQL Server operations to explore what gets written to the log when various operations are performed. Finally, Kalen will demonstrate how the log is processed during BACKUP and RESTORE commands, and how that processing changes in different recovery models.
Session 1 – Introduction to Log Internals –
Kalen will discuss basic information regarding the transaction log, including the way the log is organized and how it can be truncated. She will also delve into Visual Log Files (VLFs), explaining how they provide an easier way to handle internal management tasks in the transaction log.
Session 2 – Physical Log Management –
As of SQL Server 7, the transaction log is a physical file. Kalen will provide details on determining how large the file should be, as well as what happens when the log grows or shrinks.
Session 03 – Recovery and Restore –
Kalen will discuss details about recovering and restoring surrounding the transaction log. She will provide several recovery-related scenarios and explain the effects of Redo and Undo.
Session 04 – Backup Types –
From a full database backup to a file and filegroup differential backup, there are many options to choose from. Kalen will explain aspects of each backup type, as well as provide information concerning data and transaction log restoration.
Session 05 – The Logging Experience –
Kalen will get into the various recovery models – FULL, BULK_LOGGED and SIMPLE – and will explain how each type affects the transaction log’s behavior. She will also explain how to set, change and switch recovery model types.
Session 06 – What Gets Logged –
In the last section, Kalen will talk about ways to read the log, to understand bulk operations and to optimize TempDB. She will also recap the course, providing best practices and actionable information on the transaction log.
Kalen Delaney has been working with SQL Server for over 27 years. She has provided performance consulting services and taught advanced courses on SQL Server to thousands of professionals and organizations. Delaney began her career as a teacher and specialist for Sybase, then in 1992, became an independent trainer and consultant. As such, she worked with both the Microsoft and Sybase companies to develop courses and to do internal training for their technical support staff. She became a Microsoft SQL Server MVP in 1993.
She is the author of SQL Server Internals: In-memory OLTP, primary author of SQL Server 2012 Internals, Inside SQL Server 2005 as well as previous books on SQL Server Internals. Visit her website to find out more.