SQL DBA Blog

  • PowerShell Script: List Files with Sizes and Dates

    PowerShell Script: List Files with Sizes and Dates

    In this blog post, we’ll go through the steps for using a PowerShell script that lists files in a directory along with their sizes and creation dates. This script is useful for disk space and for checking old files in a folder. 

    List Files with Size and Last Write Time

    The following PowerShell script provides admins a quick overview of the files in the current directory, ordered by file size (MB) from highest to lowest.

    PowerShell Script:

    # PowerShell Script: List Files with Size and Last Write Time
$files = Get-ChildItem -File
$fileList = $files | Select-Object Name, LastWriteTime, @{Name="SizeMB"; Expression={[math]::Round($_.Length / 1MB, 2)}}
$sortedFileList = $fileList | Sort-Object -Property SizeMB -Descending
$sortedFileList | Format-Table -AutoSize
    PowerShell show files with sizes & dates in a folder

    How It Works
    > Get-ChildItem: Retrieves all files in the current directory using the -File parameter.
    > Select-Object: Creates a custom object for each file with its name, last write time, and size in megabytes.
    > Sort-Object: Orders the files by size in descending order.
    > Format-Table: Displays the sorted file list in a table format.

    Making It a Function

    For better reusability, we can turn the script into a function: 

    # PowerShell Function: List-FilesWithSizesAndDates
Function List-FilesWithSizesAndDates {
    param(
        [string]$directoryPath = (Get-Location)
    )
    $files = Get-ChildItem -Path $directoryPath -File
    $fileList = $files | Select-Object Name, CreationTime, @{Name="SizeMB"; Expression={[math]::Round($_.Length / 1MB, 2)}}
    $sortedFileList = $fileList | Sort-Object -Property SizeMB -Descending
    $sortedFileList | Format-Table -AutoSize
}

    Example usage: 

    # List files in the current directory
List-FilesWithSizesAndDates

# List files in a specific directory
List-FilesWithSizesAndDates -directoryPath "d:\mssql_backups"
    Create Function in PowerShell for Listing Files

    This function makes it easy to organize files by size and date, helping you quickly find large or old files that might need archiving or deletion. It’s also great for auditing, letting you track file creation dates for better record-keeping. Plus, you can name the function whatever you like, customizing it to fit your workflow and making your terminal experience even smoother.

  • Granting the VIEW SERVER STATE Permission in SQL Server

    Granting the VIEW SERVER STATE Permission in SQL Server

    In SQL Server, the VIEW SERVER STATE permission allows a user to view server-wide dynamic management views (DMVs) and functions. This is often useful for troubleshooting, monitoring, and performance tuning.

    In this blog post, I’ll share how to check which users have the VIEW SERVER STATE permission, and granting this permission to a user.

    1. Query to Check Users with VIEW SERVER STATE Permission

    To verify that the user has been granted this permission, you can query the sys.server_permissions catalog view:

    -- Check who has VIEW SERVER STATE permissions
SELECT 
    grantee.name AS UserName, 
    permission_name, 
    state_desc 
FROM 
    sys.server_permissions AS sp
JOIN 
    sys.server_principals AS grantee 
    ON sp.grantee_principal_id = grantee.principal_id
WHERE 
    permission_name = 'VIEW SERVER STATE';

    This will return all users who have the VIEW SERVER STATE permission. If you are using Always On Availability Groups, you should verify that these permissions have been sync’d between all SQL Replicas.

    2. Granting VIEW SERVER STATE Permissions

    Here is a simple T-SQL example to GRANT this permission to a specific user:

    -- Grant VIEW SERVER STATE permission for user
