Category: DB Notes

Database Articles

  • Database Backup WITH CHECKSUM

    Backup WITH CHECKSUM

    When WITH CHECKSUM option is used while taking backup, the backup process will verify each page for checksums and torn page. In case bad page checksum found, the backup will stop. Using backup checksums may affect workload and backup throughput.

    If you want to continue the backup, you have to write CONTINUE_AFTER_ERROR in backup WITH clause.
    In case backup completes successfully it means no bad checksums.

    Example

    backup database sqldba to disk = 'c:\jspace\sqldba1.bak'
with checksum

    You can also use the 3023 trace flag for the check sum option, when 3023 trace flag is on WITH CHECKSUM option will be applied all the backups by default.

    DBCC TRACEON(3023)
GO                              

backup database sqldba to disk = 'c:\jspace\sqldbatraceflag.bak'

DBCC TRACEOFF(3023)
GO

    Let’s check if the 3023 trace flag applied the page checksum to above backup command or not. Execute below query for it.

    RESTORE HEADERONLY
FROM DISK = 'c:\jspace\sqldbatraceflag.bak'
GO

    HasBackupCheckSums value is 1, it means that WITH CHECKSUM option applied to backup using 3023 trace flag.
    checksum

  • Different options to check object definition

    SP_HELPTEXT system stored procedure is mostly used to check the object definition like definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

    OBJECT_DEFINITION – is BUILT IN function returns the source text of the specified object. It returns the definition of check constraint, default constraint, stored procedure, function, rule and views.

    Option 1

    EXEC sp_helptext 'Procedure Name'

    Option 2

    select object_definition(object_id('procedure name'))
go

    Option 3

    select    [definition]
from    sys.sql_modules
where    object_id = object_id('procedure name')
go

  • T-SQL Script to check SQL Server Instance TCP/IP Port Number

    SQL Server Error Log

    -- Check the error log for port number
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO

    Executing DMV Script

    -- you must connect remotely using TCP/IP
SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

    For Dynamic Port Number
    — Read the registry for port number

    -- Execute below script if SQL Server is configured with dynamic port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

    For Static Port Number

    -- Execute below script if SQL Server is configured with static port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

  • Spinlocks lightweight synchronization primitives

    Spinlocks are lightweight synchronization primitives which are used to protect access to data structures. Spinlocks are used to access data structures for a very short period of time. When a thread attempting to acquire a spinlock and if it is unable to obtain access it executes in a loop periodically checking to determine if the resource is available instead of immediately yielding.

    After some period of time a thread waiting on a spinlock will yield before it is able to acquire the resource in order to allow other threads running on the same CPU to execute. This is known as a backoff.

    When a large number of threads are contending for access to a single spinlock and it can lead to performance problems

    You can use below DMV to get the SPIN LOCK information

    select * from sys.dm_os_spinlock_stats

    Examples
    FGCP_PRP_FILL
    OPT_IDX_STATS
    BUF_FREE_LIST

  • 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