Comprehensive SQL Server Database Audit: Logs, Recovery Models, and Settings

Summary: Use this advanced T-SQL query to audit all databases on your instance. Track Transaction Log usage, Recovery Models, Compatibility Levels, and TDE Encryption status in a single view.

Deep Dive: Auditing SQL Server Database Health and Configuration

Monitoring the health of your databases requires more than just checking if they are "online." A proactive DBA needs to understand the Transaction Log usage, Recovery Models, and critical configuration flags that impact performance and disaster recovery.

The following query provides a powerful, single-pane-of-glass view into every database on your instance, gathering crucial insights from across multiple system views and performance counters.

SQL Database Audit Script

-- Comprehensive Database Property and Log Usage Audit
SELECT 
    db.[name] AS [Database Name],
    SUSER_SNAME(db.owner_sid) AS [Database Owner],
    db.recovery_model_desc AS [Recovery Model],
    db.state_desc AS [State],
    db.log_reuse_wait_desc AS [Log Reuse Wait],
    CONVERT(DECIMAL(18, 2), ls.cntr_value / 1024.0) AS [Log Size (MB)],
    CONVERT(DECIMAL(18, 2), lu.cntr_value / 1024.0) AS [Log Used (MB)],
    CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %],
    db.[compatibility_level] AS [Compatibility],
    db.page_verify_option_desc AS [Page Verify],
    db.is_auto_create_stats_on AS [Auto-Create Stats],
    db.is_auto_update_stats_on AS [Auto-Update Stats],
    db.is_read_committed_snapshot_on AS [RCSI Enabled],
    db.is_auto_close_on AS [Auto-Close],
    db.is_auto_shrink_on AS [Auto-Shrink],
    db.is_query_store_on AS [Query Store],
    db.is_encrypted AS [Is Encrypted],
    de.encryption_state AS [TDE State],
    db.is_accelerated_database_recovery_on AS [ADR Enabled]
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) 
    ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) 
    ON db.name = ls.instance_name
LEFT OUTER JOIN sys.dm_database_encryption_keys AS de WITH (NOLOCK) 
    ON db.database_id = de.database_id
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
    AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
    AND ls.cntr_value > 0
ORDER BY db.[name]
OPTION (RECOMPILE);
        

Why This Matters for Performance

• Log Reuse Wait: If your log is full, this column tells you why. Common reasons include LOG_BACKUP (needs a transaction log backup) or ACTIVE_TRANSACTION (an uncommitted transaction is pinning the log).
• Auto-Close/Auto-Shrink: Red Flag! In production, these should almost always be 0. Auto-close causes performance overhead on every connection, and Auto-shrink causes massive fragmentation.
• Compatibility Level: Ensure your databases are taking advantage of the latest Query Optimizer features by aligning this with your current SQL Server version.
• RCSI Enabled: Read Committed Snapshot Isolation can significantly reduce locking and blocking in highly concurrent environments.

---

This query leverages two key Dynamic Management Views (DMVs) for its data:

• sys.dm_os_performance_counters: Provides real-time log usage data.
• sys.dm_database_encryption_keys: Essential for tracking the progress of Transparent Data Encryption (TDE) operations.

Is your Transaction Log out of control? Identifying the Log Reuse Wait Description is the first step toward fixing log growth issues and avoiding "Disk Full" errors!

SQL Server Extended Properties: How to Query Metadata for All Database Object

Summary: A comprehensive guide and T-SQL script library for retrieving extended properties across SQL Server objects, including tables, columns, indexes, schemas, and Service Broker components.

Retrieving Extended Properties in SQL Server

Extended properties are a powerful way to store metadata (like descriptions, versions, or owner info) directly within your database. This post provides a complete library of scripts to query sys.extended_properties for every major database object.

---

1. Objects and Columns (Class 1)

This query pulls properties for tables, views, stored procedures, and specific columns.

SELECT 
  CASE 
    WHEN ob.parent_object_id > 0
    THEN OBJECT_SCHEMA_NAME(ob.parent_object_id) + '.' + OBJECT_NAME(ob.parent_object_id) + '.' + ob.name
    ELSE OBJECT_SCHEMA_NAME(ob.object_id) + '.' + ob.name
  END + CASE 
    WHEN ep.minor_id > 0
    THEN '.' + col.name
    ELSE ''
  END AS [Path]
  ,ep.name AS [Property Name]
  ,ep.value AS [Property Value]
FROM sys.extended_properties ep
INNER JOIN sys.objects ob ON ep.major_id = ob.OBJECT_ID AND class = 1
LEFT OUTER JOIN sys.columns col ON ep.major_id = col.Object_id 
  AND class = 1 AND ep.minor_id = col.column_id;
        

2. Indexes (Class 7)