GRANT VIEW SERVER STATE TO [username];

    Replace the [username] with the name of the Domain User or SQL login you want to grant the permission to.

    To grant this permission, you will need to run as a sysadmin.

  • Why Is the Database in “In Recovery” Mode?

    In this post I’m hoping to help you resolve issues if your SQL Server Database is stuck “in Recovery” Mode.

    SQL Server enters recovery mode to ensure data integrity by:
    > Analyzing Transactions: Identifies changes since the last checkpoint.
    > Redoing Transactions: Reapplies committed changes not yet written to the database files.
    > Undoing Transactions: Rolls back uncommitted transactions.

    This process is automatic but can take time, especially with large databases or extensive transaction logs. However, sometimes we need to run some commands to help things along and recovery your db.

    How to Resolve Database in Recovery Mode

    1. Wait for Recovery to Complete
    Recovery is often just a matter of time and waiting it out. Check the SQL Server Error Log for progress updates, such as:

    Recovery of database 'DatabaseName' is 50% complete. Phase 2 of 3.

    If recovery progress is visible, try avoid interrupting the process. You could try monitor recovery progress by checking currently running processes on the database.

    2. Restore the Database
    If recovery seems stuck or incomplete, manually restore the database using the following command: 

    -- recovery from database in recovery
RESTORE DATABASE [YourDatabaseName] WITH RECOVERY;

    This command finalizes a recovery process. If you encounter issues, ensure no active connections are blocking the restore.

    3. Set the Database Offline and Online

    If recovery fails, try resetting the database state:

    -- Set database offline
ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- Set database online
ALTER DATABASE [YourDatabaseName] SET ONLINE;

    Additional Resources

    By following these steps, you should be able to resolve most issues with databases stuck in “In Recovery” mode (I hope). If the problem persists, consider checking for underlying causes, such as large transaction logs or excessive virtual log files (VLFs). You could also add a comment below and I’ll try assist!

    Hope this was useful. For more details, check out the Microsoft Documentation: Restore and Recovery Overview (SQL Server)

  • How to View the Definition of a Stored Procedure

    In this post, I’m sharing some SQL Scripts that will help you view the definition text of Stored Procedures and other objects in SQL Server.

    Ways to Query Stored Procedure Text

    Like a lot of tasks in MSSQL, there’s various ways we can get this information, including:
    > sys.sql_modules: A catalog view that stores SQL language-defined module definitions, including Stored Procedures, Views, Functions, and Triggers.
    > OBJECT_DEFINITION: A system function that retrieves the definition of a specified object.
    > sp_helptext: A system procedure for listing the text of objects (not supported in Azure Synapse Analytics).

    For this demo, we’ll focus on sys.sql_modules and OBJECT_DEFINITION, as both are supported in Azure and provide reliable results.

    Additionally, you could achieve similar results using PowerShell scripts, but I’ll keep examples purely SQL-based.

    Searching for Stored Procedure Text

    The sys.sql_modules catalog view contains a row for each object that has a SQL language-defined module. This includes:
    – Stored Procedures (P)
    – Replication Filters (RF)
    – Views (V)
    – Triggers (TR)
    – Scalar User-Defined Functions (FN)
    – Inline Table-Valued Functions (IF)
    – Table-Valued Functions (TF)
    – Rules (R)

    Even stand-alone default objects (D) have their definitions stored here. For a complete list of object types, refer to the type column in the sys.objects catalog view.

    Example 1: Searching with sys.sql_modules

    You can search for specific text within stored procedure definitions using this query:

    -- Search for text within stored procedure definitions using sys.sql_modules
SELECT o.name  
FROM sys.sql_modules m  
INNER JOIN sys.objects o  
    ON m.object_id = o.object_id  
WHERE m.[definition] LIKE '%ErrorHandler%'  
AND o.type = 'P';

    This query returns the names of all stored procedures that contain the string ErrorHandler in their definitions.

    📌 Real-Life Scenario: Imagine you’re migrating a database, and you want to identify all procedures referencing a legacy feature like a deprecated table or column. Searching with OBJECT_DEFINITION is a quick way to locate those references.

    Example 2: Using OBJECT_DEFINITION

    Alternatively, you can use the OBJECT_DEFINITION function directly to retrieve an object’s definition. For example:

    -- get an objects definition
