Category: SQL Server

  • Script to get data file size, used space and free space

    While troubleshooting the disk space issue, you can use below script to check the data file size, used space and free size.

    select 
		DBName,
		name,
		[filename],
		size as 'Size(MB)',
		usedspace as 'UsedSpace(MB)',
		(size - usedspace) as 'AvailableFreeSpace(MB)'
from		
(	
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo

  • 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

  • Wish You All Very Happy New Year 2014

    HNY

  • LiteSpeed – Restore Script

    Script to check the backup file – data and log file information

    exec master.dbo.xp_restore_filelistonly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

    Script to check the backup file header information

    exec master.dbo.xp_restore_headeronly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

    Script to verify the backup file if the backup file is valid or not

    EXEC master.dbo.xp_restore_verifyonly @filename ='<BackupFilePath>\<BackupFileName.bak>'

    Script to restore database using Full backup with the default options

    exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>'

    Script to restore database using Full backup with file move option

    exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

    Script to restore database using Full backup with replace option

    exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

    Script to restore Full backup with no recovery

    exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"',
@with='NORECOVERY'

    Script to restore log backup with no recovery

    EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='NORecovery'

    Script to restore log backup with recovery

    EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery'

    Script to do point in time recovery

    EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery',
@with = 'STOPBEFOREMARK = <LogMark>'

  • LiteSpeed – Backup Scripts

    full or complete database backup

    execute master.dbo.xp_backup_database
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bak',
  @init = 1,
  @compressionlevel = 4

    differential backup

     
 execute master.dbo.xp_backup_database
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bak',
  @init = 1,
  @compressionlevel = 4,
  @with =  differential

    transaction log backup

     
 execute master.dbo.xp_backup_log
  @database = '<database name>',
  @filename = '<path>\<backup file name>.trn',
  @init = 1,
  @compressionlevel = 4

    filegroup backup

       
 execute master.dbo.xp_backup_database 
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bck',
  @init = 1,
  @compressionlevel = 4,
  @filegroup = 'filegroupname'