aiDeeva.com

Category: SQL Server 2011 (Denali)

  • Best Practices SQL Server Transaction Log

    Background
    In SQL Server Database Recovery model will decide how the transaction log will be logged in transaction log file. Transaction log file extension is .LDF

    Full – Transaction log is fully logged (Can take log backup)
    Bulk Logged – Bulk transaction is minimally logged (Can take log backup)
    Simple – Transaction log will be truncated on checkpoint

    In transaction log file transactions are sequentially logged, every record in transaction log file is uniquely identified by log sequence number (LSN). LSN data type is Numeric (25,0)

    You can follow below best practices for the transaction log file

    1. Don’t create multiple log files : As transactions will be logged into log file sequential manner it would not help for data stripping across multiple files
    2. Keep the transaction log file on the separate drive
    3. Identify the RPO and RTO for the database and according to that choose the recovery model and correct log backup strategy
    4. RAID 1 + 0 is high recommended for transaction log
    5. AUTO SHRINK should be always off on the database
    6. Pre-allocate the space to transaction log file, it will improve the performance. Don’t depend on the auto growth option.
    7. Always set the values of Initial size, max size and growth property of the transaction log file
    8. Always set auto growth value, don’t set in percentage
    9. Transaction Log file internal fragmentation can also lead the performance and database recovery issue. Database should not have an excessive number of Virtual Log Files (VLFs) inside the Transaction Log. Having a large number of small VLFs can slow down the recovery process that a database goes through on startup or after restoring a backup. Make sure transaction log initial size and log growth defined well to avoid internal fragmentation
    10. External fragmentation can be removed by using disk defragmentation utility
    11. In case of Transaction log full, please use below query to check the cause of the log full and take the decision accordingly.

    SELECT name ,
    recovery_model_desc ,
    log_reuse_wait_desc
    FROM sys.databases
    WHERE name = @DatabaseName

  • Stripping SQL Server Database Backup to Multiple Files

    Stripping Database backup to multiple files and on different drives will make the backup speed faster and will reduce the backup duration.

    Check the below sample script for the backup and restore. You can perform the same task using SSMS GUI as well.
    Backup Script

    BACKUP DATABASE [SQLDBPool] TO  
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH NOFORMAT, 
NOINIT,  
NAME = N'SQLDBPool-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

    Restore Script

    RESTORE DATABASE [SQLDBPool] FROM  
DISK = N'C:\JSpace\Backup\SQLDBPool1.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool2.bak',  
DISK = N'C:\JSpace\Backup\SQLDBPool3.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10,replace
GO

  • Script to find out the traces running on SQL Server instance

    You can execute the below script on SQL Server instance to find out the traces, trace type, trace file path and trace status.

    select
      TraceType =
       case trace.is_default
            when 1 THEN 'Default/System Trace'
            when 0 THEN 'User Trace'
       end,
      Trace_Status =
      case trace.status
            when 1 THEN 'Running'
            when 0 THEN 'Stopped'
      end,
       ssion.session_id as SessionID,
       [loginName] = coalesce(ssion.login_name,ssion.login_name,'Reader SPID Not mentioned'),
       [Trace_File_Path] = coalesce(trace.[Path],trace.[Path],'OLEDB Client Trace')
      from sys.traces trace
            left join sys.dm_exec_sessions ssion on trace.reader_spid = ssion.session_id

  • How to attach a SQL Server database with a missing transaction log file?

    There may be situation where the transaction log file is corrupted or delete due to some issue, there is still hope to recover the database using data file. In the below script we will create the scenario and recover the database using data file only.

    -- create the below sample database
create database [SQLDBPool_5] 

-- execute the command to get the data log file location
sp_helpdb sqlhelpdesk

-- Copy the location of log file and data file from the output of the above command
-- C:\JSpace\sqldbpool_5_log.ldf, C:\JSpace\sqldbpool_5.mdf

-- Detatch the database
USE [master]
GO
ALTER DATABASE [SQLDBPool_5] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SQLDBPool_5'
GO

-- Delete the log file
xp_cmdshell 'del "c:\JSpace\SQLDBPool_5_log.ldf"'


-- attach the database using MDF file only SQL Server will create the log file its self
USE [master]
GO
CREATE DATABASE [SQLDBPool_5]  ON 
( FILENAME = N'C:\JSpace\sqldbpool_5.mdf' )
FOR ATTACH
GO

-- You will the below message on the execution of the above command
/*
File activation failure. The physical file name "c:\JSpace\SQLDBPool_5_log.ldf" may be incorrect.
New log file 'c:\JSpace\SQLDBPool_5_log.ldf' was created.
*/