Category: Database

  • 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

    Your transaction log is an AI pipeline’s raw material

    Everything on this list still applies. What is new is who is reading the log: Change Data Capture scans this same transaction log to feed real-time pipelines — including the embedding and RAG pipelines that need fresh data. Keep the log healthy (right-sized, right recovery model, no VLF sprawl) and your downstream AI stays fresh. On the retrieval side: The 9 Types of RAG, Explained

  • 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.
*/

  • Table Hints – NoLock vs ReadPast

    When any data in a database is read or modified, the database engine uses special type of mechanism, called locks, to maintain integrity in the database. Locks will be used to make sure the transaction consistency.

    NoLock Table Hint
    – Will allow you to read the uncommited data
    – Only used with SELECT statement
    – Blocking will not occur
    – Will reduce the concurrency and improve the performance at some extent
    – Risk of doing Phantom reads

    Let’s create table for the NOLOCK and READPAST hint demo

    create table tranDemo
(
			id int identity(1,1),
			name varchar(10)
)

insert into tranDemo values ('Jugal')
insert into tranDemo values ('Nehal')

    –Now let’s update the values by specifying the explicit transaction and don’t commit/rollback the transaction

    begin transaction
   update tranDemo
   set name = 'DJ'
   where name = 'Jugal'

    Now open new query window and execute the below query and you will notice query will not return data and will continue running as it is blocked

    select * from trandemo

    Now open new query window and execute the below query to check the blocking, you can see the blocking SPID in the result set

    sp_who2 active

    Now open new query window and execute the below query using NOLOCK hint and it will return data, yet transaction is not committed but it will return the updated value.

    select * from trandemo(NOLOCK)

    READPast Table Hint: Less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.
    – Will only read the commited rows which are not locked
    – Blocking will not occur
    – Only used with SELECT statement
    – Will reduce the concurrency and improve the performance at some extent
    – Result set returned by this hint is not perfect as it will not retun the locked rows or pages, so you can not make any decision based on data

    Now open new query window and run the below query it will return only one record (“Nehal”) which is not locked or modified

    select * from trandemo(ReadPast)

  • How to make sure that view metadata is updated?

    Problem: How to make sure that view metadata is updated?
    Solution: You can use sp_refreshview system stored procedure to make sure that view meta data is updated. See below example for more information.

    --creating demo table
create table demo
(
			id int identity (100,1),
			name varchar(10)
)

-- inserting sample values
insert into demo values ('Jugal'), ('Naresh')

-- creating view on table demo
create view vw_DEMO
as
select * from demo

-- select data from view
select * from vw_DEMO

-- adding new column to base table
alter table demo
add City varchar(10) null


-- select data from view, it is still returning ID and Name column
select * from vw_DEMO

-- Updating view sp_refreshview system stored procedure
sp_refreshview vw_Demo

-- selecting data again from view and it is returning all the columns
select * from vw_DEMO

    You can create below procedure to refresh all the views on the database.

    create procedure proc_RefreshViews
as
begin
    declare @vwName varchar (2000)

    declare objCursor cursor for
    select name from sys.objects where type = 'V'
    
    OPEN objCursor

    FETCH NEXT from objCursor INTO @vwName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    print  'Starting refresh of ' + @vwName
    exec ('sp_refreshview ''' + @vwName + '''')
    print  'completed refresh of ' + @vwName
    print  '--------------------------------'
    FETCH NEXT from objCursor INTO @vwName
    END

    CLOSE objCursor
    DEALLOCATE objCursor
end