USE AdventureWorks2022;  
GO  
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.uspLogError'));

    Or, when combined with sys.sql_modules:

    -- get definition of object
USE AdventureWorks2022;  
GO  
SELECT [definition]  
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID(N'dbo.uspLogError');

    📌 Tip: Use OBJECT_DEFINITION for quick lookups and sys.sql_modules when you need to integrate with other metadata from the catalog view, such as modify_date or is_schema_bound.

    That’s It!

    That’s it for this quick post. For more information, check out the Microsoft documentation on querying stored procedure definitions, which includes T-SQL examples for the other two ways to get object definitions.

  • SQL Script to Show Exclusive Locks on Tables

    In this post I’m sharing a SQL script that will show exclusive locks for all tables in a database, with added info to help you understand what we’re looking at.

    Exclusive locks are a natural thing in SQL Server which ensures data integrity during updates or deletions by preventing other transactions from accessing the locked resource. However, prolonged exclusive locks can cause performance issues, especially when planning maintenance tasks or deploying schema changes.

    Script to Show Exclusive Locks

    The script below identifies and displays details about exclusive locks (and other lock types) on tables within a passed SQL Server database. This helps DBA’s locate and troubleshoot potential blocking or performance issues caused by locked resources.

    Output:
    UserName: The login name of the user holding the lock.
    SessionID: The unique session ID for the locking session.
    HostName: The machine where the session originated.
    ObjectName: The name of the locked object, usually a table.
    ObjectType: The type of the object (e.g., USER_TABLE, SYSTEM_TABLE).
    ResourceType: The resource type being locked (e.g., OBJECT, PAGE, KEY).
    LockStatus: The status of the lock (e.g., GRANTED or WAIT).
    LockType: A descriptive name for the lock mode (e.g., Exclusive, Shared).
    ProgramName: The application responsible for the session (e.g., SSMS, SQLAgent).
    ClientInterface: The interface used to connect (e.g., ODBC Driver, ADO.NET).

    This is a custom script I’ve made while writing this post. I’ll update if I find any faults or useful updates. The sys.dm_tran_locks MS Docs page is particularly useful for further information on understanding each columns meaning.

    Operations Blocked and Allowed with Exclusive Locks

    When a table has an exclusive lock, certain operations are blocked, while others may still be possible depending on the type and level of the lock applied.

    SQL Server uses different lock types, like exclusive (X), shared (S), and intent (I), and lock levels (row, page, or table) to manage concurrency and ensure data integrity. For example, an exclusive lock (X) prevents other transactions from accessing or modifying the locked resource, but the lock’s granularity (row, page, or table) determines if other parts of the table can still be used.

    What Can’t Be Done
    Reading or Writing Data:
    Other transactions cannot modify or acquire shared locks to read the locked data. Using the NOLOCK table hint allows reading data, but this can result in dirty reads (uncommitted data) or inconsistent results.
    Schema Changes:
    ALTER, DROP, or adding the table to replication is blocked due to schema-related locks.
    Truncating the Table:
    TRUNCATE is restricted due to conflicts with schema-level locks that prevent structural changes during a lock.

    What Can Be Done
    Identify Blocking Transactions:
    Use system views like sys.dm_tran_locks or sp_who2 to locate the cause of the block.
    Access Unlocked Parts:
    If the lock is row- or page-specific, other parts of the table may still be accessible for reading or writing.

    Managing Exclusive Locks During Changes

    To minimize the impact of exclusive locks, it’s important to plan changes during off-peak hours and schedule maintenance windows when database activity is low. If you’re struggling to perform a DDL change on a table, consider arranging application downtime to allow the operation to complete without interference. For tasks like adding a table to replication, you can populate the subscriber first and then use the replication support-only option to avoid conflicts, ensuring smoother changes with minimal disruption.

    These strategies should help you maintain performance and reduce the risk of lock contention during critical operations. As a follow-up, you could also be checking the currently running SPIDs on your SQL Server for more information on busy periods.