SELECT 
  OBJECT_SCHEMA_NAME(ob.object_id) + '.' + OBJECT_NAME(ob.object_id) + '.' + ix.name AS [Index Path],
  ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.objects ob ON ep.major_id = ob.OBJECT_ID AND class = 7
INNER JOIN sys.indexes ix ON ep.major_id = ix.Object_id 
  AND class = 7 AND ep.minor_id = ix.index_id;
        

3. Schemas (Class 3) and Database (Class 0)

-- Schemas
SELECT sch.name AS [Schema], ep.name, ep.value
FROM sys.extended_properties ep
INNER JOIN sys.schemas sch ON class = 3 AND ep.major_id = SCHEMA_ID;

-- Database Level
SELECT DB_NAME() AS [Database], ep.name, ep.value
FROM sys.extended_properties ep
WHERE class = 0;
        

4. Service Broker & Specialized Objects

Retrieving properties for Routes, Services, and Message Types.

-- Routes (Class 19)
SELECT rt.name, ep.name, ep.value
FROM sys.extended_properties ep
INNER JOIN sys.routes rt ON class = 19 AND ep.major_id = rt.route_id;

-- Services (Class 17)
SELECT sv.name COLLATE DATABASE_DEFAULT, ep.name, ep.value
FROM sys.extended_properties ep
INNER JOIN sys.services sv ON class = 17 AND ep.major_id = sv.service_id;
        

Quick Reference: Class ID Table

When writing custom queries against sys.extended_properties, use these class IDs:

Class ID	Object Type
0	Database
1	Object or Column
2	Parameter
3	Schema
7	Index
10	XML Schema Collection

---

Further Reading

• sys.extended_properties (Microsoft Docs)
• Building Database Documentation with Metadata

Pro Tip: Use Extended Properties to store 'Data Sensitivity' or 'Owner' tags to automate your compliance auditing and data cataloging!

How to Add Descriptions to SQL Server Tables and Columns using sp_addextendedproperty

Summary: Learn the T-SQL syntax for adding metadata descriptions to SQL Server tables and columns using sys.sp_addextendedproperty to improve database documentation.

Documenting Your Database: Adding Comments to SQL Server Objects

Adding descriptive comments to your SQL Server database objects is a best practice for long-term maintenance. These comments (extended properties) provide immediate context to developers and analysts, often appearing directly in tools like SQL Server Management Studio (SSMS).

---

How to Comment on a Table

Use the script below to define a description for a specific table at the schema level.

-- Add a description to a table
EXEC sys.sp_addextendedproperty   
 @name = N'MS_Description', 
 @value = N'Contains customer-related data.',   
 @level0type = N'SCHEMA',    
 @level0name = 'dbo',  
 @level1type = N'TABLE',      
 @level1name = 'tblCustomer';  
GO
        

How to Comment on a Column

To add a comment to a specific column, you must define the hierarchy down to level2.

-- Add a description to a column
EXEC sys.sp_addextendedproperty   
 @name  = N'MS_Description',
 @value = N'Primary key identifier for the customer.',  
 @level0type = N'Schema',  
 @level0name = 'dbo',  
 @level1type = N'Table',   
 @level1name = 'tblCustomer',   
 @level2type = N'Column',  
 @level2name = 'CustomerID';  
GO
        

---

Pro Tip: Using the name 'MS_Description' is standard; this allows the text to automatically appear in the "Description" field within the SSMS Table Designer and Object Explorer properties.

For more in-depth guidance on querying all existing comments across SQL Server objects, visit the reference below:

🔗 Find All Comments in SQL Server – Detailed Guide

SQL Server Script to Drop and Recreate All Foreign Keys for Database Resets

Summary: Use these T-SQL scripts to automate the dropping and recreating of foreign key constraints. This is essential for resetting database contents, performing bulk data loads, or reseeding identities.

Automating Foreign Key Management for Database Resets

Maintaining ready-to-use scripts for resetting database contents is a lifesaver for dev and QA environments. When you need to truncate tables or reseed identities, foreign key constraints often get in the way.

The following workflow captures your existing FK metadata into a temporary table, generates "Drop" scripts, and prepares "Recreate" scripts so you can restore your constraints instantly after your data cleanup.

---

1. Prepare Metadata Storage

We start by ensuring our temporary workspace is clean and then capture the complex relationships, including multi-column keys.

-- Drop temp table if it exists
IF OBJECT_ID('tempdb..#Fkey') IS NOT NULL
    DROP TABLE #Fkey;

