STOP! Your SQL Server Log File is Out of Control - The RIGHT Way to Manage It
Is your SQL log file consuming all your disk space? 😱 Learn the safe, automated way to manage transaction log growth without breaking your backup chain. Free script included! #SQLServer #LogManagement #DBA

Introduction: The DBA's Nightmare - The 500GB Log File That Ate Your Server
It's 3 AM. Your monitoring system alerts: "Disk C: at 99% capacity." You investigate and find a single  .ldf  file has ballooned to hundreds of gigabytes. Panic sets in. Do you shrink it? Will it break something? How did this happen?

If you've faced this scenario, you're not alone.  Transaction log management is one of the most misunderstood aspects of SQL Server administration. In this guide, we'll give you not just an automated solution, but more importantly, the  knowledge to use it safely and effectively.

Critical Disclaimer: Read This Before Proceeding
Shrinking log files is generally NOT a best practice.  It should be treated as an emergency procedure, not regular maintenance. Frequent shrinking leads to:

• Virtual Log File (VLF) fragmentation  - causing performance degradation
• Immediate regrowth  - the very problem you're trying to solve
• Potential data loss  if not handled correctly

The proper long-term solution is:

• Proper backup strategy  (Transaction log backups for FULL recovery)
• Right-sizing  your log file from the beginning
• Monitoring growth patterns

Use this script for  emergency situations only  or in  development environments.

The Emergency Log Management Script 

-- =====================================================-- Procedure: spLogClear - EMERGENCY Transaction Log Management-- Description: Use ONLY for emergency log file reduction.--              Not recommended for regular maintenance.-- Author: FreeLearning365.com-- Usage: EXEC [dbo].[spLogClear]-- =====================================================

