Skip to content

SQL Server Data and Log Files

    What is a database file?

         When you create a database, two primary files are created by default: the data file and the transaction log file.  The primary purpose of the data file is to hold all the data, such tables, indexes, store procedures and other relevant data.  While the data file is simple to understand and requires some management, the transaction log file requires a greater attention and understanding.

    What are a transaction log file and its purpose?

    The primary function of the transaction log file is to:

    Record all changes to the database

    Record changes sequentially

    All data is written to the transaction log file first before committing the changes to the data file

    A triggering mechanism, called a checkpoint,  is triggered each few minutes to the transaction log to indicate that a particular transaction has been completely written from the buffer to the data file;  this process keeps flushes out the committed transaction, and maintains the size of the physical transaction log file (only in simple recovery mode)

     Key object needed to restore databases

    Controls the size of the transaction log file and prevents the log consuming the disk space

     Used in log shipping, database mirroring, and replication

    Allows recover to a point in time

    Reason the transaction log file is out of control in size

    Transaction log backups are not occurring while in Simple recovery mode

     Very long transactions are occurring, like indexes of table or many updates

    Demo to control the size of transaction log by doing log backups

    What are the recovery models and their roles?

    The recovery models in SQL Server, Simple, Full, Bulk-Logged, determine whether a transaction log backup is available or not

    With Simple recovery model

    Transaction log backups are not supported

    Truncation of the transaction log is done automatically, thereby releasing space to the system

    You can lose data, as there are no transaction log backups

    When in recovery mode, data in the T-Log will not grow

    With Bulk-logged recovery model

    Supports transaction log backups

    As in Full mode, there is no automated process of transaction log truncation

    Used primarily for bulk operations, such as bulk insert, thereby minimal

    Full recovery model

    Supports transaction log backups

    Little chance of data loss under the normal circumstances

    No automated process to truncate the log thus must have T-log backups

    The transaction log backups must be made regularly to mark unused space available for overwriting

    When using Full recovery mode, the T-log can get large in size

    During most backup processes, the changes contained in the logfile are sent to the backup file

    Scripts for Recovery Models:

    select [name], DATABASEPROPERTYEX([name],’recovery’)
    from sysdatabases
    where name not in (‘master’,’model’,’tempdb’,’msdb’)

    –Change the recovery mode:

    USE master;

    GO

    — Set recovery model to SIMPLE

    ALTER DATABASE Admin SET RECOVERY SIMPLE;

    GO

     — Set recovery model to FULL

    ALTER DATABASE Admin SET RECOVERY FULL;

    GO

    SCRIPTS TO CHECK THE TRANSACTION LOG

    –Demo that taking a full backup does not truncate the log file, but taking a transaction log file does truncate the log file

    –drop the current database

    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’ADMIN’

    GO

    USE [master]

    GO

    ALTER DATABASE [ADMIN] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    USE [master]

    DROP DATABASE [ADMIN]

    GO

    –Create a database for testing

      USE MASTER

      GO

      CREATE DATABASE ADMIN

      GO

    — Create a table and insert data from AdventureWorks2012.HumanResources.Employee

    USE ADMIN

    GO

    SELECT *

    INTO dbo.AAA

    FROM AdventureWorks2012.HumanResources.Employee

    SELECT * FROM AAA

    –Change the recovery mode to full so as to take transactional log backups

    USE MASTER;

    ALTER DATABASE ADMIN

    SET RECOVERY FULL;

    USE ADMIN

    GO

    — View the space used and allocated to transaction log

    DBCC SQLPERF (LOGSPACE);

    –Database Name        Log Size (MB)  Log Space Used (%)     Status

    –ADMIN                    0.5078125                 74.71154              0

    –Take a full backup of database

    BACKUP DATABASE admin

    TO DISK = ‘C:\FullBackups\admin.bak’;

    — Modify the database by updates, and deletes

    USE Admin

    GO

    UPDATE AAA

    SET MaritalStatus = ‘S’

    WHERE JobTitle = ‘Design Engineer’;

    DELETE AAA

    WHERE BusinessEntityID > 5;

    –take a full database backup to set it in full mode

    BACKUP DATABASE admin

    TO DISK = ‘C:\FullBackups\admin.bak’;

    –check the space used by log file after full database backup, notice the log space used had not reduced in size!

    DBCC SQLPERF (LOGSPACE);

    –Database Name        Log Size (MB)  Log Space Used (%)     Status

    –ADMIN                   0.8046875                  74.45388             0          

    –take a transaction log backup.  Note that the size of the log file space used is reduced, but not the actual size of the file

    –this is because, when you take a log backup, the inactive transactions are removed from the log file to the backup file!

    BACKUP log  admin

    TO DISK = ‘C:\FullBackups\admin.bak’

    DBCC SQLPERF (LOGSPACE);

    –Database Name        Log Size (MB)  Log Space Used (%)     Status

    –ADMIN                    0.8046875                 48.36165              0

    Viewing inside the SQL Server Database Transaction Log to prevent internal fragmentation

    (Auto growth and sizing of auto growth log)

         There is a function called (fn_dblog) which requires a beginning LSN and an ending LSN (Log Sequence Number) for a transaction, but for our purpose we will use NULL as starting and ending values. The following example will show how the transaction log records multiple rows of data for any activity against the database and how a transaction log backup truncates the transaction file, once it’s written to the backup log.  (fn_dblog)  A ways to view the contents of the log.  DONOT RUN THIS COMMAND IN PRODUCTION AS IT IS UNDOCUMENTED.

    USE [master];

    GO

    CREATE DATABASE VIEWLOG;

    GO

    — Create tables.

    USE VIEWLOG;

    GO

    CREATE TABLE Cities

    (City varchar (20));

    USE VIEWLOG;

    GO

    Backup database fnlog to disk = ‘C:\FullBackups\VIEWLOG.back’

    Insert into Cities values (‘NewYork’)

    Go 1000

    Select COUNT (*) from fn_dblog (null, null)

    Backup database fnlog to disk = ‘C:\FullBackups\VIEWLOG.back’

    Select COUNT (*) from fn_dblog (null, null)

    Backup log fnlog to disk = ‘C:\FullBackups\VIEWLOG.back’

    Select COUNT (*) from fn_dblog (null, null)

    Select * from fn_dblog (null, null)

    Options to truncate the log file:

    1. Backup the database.
    2. Detach the database,
    3. Delete the transaction log file. (or rename the file, just in case)
    4. Re-attach the database
    5. Shrink the database
    6. None of the above

    Create simple database script

    CREATE DATABASE [Admin]
    GO
    -- Find recovery mode for each database
    select [name], DATABASEPROPERTYEX([name],'recovery') As RecoveryMode
    from sysdatabases
    where name not in ('master','model','tempdb','msdb') --<< Excluding system databases.  (comment out the where clause to find recovery modes for system dbs)
    
    --Change the recovery mode for databases:
    USE master;
    GO
    -- Set recovery model to SIMPLE
    ALTER DATABASE Admin 
    SET RECOVERY SIMPLE;
    GO
     
    -- Set recovery model to FULL
    ALTER DATABASE Admin 
    SET RECOVERY FULL;
    GO