-- Capture Foreign Key Information
;WITH cte AS (
    SELECT 
        fkc.constraint_column_id AS consColumn,
        fk.NAME AS foreignKeyName,
        parentSchema.name AS parentSchema,
        parentTable.NAME AS parentTableName,
        parent_col.NAME AS parentColName,
        refSchema.name AS refSchema,
        refTable.NAME AS refTableName,
        ref_col.NAME AS refColName
    FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.tables parentTable ON parentTable.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas parentSchema ON parentSchema.schema_id = parentTable.schema_id
    INNER JOIN sys.columns parent_col ON fkc.parent_column_id = parent_col.column_id 
        AND parent_col.object_id = parentTable.object_id
    INNER JOIN sys.tables refTable ON refTable.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas refSchema ON refSchema.schema_id = refTable.schema_id
    INNER JOIN sys.columns ref_col ON fkc.referenced_column_id = ref_col.column_id 
        AND ref_col.object_id = refTable.object_id
    WHERE parentTable.type = 'U' AND refTable.type = 'U'
)
SELECT DISTINCT 
    foreignKeyName,
    parentSchema,
    parentTableName,
    SUBSTRING((
        SELECT ',' + a.parentColName
        FROM cte a
        WHERE a.foreignKeyName = c.foreignKeyName
        ORDER BY a.consColumn
        FOR XML PATH('')
    ), 2, 300000) AS parentColName,
    refSchema,
    refTableName,
    SUBSTRING((
        SELECT ',' + b.refColName
        FROM cte b
        WHERE b.foreignKeyName = c.foreignKeyName
        ORDER BY b.consColumn
        FOR XML PATH('')
    ), 2, 300000) AS refColName
INTO #Fkey
FROM cte c;
        

2. Generate "DROP" Scripts

Run the query below and copy the results to safely remove constraints.

SELECT DISTINCT 
    'IF EXISTS (SELECT * FROM sys.foreign_keys 
        WHERE object_id = OBJECT_ID(N''[' + parentSchema + '].[' + foreignKeyName + ']'') 
        AND parent_object_id = OBJECT_ID(N''[' + parentSchema + '].[' + ParentTableName + ']''))
    ALTER TABLE [' + parentSchema + '].[' + ParentTableName + '] DROP CONSTRAINT [' + foreignKeyName + ']' 
AS foreignKey_drop_script
FROM #Fkey;
        

3. Generate "RECREATE" Scripts

After your data operations are complete, run these generated scripts to restore referential integrity.

SELECT DISTINCT 
    'ALTER TABLE [' + parentSchema + '].[' + ParentTableName + '] WITH CHECK 
    ADD CONSTRAINT [' + foreignKeyName + '] FOREIGN KEY(' + parentColName + ') 
    REFERENCES [' + refSchema + '].[' + refTableName + '](' + refColName + ')' 
AS Add_constraints_script
FROM #Fkey;
        

---

Additional Resources:

• How to Find SQL Server Object Dependency
• Identifying Missing Indexes in SQL Server
• Optimizing Foreign Key Performance

Working with Truncate? Remember that TRUNCATE TABLE is not allowed if a table is referenced by a Foreign Key, even if the table is empty. Use these scripts to drop them first!

SQL Server Database Snapshots: Creation and Restoration Guide (SQL 2016+)

Summary: Learn how to create and restore SQL Server Database Snapshots. This guide provides the T-SQL syntax for static, read-only snapshots and explains how to revert a database to a specific point in time.

Working with SQL Server Database Snapshots

Starting with SQL Server 2016 SP1, the Database Snapshot feature is supported across all editions, including Standard and Express.

A database snapshot provides a static, read-only view of a database at a specific point in time. It uses a "copy-on-write" mechanism: it doesn't store a full copy of the data initially, but as pages in the source database are modified, the original versions of those pages are copied into the snapshot's sparse file.

---

Creating a Database Snapshot

The snapshot must reside on the same server instance as the source database. The .ss extension is a common convention, but any extension is technically valid.

-- Generic Syntax
CREATE DATABASE <database_snapshot_name>
ON (
  NAME = <logical_file_name>, 
  FILENAME = '<os_file_name>'
) [ ,...n ]
AS SNAPSHOT OF <source_database_name>;

-- Example Implementation
CREATE DATABASE AdventureWorks_dbss1800 ON  
( NAME = AdventureWorks, FILENAME =  
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )  
AS SNAPSHOT OF AdventureWorks;  
GO
        

Restoring a Database from a Snapshot

You can quickly revert your source database to the state it was in when the snapshot was taken. This is much faster than a traditional restore but requires that no other snapshots exist on the same database.

-- Reverting the database
RESTORE DATABASE <database_name> 
FROM DATABASE_SNAPSHOT = <database_snapshot_name>;
GO
        

Critical Considerations

• Read-Only: Snapshots cannot be modified. They are ideal for reporting or "pre-deployment" safety nets.
• Sparse Files: The snapshot file starts small and grows as the source database changes. Monitor disk space closely!
• Dependency: If the source database goes offline or the data files are corrupted, the snapshot becomes useless. It is not a replacement for a full backup strategy.