ALTER PROCEDURE [dbo].[spLogClear]ASBEGIN
    SET NOCOUNT ON;

    DECLARE
        @DBName SYSNAME,
        @LogFileName SYSNAME,
        @StartTime DATETIME = GETDATE(),
        @Msg NVARCHAR(MAX),
        @ErrorMsg NVARCHAR(MAX),
        @SQL NVARCHAR(MAX),
        @OriginalRecoveryModel NVARCHAR(60),
        @CurrentRecoveryModel NVARCHAR(60);

    BEGIN TRY
        -- SECTION 1: INITIALIZATION & VALIDATION
        -- ===========================================

        -- Dynamically detect current database context
        SET @DBName = DB_NAME();

        -- Safety Check: Prevent execution on system databases
        IF @DBName IN ('master', 'model', 'msdb', 'tempdb')
        BEGIN
            SET @Msg = 'CRITICAL: This procedure cannot be run on system databases. Attempted on: ' + @DBName;
            RAISERROR(@Msg, 16, 1);
            RETURN;
        END

        -- Detect the logical name of the transaction log file
        SELECT TOP 1 @LogFileName = name
        FROM sys.database_files
        WHERE type_desc = 'LOG';

        -- Safety Check: Ensure log file was found
        IF @LogFileName IS NULL
        BEGIN
            RAISERROR('Could not identify transaction log file for database: %s', 16, 1, @DBName);
            RETURN;
        END

        -- SECTION 2: AUDITING & RECOVERY MODEL MANAGEMENT
        -- ==================================================

        -- Capture original recovery model for restoration
        SELECT @OriginalRecoveryModel = recovery_model_desc
        FROM sys.databases
        WHERE name = @DBName;

        -- Log process initiation
        INSERT INTO log.LogShrinkAudit
        (DBName, LogFileName, StartTime, Status, Message)
        VALUES
        (@DBName, @LogFileName, @StartTime, 'Started',
         'Emergency log shrink initiated. Original Recovery: ' + @OriginalRecoveryModel);

        PRINT 'Starting emergency log management for database: ' + @DBName;
        PRINT 'Log file name: ' + @LogFileName;
        PRINT 'Original recovery model: ' + @OriginalRecoveryModel;

        -- SECTION 3: THE CORE LOG MANAGEMENT PROCESS
        -- =============================================

        -- STEP 3.1: TEMPORARY RECOVERY MODEL SWITCH
        -- WARNING: This breaks the log backup chain in FULL recovery!
        SET @SQL = N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY SIMPLE;';
        EXEC(@SQL);
        PRINT 'Temporarily switched to SIMPLE recovery model.';

        -- STEP 3.2: CHECKPOINT - Flushes dirty pages to data file
        CHECKPOINT;
        PRINT 'Checkpoint completed.';

        -- STEP 3.3: SHRINK LOG FILE (THE MAIN EVENT)
        -- Parameter 0 = shrink to smallest possible size
        SET @SQL = N'DBCC SHRINKFILE (N''' + @LogFileName + N''', 0);';
        EXEC(@SQL);
        PRINT 'Log file shrink completed.';

        -- STEP 3.4: RIGHT-SIZE THE LOG FILE (CRITICAL STEP!)
        -- Prevents immediate autogrowth. Adjust 1000MB based on your needs.
        SET @SQL = N'ALTER DATABASE [' + @DBName + N']
                    MODIFY FILE (NAME = N''' + @LogFileName + N''',
                                SIZE = 1000MB,
                                MAXSIZE = UNLIMITED,
                                FILEGROWTH = 500MB);';
        EXEC(@SQL);
        PRINT 'Log file resized to prevent immediate regrowth.';

        -- STEP 3.5: RESTORE ORIGINAL RECOVERY MODEL
        IF @OriginalRecoveryModel = 'FULL'
        BEGIN
            SET @SQL = N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY FULL;';
            EXEC(@SQL);
            PRINT 'Recovery model restored to FULL.';

            -- CRITICAL: Take a FULL backup after restoring FULL recovery
            -- This establishes a new backup chain
            SET @Msg = 'IMPORTANT: Take a FULL database backup immediately to re-establish backup chain.';
            PRINT @Msg;
        END

        -- SECTION 4: SUCCESS REPORTING
        -- ===============================

        SET @Msg = N'Emergency log management completed successfully for database [' + @DBName +
                   N']. Process completed at ' + CONVERT(NVARCHAR(30), GETDATE(), 120);

        INSERT INTO log.LogShrinkAudit
        (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES
        (@DBName, @LogFileName, @StartTime, GETDATE(), 'Success', @Msg);

        PRINT @Msg;
        PRINT '=== PROCESS COMPLETED SUCCESSFULLY ===';

    END TRY

    BEGIN CATCH
        -- SECTION 5: COMPREHENSIVE ERROR HANDLING
        -- ==========================================

        SET @ErrorMsg = 'ERROR [' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + ']: ' +
                        ERROR_MESSAGE() + ' (Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)) + ')';

        PRINT 'PROCESS FAILED: ' + @ErrorMsg;

        -- Attempt to restore original recovery model on failure
        BEGIN TRY
            IF @OriginalRecoveryModel IS NOT NULL AND @OriginalRecoveryModel != 'SIMPLE'
            BEGIN
                SET @SQL = N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY ' + @OriginalRecoveryModel + N';';
                EXEC(@SQL);
                PRINT 'Original recovery model restored after failure.';
            END
        END TRY
        BEGIN CATCH
            PRINT 'WARNING: Could not restore original recovery model after failure.';
        END CATCH

        -- Log the failure
        INSERT INTO log.LogShrinkAudit
        (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES
        (@DBName, ISNULL(@LogFileName, 'Unknown'), @StartTime, GETDATE(), 'Failed', @ErrorMsg);

        -- Re-throw error for external handling (e.g., SQL Agent)
        THROW;

    END CATCH
END
GO

Step-by-Step Implementation Guide
Step 1. Prerequisites & Safety Checks
Create the Audit Table:

CREATE SCHEMA [log];
GO

CREATE TABLE [log].[LogShrinkAudit](
    [AuditID] [int] IDENTITY(1,1) NOT NULL,
    [DBName] [sysname] NOT NULL,
    [LogFileName] [sysname] NOT NULL,
    [StartTime] [datetime] NULL,
    [EndTime] [datetime] NULL,
    [Status] [nvarchar](50) NULL,
    [Message] [nvarchar](max) NULL)

Permissions Required:

• ALTER DATABASE  permission on the target database
• INSERT  permission on the audit table
• Membership in  db_owner  role is typically required

Step 2. Initial Testing (SAFE MODE)
NEVER run this in production without testing first!

-- TEST 1: Check what would happen (read-only check)-- Examine current log size and VLFsSELECT
    name AS [LogFileName],
    size * 8.0 / 1024 AS [SizeMB],
    CASE WHEN size = max_size THEN 'FULL' ELSE 'GROWTH AVAILABLE' END AS [Status]FROM sys.database_files
WHERE type_desc = 'LOG';

-- Check VLF count (high count = fragmentation)DBCC LOGINFO;

Step 3. Emergency Execution

Only proceed if you have a genuine emergency and understand the risks:
-- Execute the emergency procedureEXEC [dbo].[spLogClear];
-- Monitor the audit logSELECT * FROM [log].[LogShrinkAudit] ORDER BY StartTime DESC;
-- RITICAL POST-PROCESS STEP: Take a FULL backupBACKUP DATABASE [YourDatabase]
TO DISK = 'D:\Backups\PostShrink_FullBackup.bak'WITH COMPRESSION, INIT;

Pros, Cons, and Best Practices 

The RIGHT Way: Long-Term Log Management Strategy

Instead of regular shrinking, implement this

For FULL Recovery Model Databases
-- Schedule frequent transaction log backupsBACKUP LOG [YourDatabase]
TO DISK = 'D:\LogBackups\YourDatabase_Log.trn'WITH COMPRESSION;

    Right-Size from Beginning

-- Set appropriate initial size and growthALTER DATABASE [YourDatabase]MODIFY FILE (NAME = YourDatabase_Log,
         SIZE = 4096MB,
         FILEGROWTH = 1024MB); -- Fixed growth, not percentage

    Monitoring & Alerting

-- Regular monitoring querySELECT
    name AS DatabaseName,
    (size * 8.0 / 1024) AS LogSizeMB,
    (CAST(used_space AS float) * 8 / 1024) AS UsedSpaceMB,
    (size * 8.0 / 1024) - (CAST(used_space AS float) * 8 / 1024) AS FreeSpaceMB
FROM sys.dm_db_log_space_usage
CROSS APPLY sys.databases
WHERE sys.databases.database_id = sys.dm_db_log_space_usage.database_id;

Business Case & Limitations

Business Case:  Prevents production outages due to disk space exhaustion. Maintains system availability during unexpected log growth scenarios.

Limitations

• Temporary solution  - doesn't address root cause
• Performance impact  - VLF fragmentation affects write performance
• Recovery implications  - breaks the point-in-time recovery capability until a new full backup is taken
• Not a substitute  for a proper backup strategy

"The truth your DBA won't tell you about log files"
"From panic to solution in 5 minutes"
"The emergency fix that saved our production server"
"Why your log file keeps growing (and how to stop it forever)"
"The shrink operation that won't get you fired"

Remember: This script is your  emergency fire extinguisher  - essential to have, but you hope you never need to use it! 

Alternative Approach
Executive summary/business case

• Why this matters
• Large or uncontrolled log files consume disk, slow backups, complicate restores, and amplify ransomware damage.
• Frequent, ad-hoc shrinking causes fragmentation and performance problems.

The right approach: prevent log growth with a correct recovery model + frequent log backups, monitor, and only shrink when necessary with a documented, auditable process.

Business benefits

• Predictable disk usage and lower storage costs.
• Faster restores and improved availability.
• Audit trail for operations (compliance & change control).

Key principles & best standard (short)

• Prefer prevention over cure:  take frequent transaction-log backups (FULL model) or use SIMPLE model only for dev/test.
• Avoid routine shrinking:  shrink only when required (e.g., after one-off huge operation, log growth due to long-running transaction, or DB restore/maintenance).
• Logically plan growth settings:  set sensible  FILEGROWTH  and  INITIAL SIZE —Avoid tiny percent-based growth for large logs.
• Automate monitoring & alerts:  watch  log_reuse_wait_desc , free space, and autogrowth events.
• Audit & document any shrink operation.  Record who ran it, why, and the before/after sizes.

Common causes of log growth
Long-running or uncommitted transactions
Replication, CHANGE_TRACKING, CDC, DB mirroring, AlwaysOn, long-running backups
Missing or infrequent log backups in the FULL recovery model
Bulk operations (index rebuilds, large loads)

Pros & Cons of shrinking logs
Pros
Instantly reclaims disk after one-time surge.
Useful after large one-off operations or restoring from a backup with a larger log.

Cons
Causes file fragmentation and potential performance degradation.
Shrinking regrows logs if operations continue — regrowth is expensive.
Not a long-term solution to recurring growth.

Best practices (step-by-step checklist before shrinking)

• Investigate root cause: Check  DBCC SQLPERF(LOGSPACE)  and  sys.databases   log_reuse_wait_desc .
• Fix underlying issue: e.g., schedule frequent log backups, commit/kill long transactions, disable/reconfigure features.
• Take a transaction log backup (FULL model)  to free up virtual log files (VLFs) if possible.
• Delay shrinking until log is reusable  (log_reuse_wait_desc = NOTHING).
• Document & audit : always insert an audit record before/after shrink.
• Shrink in a maintenance window and monitor performance, autogrowth.
• Adjust file growth strategy: e.g.,  FILEGROWTH = 512MB  for busy OLTP DBs, avoid 1% growth for large files.
• Perform index maintenance afterwards if required (heavy shrink may impact fragmentation).

Security, permissions & masking sensitive fields

• Do not run shrink scripts as  sa  unless required. Use a least-privilege account with  ALTER DATABASE  permissions or run as an approved operator via SQL Agent.
• Mask sensitive values in audit/log tables (if you store server or path info that could reveal structure). Example: store  ServerName  hashed or store only the first/last 8 chars.
• Record operator identity using  SUSER_SNAME()  or  ORIGINAL_LOGIN()  — but don’t store personal data that is not necessary.

Table design (you provided) — small improvements
Add a default  StartTime  and index for queries:
ALTER TABLE log.LogShrinkAudit
ADD CONSTRAINT DF_LogShrinkAudit_StartTime DEFAULT (GETDATE()) FOR StartTime;

CREATE NONCLUSTERED INDEX IX_LogShrinkAudit_DBName_StartTime
ON log.LogShrinkAudit(DBName, StartTime DESC);

Masking note: if you log paths or server names and want to mask, store a hash:  HASHBYTES('SHA2_256', @FullServerPath)  (store varbinary) and keep mapping in a secure admin-only table.

Production-ready stored procedure (improved, audited, safe)
Key improvements over the original:

Detects  log_reuse_wait_desc  and refuses to shrink unless reason is  NOTHING  or explicit override given.

Optional  @Force  for documented emergency shrink with required  @Reason  param.

Records before/after sizes, VLF count, user who ran it.

Graceful error handling and throttled shrink (shrinks to target percent/size).

Avoids setting recovery model from FULL→SIMPLE→FULL automatically (dangerous) unless explicitly allowed.

Warning:  Changing recovery model from FULL→SIMPLE causes break in log backup chain. Only do if you know consequences.

-- ================================================-- dbo.spLogClear_Managed-- Safer, auditable transactional log cleanup procedure-- Usage: EXEC dbo.spLogClear_Managed @TargetSizeMB=1024, @Force=0, @AllowRecoveryChange=0, @Reason='...';-- ================================================CREATE OR ALTER PROCEDURE dbo.spLogClear_Managed
(
@TargetSizeMB INT = 1024,            -- desired final size in MB (approx)
@MinFreePercent INT = 10,            -- shrink only if free space percent > this
@Force BIT = 0,                      -- 1 = allow shrink even if log_reuse_wait != NOTHING (use with caution)
@AllowRecoveryChange BIT = 0,        -- 1 = allow temporary switch to SIMPLE (dangerous, breaks log chain)
@Reason NVARCHAR(4000) = NULL        -- required if @Force = 1 or @AllowRecoveryChange = 1
)
ASBEGIN
SET NOCOUNT ON;
DECLARE @DBName SYSNAME = DB_NAME();
DECLARE @LogFileName SYSNAME;
DECLARE @StartTime DATETIME = GETDATE();
DECLARE @BeforeSizeMB INT;
DECLARE @BeforeUsedPercent DECIMAL(5,2);
DECLARE @AfterSizeMB INT;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @WaitDesc SYSNAME;
DECLARE @Operator SYSNAME = SUSER_SNAME();
DECLARE @AuditMsg NVARCHAR(MAX);

BEGIN TRY
    -- 1. Log initial state: find log file
    SELECT TOP(1)
        @LogFileName = name
    FROM sys.database_files
    WHERE type_desc = 'LOG';

    -- 2. Get current log size & used percent
    SELECT
        @BeforeSizeMB = CAST(size/128.0 AS INT),
        @BeforeUsedPercent = CAST( ( (size - CAST(FILEPROPERTY(name,'SpaceUsed') AS INT)) * 100.0 / NULLIF(size,0) ) AS DECIMAL(5,2) )
    FROM sys.database_files
    WHERE type_desc = 'LOG' AND name = @LogFileName;

    -- 3. Determine log reuse wait reason
    SELECT @WaitDesc = log_reuse_wait_desc
    FROM sys.databases
    WHERE name = @DBName;

    -- 4. Audit start
    INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, Status, Message)
    VALUES (@DBName, @LogFileName, @StartTime, 'Started',
            'Initiated by ' + ISNULL(@Operator,'Unknown') + '; log_reuse_wait_desc=' + ISNULL(@WaitDesc,'Unknown'));

    -- 5. Safety checks
    IF @Force = 0 AND @WaitDesc <> 'NOTHING'
    BEGIN
        SET @AuditMsg = 'Abort: log_reuse_wait_desc = ' + @WaitDesc + '. Use proper log backup or resolve wait reason before shrinking.';
        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES(@DBName, @LogFileName, @StartTime, GETDATE(), 'Aborted', @AuditMsg);
        RETURN;
    END

    IF @Force = 1 AND (@Reason IS NULL OR LEN(@Reason) < 5)
    BEGIN
        THROW 51010, 'Force requested but @Reason is required (short justification).', 1;
    END

    -- 6. Optionally switch recovery model (DANGEROUS)
    IF @AllowRecoveryChange = 1
    BEGIN
        IF @Reason IS NULL OR LEN(@Reason) < 5
            THROW 51011, 'AllowRecoveryChange requires a justified @Reason.', 1;

        SET @SQL = N'ALTER DATABASE [' + @DBName + '] SET RECOVERY SIMPLE WITH NO_WAIT;';
        EXEC(@SQL);

        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, Status, Message)
        VALUES(@DBName, @LogFileName, @StartTime, 'Info', 'Recovery switched to SIMPLE temporarily. Reason: ' + @Reason);
    END

    -- 7. Do a checkpoint if in SIMPLE or after log backup
    CHECKPOINT;

    -- 8. Recompute size and used percent (safe check)
    SELECT
        @BeforeSizeMB = CAST(size/128.0 AS INT),
        @BeforeUsedPercent = CAST( ( (size - CAST(FILEPROPERTY(name,'SpaceUsed') AS INT)) * 100.0 / NULLIF(size,0) ) AS DECIMAL(5,2) )
    FROM sys.database_files
    WHERE type_desc = 'LOG' AND name = @LogFileName;

    -- 9. Check min free percent condition
    IF @BeforeUsedPercent <= (100 - @MinFreePercent)
    BEGIN
        -- compute target shrink size in pages (approx) using DBCC SHRINKFILE target in MB
        SET @SQL = N'DBCC SHRINKFILE ([' + @LogFileName + '], ' + CAST(@TargetSizeMB AS NVARCHAR(20)) + ');';
        EXEC(@SQL);

        -- Capture after size
        SELECT @AfterSizeMB = CAST(size/128.0 AS INT)
        FROM sys.database_files
        WHERE type_desc = 'LOG' AND name = @LogFileName;

        SET @AuditMsg = 'Shrink attempted. BeforeSizeMB=' + CAST(@BeforeSizeMB AS NVARCHAR(20)) +
                        '; AfterSizeMB=' + CAST(ISNULL(@AfterSizeMB, -1) AS NVARCHAR(20));
        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES(@DBName, @LogFileName, @StartTime, GETDATE(), 'Success', @AuditMsg);
    END
    ELSE
    BEGIN
        SET @AuditMsg = 'Abort: insufficient free space in log to shrink safely. UsedPercent=' + CAST(@BeforeUsedPercent AS NVARCHAR(10));
        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES(@DBName, @LogFileName, @StartTime, GETDATE(), 'Skipped', @AuditMsg);
    END

    -- 10. Restore recovery model if changed (only if @AllowRecoveryChange = 1)
    IF @AllowRecoveryChange = 1
    BEGIN
        SET @SQL = N'ALTER DATABASE [' + @DBName + '] SET RECOVERY FULL WITH NO_WAIT;';
        EXEC(@SQL);

        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, Status, Message)
        VALUES(@DBName, @LogFileName, GETDATE(), 'Info', 'Recovery switched back to FULL. Ensure you take a full + log backup to re-establish chain.');
    END
END TRY
BEGIN CATCH
    DECLARE @ErrMsg NVARCHAR(MAX) = ERROR_MESSAGE();
    INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, EndTime, Status, Message)
    VALUES(@DBName, ISNULL(@LogFileName,'Unknown'), @StartTime, GETDATE(), 'Failed', @ErrMsg);
    THROW; -- re-throw for agent visibility
END CATCH
END
GO

Usage examples
Normal safe run (abort if log is in use):
EXEC dbo.spLogClear_Managed @TargetSizeMB=1024, @MinFreePercent=20;

Emergency (must supply reason):
EXEC dbo.spLogClear_Managed @TargetSizeMB=512, @Force=1, @Reason='Emergency disk pressure after large archive load';

Temporary recovery change (dangerous; document and follow-up!):
EXEC dbo.spLogClear_Managed @TargetSizeMB=512, @AllowRecoveryChange=1, @Reason='One-off maintenance, will take full backup after';

Alternatives & automation optionsPreferred:  Fix root cause (frequent log backups) — implement a schedule  BACKUP LOG  to reduce VLF usage.

• Ola Hallengren  — maintenance solution includes integrity checks, backup, and cleanup.
• PowerShell approach:  use  Invoke-Sqlcmd  to check state and issue shrink if needed; easier to integrate with vaults and monitoring.
• Third-party:  enterprise tools that manage log shipping, archiving, and unintrusive cleanup.

Monitoring & telemetry (what to alert on)
log_reuse_wait_desc  ≠ NOTHING — alert when it grows continuously.

Rapid autogrowth events — alert on frequent autogrowth.

Free disk space thresholds.

Number of VLFs (very large counts cause performance issues) — use  DBCC LOGINFO  or DMV-based scripts.

Risk mitigation & rollback plan
Before : always take current full + log backup.

If the shrink fails or the DB becomes suspect: have an emergency restore plan; always test restores in staging.
After: if you changed the recovery model, take a full backup, then restart the log backup schedule to reestablish the chain.

Masking sensitive fields example
When inserting path/server info into logs, store only hashed values or partials:
INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, Status, Message)
VALUES(@DBName, HASHBYTES('SHA2_256', @LogFileName), GETDATE(), 'Started', 'Initiated by ' + SUSER_SNAME());

Store mapping in a separate secure table accessible only to auditors.

• “Ransomware-resistant log strategy”
• “Zero-trust backup & log hygiene”
• “Immutable audit trail”
• “Predictable storage, instant restoration”
• “Proactive log management — not reactive surgery”

SQL Server may automatically switch a database into EMERGENCY mode if it is suspected of being compromised. Limited access is available for troubleshooting and repairs in this mode. This post will explain how to properly restore a database from EMERGENCY mode using Microsoft-approved recovery procedures and detailed SQL instructions.

What Is Emergency Mode?
Emergency mode is a special state in SQL Server that allows administrators to access a database marked as corrupt or inaccessible. It’s read-only, single-user, and bypasses the transaction log for emergency repair or data extraction.

Typical reasons your database enters this state include:

Missing or corrupt transaction logs

• Hardware failure or disk corruption
• Unexpected shutdowns or SQL Server crashes
• File system or drive letter changes

Step-by-Step Guide to Recover a Database from Emergency Mode

Step 1. Check Database State
Use this command to verify the current state of your database:
SELECT name, state_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';

If the state shows as EMERGENCY or SUSPECT, proceed to recovery.

Step 2. Set Database to Emergency Mode (Manually)
If SQL Server has not already done this, you can manually put your database into EMERGENCY mode:
ALTER DATABASE YourDatabaseName SET EMERGENCY;
GO

This gives you sysadmin-level access to inspect and fix the database.

Step 3. Run Consistency Check (DBCC CHECKDB)
Now, check the physical and logical integrity of your database:
DBCC CHECKDB (YourDatabaseName);
GO

If you see errors like “Msg 824, 825, or 826”, it means corruption exists.

Step 4. Set Database to Single User Mode
Before performing repairs, you must ensure no other connections interfere:
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Step 5. Attempt to Repair the Database
There are multiple repair levels; the most commonly used for severe corruption is:
DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS);
GO

Warning: As the name suggests, this may cause some data loss.
Always attempt to restore from backup first if available.

If you have a valid recent backup, do not run this — instead, restore using RESTORE DATABASE.

Step 6. Set Database Back to Multi User Mode
Once the repair completes successfully, return the database to normal operation:
ALTER DATABASE YourDatabaseName SET MULTI_USER;
GO

Then, verify the database state again:
SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
GO

It should now show as ONLINE.

Optional: Restore from Backup Instead of Repair

If you maintain regular backups, restoring is the safest route:
RESTORE DATABASE YourDatabaseName
FROM DISK = 'D:\Backups\YourDatabaseName.bak'
WITH REPLACE;
GO

This avoids potential data loss caused by emergency repairs.

Example Scenario
Let’s assume your database StudentDB is marked as SUSPECT.
Here’s a real-world recovery script:
USE master;
GO

ALTER DATABASE StudentDB SET EMERGENCY;
GO

ALTER DATABASE StudentDB SET SINGLE_USER;
GO

DBCC CHECKDB (StudentDB, REPAIR_ALLOW_DATA_LOSS);
GO

ALTER DATABASE StudentDB SET MULTI_USER;
GO

After running this, StudentDB should return to a normal operational state.

Verify and Backup Immediately
Once your database is online:
Validate key tables and data integrity.

Take a full backup immediately:
BACKUP DATABASE StudentDB TO DISK = 'D:\Backup\StudentDB_Recovered.bak';
GO

Monitor your SQL Server logs for any recurring I/O or consistency errors.

Best Practices to Prevent Emergency Mode Issues

• Always maintain daily full backups and transaction log backups.
• Enable SQL Server alerts for error codes 823, 824, 825.
• Store backups on different physical drives or cloud storage (e.g., Azure Blob, AWS S3).
• Use ECC RAM and RAID storage for database servers.
• Schedule regular DBCC CHECKDB checks in SQL Agent jobs.

It may be necessary to use the results of another query to filter data while writing SQL queries. The IN and EXISTS operators are two popular methods for doing this. They may seem similar and often return the same results, but they work differently in the background. Choosing the right one can help your query run faster.

In this blog, we’ll look at:

• What IN and EXISTS mean
• Easy examples to understand how they work
• How their performance compares
• Tips on when to use each one
• Getting to Know the Concept

IN Operator:   The IN operator helps you check whether a value appears in a list or in the results of another query. If the value is found in that list or subquery, the condition returns true.
SELECT *
FROM Employees
WHERE DepartmentID IN (
    SELECT DepartmentID FROM Departments WHERE IsActive = 1
);

How it works?
The subquery is evaluated first, generating a list of DepartmentID values. The outer query then checks if each row’s DepartmentID is present in that list.
EXISTS Operator: The EXISTS operator is used to check whether a subquery returns any rows. If the subquery finds even one matching record, the condition returns true. It doesn’t compare specific values — it simply checks if results exist
SELECT *
FROM Employees E
WHERE EXISTS (
    SELECT 1
    FROM Departments D
    WHERE D.DepartmentID = E.DepartmentID
      AND D.IsActive = 1
);

How it works?
For each row in Employees, the subquery checks for at least one matching DepartmentID. If it finds one, it stops searching and returns TRUE

Key Differences

Performance Comparison
Let us compare performance with a practical scenario.

• IN can slow down if the subquery returns a lot of rows that include NULL values.
• EXISTS doesn’t have this problem and usually works more efficiently for large datasets.

Think of it like this: IN is like checking a list item by item, while EXISTS just asks, “Is there at least one match?” — and stops as soon as it finds one.

Best Practices for Using IN and EXISTS
Use EXISTS for correlated subqueries

When your subquery depends on the outer query, EXISTS is usually the better choice:
SELECT *
FROM Orders O
WHERE EXISTS (
    SELECT 1
    FROM OrderDetails D
    WHERE D.OrderID = O.OrderID
);

Use IN for small or fixed lists
If you have a short list of known values, IN is simple and easy to read:
WHERE Country IN ('US', 'CA', 'UK')

Be careful with NOT IN and NULLs
If your subquery might return NULL, using NOT IN can lead to unexpected results:
WHERE DepartmentID NOT IN (
    SELECT DepartmentID
    FROM Departments
)

If the subquery contains a NULL, this query will return no results. To avoid this problem, use NOT EXISTS instead — it handles NULLs safely.

Inspect the execution plan
Use EXPLAIN (or SET SHOWPLAN in SQL Server) to see how the database executes your query. This shows whether it turns your query into a semi-join (for IN/EXISTS) or an anti-join (for NOT EXISTS).

Index your foreign keys
Make sure the columns used in subqueries — especially join keys like DepartmentID — have indexes. This helps the database find matches faster and improves performance.

Final Thoughts
Both IN and EXISTS are powerful tools in SQL. The key is not just knowing what they do, but understanding how they work behind the scenes. By paying attention to the size of your data, indexing, and how NULLs are handled, you can choose the most efficient option for your queries.

Remember

• Use IN for small, fixed lists of values.
• Use EXISTS for correlated subqueries or large result sets.
• Always check the execution plan - query optimizers can surprise you!

With these tips, you’ll be able to write SQL queries that are both correct and fast.
Happy Coding!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

SET SERVEROUTPUT ON in PL/SQL: Why It Matters and How to Use It
When working with Oracle PL/SQL, one of the first and most essential commands you'll encounter is:

SET SERVEROUTPUT ON;
Though it looks simple, this command plays a crucial role in how PL/SQL programs behave, especially when you want to display output from procedures, anonymous blocks, or scripts using DBMS_OUTPUT.PUT_LINE.

Why Do We Use SET SERVEROUTPUT ON?
By default, PL/SQL code runs silently inside the Oracle database engine. This means that even if your program generates output, you won’t see any result on your screen — unless you explicitly enable server output.

The command SET SERVEROUTPUT ON instructs SQL*Plus or SQL Developer to display output from the DBMS_OUTPUT buffer, allowing you to see the results of your PL/SQL program.

Syntax
SET SERVEROUTPUT ON;

You can also control the buffer size and format (optional):
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED;

When Should You Use It?
Use SET SERVEROUTPUT ON in situations like:

When using DBMS_OUTPUT.PUT_LINE to display output.

During testing and debugging of PL/SQL procedures or anonymous blocks.

To trace variable values or track the flow of control in your code.

Example: Using SET SERVEROUTPUT ON in a PL/SQL Block

Here’s a simple example that declares variables and uses DBMS_OUTPUT.PUT_LINE to display them:
SET SERVEROUTPUT ON;

DECLARE
    eno NUMBER(5) NOT NULL := 2;
    ename VARCHAR2(15) := 'Branson Devs';
    edept CONSTANT VARCHAR2(15) := 'Web Developer';
BEGIN
    dbms_output.put_line('Declared Values:');
    dbms_output.put_line(' Employee Number: ' || eno || ' Employee Name: ' || ename);
    dbms_output.put_line('Constant Declared:');
    dbms_output.put_line(' Employee Department: ' || edept);
END;
/

Output (Only Visible If SERVEROUTPUT Is ON):
Declared Values:
Employee Number: 2 Employee Name: Branson Devs
Constant Declared:
Employee Department: Web Developer

Important: If you omit SET SERVEROUTPUT ON, the DBMS_OUTPUT.PUT_LINE results will not be displayed, even though the block executes successfully.

Tips for Using SET SERVEROUTPUT ON
In SQL Developer, go to View > DBMS Output, then click the green + icon to enable output for your session.
In SQL*Plus, always run SET SERVEROUTPUT ON before any PL/SQL block that uses output.
Use SET SERVEROUTPUT OFF when you no longer need the output to be displayed.

Conclusion
The SET SERVEROUTPUT ON command is small but vital for writing and debugging PL/SQL code. It provides visibility into your PL/SQL logic by allowing output messages to be isplayed on screen, making your development workflow smoother and more transparent.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The common language used to manage and communicate with relational databases is called Structured Query Language, or SQL. SQL provides the capabilities to effectively store, manage, and retrieve data, whether you're developing an enterprise application, corporate dashboard, or website.

What is SQL?
SQL (pronounced “ess-que-el” or sometimes “sequel”) is a domain-specific language used to communicate with relational database management systems (RDBMS) such as:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle Database

SQLite
SQL lets you describe what data you want, while the database engine figures out how to get it.

Core Features of SQL

• Data Definition: Create and modify the structure of databases (tables, views, indexes).
• Data Manipulation: Insert, update, delete, and retrieve records.
• Data Control: Manage permissions and security (GRANT, REVOKE).
• Transaction Control: Commit or roll back changes safely.

Basic SQL Commands
Here are some of the most commonly used SQL statements:

Create a Table
CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(50),

Position VARCHAR(50),

Salary DECIMAL(10,2)

);

Insert Data
INSERT INTO Employees (EmployeeID, Name, Position, Salary)

VALUES (1, 'Alice', 'Developer', 65000);

Retrieve Data
SELECT Name, Position

FROM Employees

WHERE Salary > 60000;

Update Data
UPDATE Employees

SET Salary = 70000

WHERE EmployeeID = 1;

Delete Data
DELETE FROM Employees

WHERE EmployeeID = 1;

Why SQL is Important?

• Universality: Nearly all relational databases use SQL or a close dialect.
• Powerful Queries: Combine, group, and filter data with ease.
• Data Integrity: Enforce constraints (primary keys, foreign keys) to keep data consistent.
• Scalability: Handle anything from a small app’s data to enterprise-level systems.

Common Uses of SQL

• Business intelligence and reporting
• Backend for web and mobile apps
• Data analytics and dashboards
• Financial and inventory systems
• Data migration between platforms

Advantages of SQL

• Human-readable, declarative syntax
• Optimized by database engines for performance
• Portable across platforms with minimal changes
• Supports complex operations with relatively simple commands

Limitations

• Not ideal for unstructured or semi-structured data (that’s where NoSQL databases shine).
• Large, complex queries can become hard to maintain without proper design.
• Performance tuning may require knowledge of indexes, execution plans, and normalization.

Conclusion
The foundation of relational systems' data handling is SQL. You acquire a useful ability that forms the basis of practically every contemporary software program by becoming proficient with its commands and comprehending how databases arrange data. SQL is an essential skill for anyone dealing with data, whether they are an analyst, developer, or data scientist.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The most of the harm is typically caused by a few statements when a SQL Server feels "slow." With Dynamic Management Views (DMVs), you can locate them the quickest. The worst offenders are uncovered by CPU, IO, duration, and "what's running right now" using the copy-paste scripts below, along with instructions on how to read the results and what to do next.

Requirements: VIEW SERVER STATE permission. Numbers like total_worker_time and total_elapsed_time are in microseconds unless noted.

What “high usage” means (pick the lens)

• CPU: how much processor time a query uses.
• IO: logical/physical reads and writes (memory and disk pressure).
• Duration: how long a query takes end-to-end.
• Currently running: live workload that may be blocking others.

You’ll use a different script depending on which lens you want.

Top queries by CPU
-- Top 20 queries by total CPU since the plan was cached
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    qs.total_worker_time/1000.0                             AS total_cpu_ms,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_cpu_ms,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    qs.total_logical_reads + qs.total_physical_reads        AS total_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Tip: Add WHERE DB_NAME(st.dbid) = 'YourDbName' if you only care about one database.
Top queries by IO (reads/writes)

-- Top 20 by total reads; add writes if you care about heavy DML
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    (qs.total_logical_reads + qs.total_physical_reads)      AS total_reads,
    (qs.total_logical_writes + qs.total_physical_writes)    AS total_writes,
    (qs.total_logical_reads + qs.total_physical_reads) / NULLIF(qs.execution_count,0) AS avg_reads,
    (qs.total_logical_writes + qs.total_physical_writes) / NULLIF(qs.execution_count,0) AS avg_writes,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_reads DESC;

Top queries by average duration
-- Queries that are slow per execution (not just popular)
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    (qs.total_elapsed_time/1000.0)                          AS total_duration_ms,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0)  AS avg_cpu_ms,
    (qs.total_logical_reads + qs.total_physical_reads) / NULLIF(qs.execution_count,0) AS avg_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count > 0
ORDER BY avg_duration_ms DESC;

Stored procedures that hit the server hardest
Use sys.dm_exec_procedure_stats to get procedure-level rollups (cleaner than trying to stitch statements together).

-- Procedures by total CPU
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    ps.total_worker_time/1000.0                             AS total_cpu_ms,
    (ps.total_worker_time/1000.0)/NULLIF(ps.execution_count,0) AS avg_cpu_ms,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4  -- skip system DBs; remove if you want them
ORDER BY ps.total_worker_time DESC;

-- Procedures by total reads (IO)
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    (ps.total_logical_reads + ps.total_physical_reads)      AS total_reads,
    ((ps.total_logical_reads + ps.total_physical_reads)/NULLIF(ps.execution_count,0)) AS avg_reads,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4
ORDER BY total_reads DESC;

-- Procedures by average duration
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    (ps.total_elapsed_time/1000.0)/NULLIF(ps.execution_count,0) AS avg_duration_ms,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4 AND ps.execution_count > 0
ORDER BY avg_duration_ms DESC;

What’s heavy right now (live view)?
-- Currently executing requests ordered by CPU time
SELECT
    r.session_id,
    r.status,
    DB_NAME(r.database_id)            AS database_name,
    r.cpu_time                        AS cpu_ms,         -- already in ms
    r.total_elapsed_time              AS elapsed_ms,     -- already in ms
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    SUBSTRING(t.text, r.statement_start_offset/2 + 1,
        (CASE WHEN r.statement_end_offset = -1
              THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset END - r.statement_start_offset)/2 + 1) AS running_statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

If you see blocking_session_id populated, chase that session and fix the blocker first.

Group similar statements together (query_hash)
Same query text with different literals can appear as separate rows. Aggregate by query_hash to see the true top offenders.
-- Roll up by query_hash to combine similar statements
WITH q AS (
    SELECT
        qs.query_hash,
        qs.execution_count,
        qs.total_worker_time,
        qs.total_elapsed_time,
        qs.total_logical_reads + qs.total_physical_reads AS total_reads
    FROM sys.dm_exec_query_stats qs
)
SELECT TOP 20
    query_hash,
    SUM(execution_count)                                  AS executions,
    SUM(total_worker_time)/1000.0                         AS total_cpu_ms,
    (SUM(total_worker_time)/1000.0)/NULLIF(SUM(execution_count),0) AS avg_cpu_ms,
    (SUM(total_elapsed_time)/1000.0)/NULLIF(SUM(execution_count),0) AS avg_duration_ms,
    SUM(total_reads)                                      AS total_reads
FROM q
GROUP BY query_hash
ORDER BY total_cpu_ms DESC;

Filters you’ll actually use

Add these lines to any query above as needed:
-- Only one DB
WHERE DB_NAME(st.dbid) = 'YourDbName'

-- Only statements executed in the last day (approx; uses last_execution_time)
WHERE qs.last_execution_time >= DATEADD(DAY, -1, SYSUTCDATETIME())

-- Exclude trivial one-off executions
AND qs.execution_count >= 5

Read the numbers the right way

• High total + low average: popular query. Optimize for throughput (indexing, cached plan quality).
• Low total + very high average: rare but slow. Optimize for latency (rewrite, avoid RBAR/scalar UDFs, better joins).
• High duration but modest CPU/IO: usually blocking or waits. Check wait_type, blocking_session_id, and missing indexes that cause scans.
• Metrics reset when plans get evicted or the instance restarts. Treat them as a rolling window, not forever history.

Quick wins to try after you find a culprit

• Add the right index (covering where needed). Look at the actual plan’s missing index hints, then design a lean index yourself (don’t blindly accept 12-column monsters).
• Kill implicit conversions (mismatched data types, e.g., NVARCHAR vs INT).
• Replace SELECT * with exact columns (cuts reads).
• Update statistics if they’re stale; consider WITH RECOMPILE for bad parameter sniffing cases (sparingly).
• Avoid scalar UDFs in hot paths; inline logic or use APPLY.
• Batch big writes; keep transactions short.

Bonus: store a snapshot for trending
If you want a daily/15-minute snapshot to trend over time:
-- One-time setup
CREATE TABLE dbo.TopQuerySnapshot
(
    captured_at           DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    database_name         SYSNAME,
    executions            BIGINT,
    total_cpu_ms          BIGINT,
    avg_cpu_ms            DECIMAL(18,2),
    avg_duration_ms       DECIMAL(18,2),
    total_reads           BIGINT,
    query_text            NVARCHAR(MAX)
);

-- Collector (schedule as an Agent Job)
INSERT INTO dbo.TopQuerySnapshot (database_name, executions, total_cpu_ms, avg_cpu_ms, avg_duration_ms, total_reads, query_text)
SELECT TOP 50
    DB_NAME(st.dbid),
    qs.execution_count,
    qs.total_worker_time/1000,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0),
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0),
    (qs.total_logical_reads + qs.total_physical_reads),
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Now you can chart trends and prove improvements.

Conclusion
Start with CPU, then IO, then duration. Use the live view if users are complaining right now. Once you spot a heavy hitter, check its plan, add the right index, fix data types, and re-test. Small, focused changes usually deliver big wins.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The most of the harm is typically caused by a few statements when a SQL Server feels "slow." With Dynamic Management Views (DMVs), you can locate them the quickest. The worst offenders are uncovered by CPU, IO, duration, and "what's running right now" using the copy-paste scripts below, along with instructions on how to read the results and what to do next.

    Requirements: VIEW SERVER STATE permission. Numbers like total_worker_time and total_elapsed_time are in microseconds unless noted.

What “high usage” means (pick the lens)?

• CPU: how much processor time a query uses.
• IO: logical/physical reads and writes (memory and disk pressure).
• Duration: how long a query takes end-to-end.
• Currently running: live workload that may be blocking others.

You’ll use a different script depending on which lens you want.

Top queries by CPU
-- Top 20 queries by total CPU since the plan was cached
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    qs.total_worker_time/1000.0                             AS total_cpu_ms,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_cpu_ms,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    qs.total_logical_reads + qs.total_physical_reads        AS total_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Tip: Add WHERE DB_NAME(st.dbid) = 'YourDbName' if you only care about one database.

Top queries by IO (reads/writes)
-- Top 20 by total reads; add writes if you care about heavy DML
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    (qs.total_logical_reads + qs.total_physical_reads)      AS total_reads,
    (qs.total_logical_writes + qs.total_physical_writes)    AS total_writes,
    (qs.total_logical_reads + qs.total_physical_reads) / NULLIF(qs.execution_count,0) AS avg_reads,
    (qs.total_logical_writes + qs.total_physical_writes) / NULLIF(qs.execution_count,0) AS avg_writes,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_reads DESC;

Top queries by average duration
-- Queries that are slow per execution (not just popular)
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    (qs.total_elapsed_time/1000.0)                          AS total_duration_ms,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0)  AS avg_cpu_ms,
    (qs.total_logical_reads + qs.total_physical_reads) / NULLIF(qs.execution_count,0) AS avg_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count > 0
ORDER BY avg_duration_ms DESC;

Stored procedures that hit the server hardest
Use sys.dm_exec_procedure_stats to get procedure-level rollups (cleaner than trying to stitch statements together).
-- Procedures by total CPU
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    ps.total_worker_time/1000.0                             AS total_cpu_ms,
    (ps.total_worker_time/1000.0)/NULLIF(ps.execution_count,0) AS avg_cpu_ms,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4  -- skip system DBs; remove if you want them
ORDER BY ps.total_worker_time DESC;

-- Procedures by total reads (IO)
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    (ps.total_logical_reads + ps.total_physical_reads)      AS total_reads,
    ((ps.total_logical_reads + ps.total_physical_reads)/NULLIF(ps.execution_count,0)) AS avg_reads,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4
ORDER BY total_reads DESC;

-- Procedures by average duration
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    (ps.total_elapsed_time/1000.0)/NULLIF(ps.execution_count,0) AS avg_duration_ms,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4 AND ps.execution_count > 0
ORDER BY avg_duration_ms DESC;

What’s heavy right now (live view)?
-- Currently executing requests ordered by CPU time
SELECT
    r.session_id,
    r.status,
    DB_NAME(r.database_id)            AS database_name,
    r.cpu_time                        AS cpu_ms,         -- already in ms
    r.total_elapsed_time              AS elapsed_ms,     -- already in ms
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    SUBSTRING(t.text, r.statement_start_offset/2 + 1,
        (CASE WHEN r.statement_end_offset = -1
              THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset END - r.statement_start_offset)/2 + 1) AS running_statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

If you see blocking_session_id populated, chase that session and fix the blocker first.

Group similar statements together (query_hash)
Same query text with different literals can appear as separate rows. Aggregate by query_hash to see the true top offenders.
-- Roll up by query_hash to combine similar statements
WITH q AS (
    SELECT
        qs.query_hash,
        qs.execution_count,
        qs.total_worker_time,
        qs.total_elapsed_time,
        qs.total_logical_reads + qs.total_physical_reads AS total_reads
    FROM sys.dm_exec_query_stats qs
)
SELECT TOP 20
    query_hash,
    SUM(execution_count)                                  AS executions,
    SUM(total_worker_time)/1000.0                         AS total_cpu_ms,
    (SUM(total_worker_time)/1000.0)/NULLIF(SUM(execution_count),0) AS avg_cpu_ms,
    (SUM(total_elapsed_time)/1000.0)/NULLIF(SUM(execution_count),0) AS avg_duration_ms,
    SUM(total_reads)                                      AS total_reads
FROM q
GROUP BY query_hash
ORDER BY total_cpu_ms DESC;

Filters you’ll actually use
Add these lines to any query above as needed:
-- Only one DB
WHERE DB_NAME(st.dbid) = 'YourDbName'

-- Only statements executed in the last day (approx; uses last_execution_time)
WHERE qs.last_execution_time >= DATEADD(DAY, -1, SYSUTCDATETIME())

-- Exclude trivial one-off executions
AND qs.execution_count >= 5

Read the numbers the right way

• High total + low average: popular query. Optimize for throughput (indexing, cached plan quality).
• Low total + very high average: rare but slow. Optimize for latency (rewrite, avoid RBAR/scalar UDFs, better joins).
• High duration but modest CPU/IO: usually blocking or waits. Check wait_type, blocking_session_id, and missing indexes that cause scans.
• Metrics reset when plans get evicted or the instance restarts. Treat them as a rolling window, not forever history.

Quick wins to try after you find a culprit

• Add the right index (covering where needed). Look at the actual plan’s missing index hints, then design a lean index yourself (don’t blindly accept 12-column monsters).
• Kill implicit conversions (mismatched data types, e.g., NVARCHAR vs INT).
• Replace SELECT * with exact columns (cuts reads).
• Update statistics if they’re stale; consider WITH RECOMPILE for bad parameter sniffing cases (sparingly).
• Avoid scalar UDFs in hot paths; inline logic or use APPLY.
• Batch big writes; keep transactions short.

Bonus: store a snapshot for trending
If you want a daily/15-minute snapshot to trend over time:
-- One-time setup
CREATE TABLE dbo.TopQuerySnapshot
(
    captured_at           DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    database_name         SYSNAME,
    executions            BIGINT,
    total_cpu_ms          BIGINT,
    avg_cpu_ms            DECIMAL(18,2),
    avg_duration_ms       DECIMAL(18,2),
    total_reads           BIGINT,
    query_text            NVARCHAR(MAX)
);

-- Collector (schedule as an Agent Job)
INSERT INTO dbo.TopQuerySnapshot (database_name, executions, total_cpu_ms, avg_cpu_ms, avg_duration_ms, total_reads, query_text)
SELECT TOP 50
    DB_NAME(st.dbid),
    qs.execution_count,
    qs.total_worker_time/1000,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0),
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0),
    (qs.total_logical_reads + qs.total_physical_reads),
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Now you can chart trends and prove improvements.

Common questions
Does this include plans not in cache?
No. DMVs reflect what’s cached. For long-term history, enable Query Store (SQL Server 2016+) and analyze sys.query_store_runtime_stats.

What about currently blocked sessions?
Use the “live view” script; chase the blocking_session_id, then inspect that session’s SQL text and plan.

Conclusion
CPU comes first, followed by IO and duration. If users are currently complaining, use the live view. Check its strategy, add the appropriate index, correct data types, and retest when you've identified a heavy hitter. Big results are typically achieved with small, targeted modifications.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The amount of data generated and stored by organizations has been growing exponentially in recent years. Big data refers to this large and complex data sets that traditional data processing applications cannot handle. Managing and analyzing big data is becoming increasingly important for businesses to gain insights and stay ahead of the competition. Microsoft SQL Server is a powerful database management system capable of handling big data.

In this article, we will use appropriate examples to explore the techniques for working with big data in SQL Server.

Partitioning
Partitioning is a technique used to break large tables into smaller, more manageable pieces called partitions. Each partition contains a subset of the data, which can be processed and queried independently. SQL Server supports horizontal partitioning, which involves splitting data based on a column or set of columns.

For example, consider a table containing sales data for a large retail store. Partitioning the table based on the year column would create a separate partition for each year of data. This would allow queries to be run on individual partitions rather than the entire table, resulting in faster query performance. First we need to create a table with partitioned indexes. We can use the below code to create a table called "Sales" with a partitioned index on the "SaleDate" column:
CREATE TABLE [dbo].[Sales]
(
    [SaleID] [int] NOT NULL PRIMARY KEY,
    [Product] [nvarchar](50) NOT NULL,
    [SaleDate] [date] NOT NULL,
    [SaleAmount] [money] NOT NULL
)
WITH (MEMORY_OPTIMIZED = OFF, DURABILITY = ON)
GO

CREATE PARTITION FUNCTION [PF_Sales_SaleDate]
    (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-02-01', '2023-03-01');

CREATE PARTITION SCHEME [PS_Sales_SaleDate]
    AS PARTITION [PF_Sales_SaleDate]
    TO ([FG_Sales_202301], [FG_Sales_202302], [FG_Sales_202303], [FG_Sales_202304]);

CREATE CLUSTERED INDEX [CI_Sales_SaleDate]
ON [dbo].[Sales] ([SaleDate])
WITH (DROP_EXISTING = ON)
ON [PS_Sales_SaleDate] ([SaleDate]);

The code creates a table with columns for SaleID, Product, SaleDate, and SaleAmount, and defines a primary key on the SaleID column. The table is defined as DURABILITY = ON to ensure data is written to disk, but MEMORY_OPTIMIZED = OFF to ensure that data is not stored in memory. The partition function and scheme are defined to partition the table based on the SaleDate column, with partitions for January, February, and March of 2023 and at last, a clustered index is created on the SaleDate column, using the partition scheme to distribute the index across the partitions. Once we have created the table, we can insert some data into it using below query.

INSERT INTO [dbo].[Sales] ([SaleID], [Product], [SaleDate], [SaleAmount])
VALUES (1, 'Product A', '2022-01-01', 100.00),(2, 'Product B', '2022-01-02', 200.00),
       (3, 'Product C', '2022-01-03', 300.00),(4, 'Product A', '2022-02-01', 400.00),
       (5, 'Product B', '2022-02-02', 500.00),(6, 'Product C', '2022-02-03', 600.00);

Now whenever we can query the Sales table, the partitioned index will automatically be used. SQL Server can scan only the partitions that contain the relevant data. This improves query performance and reduces the amount of disk I/O required. Partitioning indexes is a powerful feature in SQL Server that can significantly improve the performance of queries on large tables. By partitioning a table based on a specific column, SQL Server can scan only the relevant partitions, reducing disk I/O and improving query performance.

Columnstore Indexes
Columnstore indexes are a specialized type of index that is optimized for large data warehouses. They store data in columns rather than rows, which makes them much more efficient for querying large datasets. Columnstore indexes are particularly useful for frequently queried but rarely updated data.

For example, consider a table containing customer sales data for a large online retailer. A columnstore index could be created on the Product columns. This would allow for very fast querying of the total sales for each product. First we need to create a table called "Sales" with a columnstore index on the "Product" column. To create Colunmstore Index:
CREATE TABLE [dbo].[Sales]
(
    [SaleID] [int] NOT NULL PRIMARY KEY,
    [Product] [nvarchar](50) NOT NULL,
    [SaleDate] [date] NOT NULL,
    [SaleAmount] [money] NOT NULL
)
WITH (MEMORY_OPTIMIZED = OFF, DURABILITY = ON)
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CSI_Sales_Product]
ON [dbo].[Sales]([Product]);

Above query creates columnstore index on the Product column as a clustered index, which means that the entire table is stored in a columnar format. Now whenever we can query the Sales table, it will be much faster than a query on a traditional row-based index because the columnstore index is created on the Product column

In-Memory OLTP
In-Memory OLTP is a new feature in SQL Server that allows for creating memory-optimized tables. These tables are stored entirely in memory, which makes them much faster than traditional disk-based tables. In-Memory OLTP is beneficial for applications requiring high performance and low latency. For example, consider a table containing stock market data. In-Memory OLTP could create a memory-optimized table that stores the latest market data. This would allow for very fast querying of real-time stock prices. To create a memory-optimized filegroup, which will contain the memory-optimized tables we can use the below query:
ALTER DATABASE [MyDatabase] ADD FILEGROUP [InMemoryFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

It will add a new filegroup called "InMemoryFilegroup" to the "MyDatabase" database, which contains memory-optimized data. Now we will create a memory-optimized table that will be stored entirely in memory:
CREATE TABLE [dbo].[MarketData_MemoryOptimized]
(
    [Name] [nvarchar](50) NOT NULL PRIMARY KEY NONCLUSTERED,
    [Price] [decimal](18, 2) NOT NULL,
    [Timestamp] [datetime2](0) NOT NULL,
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

The "MarketData_MemoryOptimized" table is defined as MEMORY_OPTIMIZED, which means it will be stored entirely in memory, and DURABILITY is set to SCHEMA_ONLY, which means it won't be written to disk. In-Memory OLTP can be a powerful tool for storing and querying real-time data in memory-optimized tables. By storing data in memory, In-Memory OLTP can improve query performance and reduce latency for certain types of database workloads, such as real-time market data.

PolyBase
PolyBase is a feature in SQL Server that allows for integrating external data sources, such as Hadoop or Azure Blob Storage. PolyBase allows for querying structured and unstructured data, making it a powerful tool for working with big data.

For example, consider a large financial services company that stores customer data in Hadoop. The company may want to analyze customer behavior and trends to improve their services, but querying the large amount of data stored in Hadoop can be difficult and time-consuming. This is where PolyBase comes in - by connecting SQL Server directly to the Hadoop data source, the company can query the data easily and quickly, allowing for in-depth analysis of customer behavior and trends.
EXEC sp_configure 'polybase enabled', 1;
RECONFIGURE;

CREATE EXTERNAL DATA SOURCE HadoopDataSource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://<HadoopNameNode>:<PortNumber>',
    CREDENTIAL = HadoopCredential
);

CREATE EXTERNAL TABLE CustomerData_Hadoop
(
    CustomerID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    EmailAddress VARCHAR(50),
    Address VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(2),
    ZipCode VARCHAR(10)
)
WITH (
    LOCATION = '/customerdata',
    DATA_SOURCE = HadoopDataSource,
    FILE_FORMAT = TEXTFILE
);

The above code enables PolyBase in SQL Server, creates an external data source called HadoopDataSource that points to the Hadoop cluster at the location hdfs://<HadoopNameNode>:<PortNumber>., and creates an external table called CustomerData_Hadoop that maps to the data stored in Hadoop. The LOCATION option specifies the location of the data in Hadoop, and the DATA_SOURCE option specifies the external data source to use to query the data. The FILE_FORMAT option specifies the format of the data in Hadoop, in this case, TEXTFILE.

PolyBase allows us to query external data sources directly from SQL Server. This can be a valuable tool for organizations that have large amounts of data stored in external sources and need to query that data quickly and easily.

Conclusion
SQL Server offers a number of effective tools and methods for handling large amounts of data. There are numerous methods for maximizing the speed of big data sets, ranging from partitioning and columnstore indexes to In-Memory OLTP and PolyBase. In today's data-driven world, companies may remain ahead of the competition and obtain insightful knowledge by employing these strategies.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In contemporary software development, database engineers and database administrators, or DBAs, are essential. They frequently have a lot of work to do, from managing performance, security, and compliance to guaranteeing data veracity. Let's talk about generative AI. These AI models are capable of creating documentation, optimizing queries, writing SQL, designing schemas, and even assisting with compliance. By taking on repetitive tasks, they enhance the abilities of DBAs rather than replace them, allowing engineers to concentrate on high-value problem-solving.

With specific examples and tools you can use right now, we'll examine how database engineers and DBAs may use generative AI throughout the data lifecycle in this post.

1. Schema Design & Documentation
Problem: Designing normalized schemas and documenting them manually is time-consuming.
AI Solution: Generative AI can propose ERDs, normalize schemas, and generate Markdown or HTML documentation instantly.
Tools: ChatGPT, dbdiagram.io AI, Claude.

Example Prompt:
“Design a normalized Postgres schema for a healthcare awareness website with users, doctors, and articles. Include indexes for frequent queries.”

Benefit: Schema prototyping is 50–70% faster.

2. SQL Query Generation & Optimization
Problem: Writing and tuning complex queries can block analysts and slow delivery.
AI Solution: AI can convert plain English into SQL, write joins, and suggest performance improvements.

Tools: ChatGPT, Text2SQL tools, MindsDB.

Example Prompt:
“Write a SQL query to return the top 10 most read articles in the past 30 days by unique users.”

Benefit: Analysts and engineers save 60% time on query generation.

3. Performance Tuning
Problem: Query optimization often involves deep expertise and trial-and-error with execution plans.
AI Solution: Paste EXPLAIN ANALYZE outputs into ChatGPT to get recommendations for indexes, partitioning, or caching.

Example Prompt:
“Here’s a Postgres EXPLAIN ANALYZE output. Recommend optimizations and appropriate indexes.”

Benefit: Speeds up triage 30–40%.

4. Data Migration & ETL Automation
Problem: Writing migration scripts between databases or cleaning data pipelines can take weeks.
AI Solution: Generative AI can generate migration code, field mappings, and transformation logic.
Tools: ChatGPT, LangChain connectors, Claude.

Example Prompt:
“Generate SQL to migrate users from MySQL schema A to Postgres schema B. Map user_id → id, full_name → first_name + last_name.”

Benefit: Migration tasks are 2–3x faster.

5. Data Quality & Validation
Problem: Data validation scripts and anomaly detection rules are often neglected.

AI Solution: AI can generate test cases and quality checks.

Tools: ChatGPT + dbt.

Example Prompt:
“Write SQL checks to validate that zipcode is 5 digits and articles.title is never null. Output in dbt schema test format.”

Benefit: Expands coverage with little extra effort.

6. Security & Compliance
Problem: DBAs must enforce GDPR, HIPAA, and internal security rules — often manually.

AI Solution: AI can create row-level security policies, anonymization scripts, and audit queries.

Example Prompt:
“Generate Postgres row-level security so users can only see their own records.”

Benefit: Faster compliance, reduced risk.

7. Documentation & Knowledge Sharing
Problem: Documentation is often outdated, incomplete, or skipped.
AI Solution: AI can auto-generate data dictionaries, ER diagrams, and tutorials from schemas.

Example Prompt:
“Generate a Markdown data dictionary from this schema. Include data types, constraints, and sample values.”

Benefit: 80% of documentation time eliminated.

Productivity Gains for DBAs Using Generative AI

• Schema prototyping: 50–70% faster
• SQL generation: 60% faster
• Query optimization: 30–40% faster
• Documentation: 80% automated
• Compliance & quality checks: 2–3× coverage

Generative AI transforms DBAs from “firefighters” into strategic enablers of innovation.

Learn More: Upskill with Generative AI
Want to master how to use Generative AI in your daily engineering work?

Check out LearnAI at C# Corner — our hands-on training designed for developers, data engineers, and DBAs who want to stay ahead of the AI curve.
You’ll learn prompt engineering, AI coding, AI database workflows, and production-ready integrations.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Don't worry if you're just getting started. I'll go over the fundamental SQL commands that every novice should know in this blog. These commands will provide you with a solid foundation regardless of whether you are developing a basic application or are just learning about SQL Server.

What is SQL?
SQL stands for Structured Query Language. It is a programming language for storing and processing information in a relational database.

With SQL, you can,

• Create and manage databases
• Add and retrieve data
• Update or delete records
• Control access to data

SQL Commands
1. DDL ( Data Definition Language )
What it does: DDL commands define and modify the structure of database objects like tables, schemas, or databases.

Common DDL Commands

• CREATE
• ALTER
• DROP
• TRUNCATE

Example

Note. DDL commands are auto-committed — once executed, you cannot roll them back.

2. DML – Data Manipulation Language

What it does: DML commands let you insert, update, or delete actual data inside your tables.

Common DML Commands

• INSERT
• UPDATE
• DELETE

Example

Note. Use WHERE carefully, forgetting that it can update or delete every row in the table.

3. DQL – Data Query Language
What it does: DQL is all about retrieving data from the database using queries.

Main DQL Command
SELECT

Example

This is the most-used category for anyone working with reports, dashboards, or APIs.

4. TCL – Transaction Control Language
What it does: TCL commands help manage transactions in SQL. These are useful when you want to ensure multiple operations succeed or fail together.

Common TCL Commands
    BEGIN TRANSACTION
    COMMIT
    ROLLBACK
    SAVEPOINT (optional/advanced)

Example

Best used when making multiple changes that must all succeed or fail together.
5. DCL – Data Control Language

What it does: DCL commands are about access control and permissions in the database.

Common DCL Commands
    GRANT
    REVOKE

Example
Data Control

Helpful for controlling individuals in settings where security is important, such production settings.

Conclusion
Understanding SQL command categories like DDL, DML, DQL, TCL, and DCL makes it much easier to work with databases. Whether you're creating tables, inserting data, running queries, or managing transactions, knowing which command to use and helps you write better and safer SQL.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.