---

Further Reading:

• Snapshots vs. Backups: Which should you use?
• Official Microsoft Documentation: CREATE DATABASE SNAPSHOT

Pro Tip: Snapshots are perfect for protecting against "Fat Finger" errors during major data updates. Take a snapshot, run your scripts, and if something goes wrong, you can revert in seconds!

Fix SSIS Error 0xC0202092: Handling CSV Text Qualifiers and Double Quotes

Summary: Troubleshooting SSIS Flat File Source errors (DTS_E_PRIMEOUTPUTFAILED) when importing CSV files with complex text qualifiers. Learn how to adjust Connection Manager properties to resolve fatal pipeline execution errors.

Resolving SSIS CSV Import Errors with Text Qualifiers

Importing CSV files into SQL Server via SSIS is usually straightforward, but "dirty" data—specifically strings enclosed in double quotes that contain nested quotes or commas—can trigger fatal pipeline errors.

---

The Problem: Sample CSV Data

In the example below, row four contains a nested quote within the text qualifier, which often confuses the SSIS parser:

"090","Grey",""
"091","Grey, Red",""
"092","White/Teal",""
"042","Blue ","(\"is not blue\" )"  -- Nested quotes causing parser failure
"093","Light Grey",""
        

Common Error Message

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. 
The PrimeOutput method on Flat File Source returned error code 0xC0202092. 
This error is fatal and stops pipeline execution.
        

---

The Solution: Adjusting Connection Manager Properties

When the automatic parser fails to identify the boundaries of a column due to conflicting qualifiers, you can manually override the behavior in the Flat File Connection Manager.

Step 1: Navigate to the Advanced Tab and select the problematic column.

Step 2: Locate the TextQualified property and set it to False.

By setting this to false, SSIS treats the qualifiers as literal data, which prevents the PrimeOutput method from crashing when it encounters unexpected quote patterns.

Note: If you set TextQualified to False, the double quotes will be imported into your database table. You can easily remove them later using a REPLACE(Column, '"', '') command in SQL or a Derived Column transformation in SSIS!

SQL Server PIVOT and UNPIVOT Tutorial: Syntax and Practical Examples

Summary: Master the SQL Server PIVOT and UNPIVOT operators. This guide provides the general syntax and real-world examples using AdventureWorks to transform rows into columns and back again.

SQL Server PIVOT and UNPIVOT: Transforming Data

The PIVOT operator in SQL Server is a powerful tool for data analysis. It allows you to rotate a table-valued expression by turning unique values from one column into multiple columns in the output, while performing aggregations on any remaining column values.

---

1. General PIVOT Syntax

Understanding the structure is key to mastering pivot operations. The query requires a source subquery, an aggregate function, and an IN clause for the new headers.

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that retrieves the data>) AS <source alias>  
PIVOT  
(  
    <aggregate function>(<column to aggregate>)  
    FOR [<column with values to become headers>]  
    IN ([first pivoted column], [second pivoted column], ..., [last pivoted column])  
) AS <pivot table alias>  
<optional ORDER BY clause>;
        

2. Basic PIVOT Example

This example calculates the average cost of products, sorted by the number of days it takes to manufacture them.

-- This example creates a pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,  
[0], [1], [2], [3], [4]  
FROM  
(SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  
) AS PivotTable;
        

3. Advanced PIVOT Query

Using the AdventureWorks database to count purchase orders per employee, pivoted by specific Employee IDs.

USE AdventureWorks2014;  
GO  
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  
FROM  
(SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) AS p  
PIVOT  
(  
COUNT(PurchaseOrderID)  
FOR EmployeeID IN ([250], [251], [256], [257], [260])  
) AS pvt  
ORDER BY pvt.VendorID;
        

4. The UNPIVOT Operation

The UNPIVOT operator performs the opposite operation, transforming columns back into rows for normalized reporting.

-- Creating the base table and inserting sample values
CREATE TABLE #pvt (
    VendorID INT, 
    Emp1 INT, 
    Emp2 INT, 
    Emp3 INT, 
    Emp4 INT, 
    Emp5 INT
);  

INSERT INTO #pvt VALUES (1,4,3,5,4,4), (2,4,1,5,5,5), (3,4,3,5,4,4);  

-- Applying the UNPIVOT operator
SELECT VendorID, Employee, Orders  
FROM  
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM #pvt) AS p  
UNPIVOT  
(Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)) AS unpvt;  
GO
        

---

Need Dynamic Columns? Standard PIVOT requires hardcoded values in the IN clause. For variable headers, you'll need to use Dynamic SQL to build the column list at runtime.