How to Recover Unsaved SQL Queries in SSMS (SQL Server Management Studio)

Summary: Lost a SQL script? Learn how to recover unsaved queries in SSMS using AutoRecover files, temporary system folders, and T-SQL script execution history.

How to Recover Unsaved Work in SQL Server Management Studio (SSMS)

Losing unsaved work can be incredibly frustrating, especially after spending hours tuning a complex script in SQL Server Management Studio (SSMS). Fortunately, SSMS has built-in safeguards. Here’s how you can find and recover your lost SQL files.

1. Default Location for Unsaved SQL Files

If SSMS crashes, it often saves temporary copies of your open queries. Depending on your version of Windows, check these directories:

• Windows Vista / 7 / 10 / 11:

%USERPROFILE%\Documents\SQL Server Management Studio\Backup Files
OR
%USERPROFILE%\AppData\Local\Temp

• Windows XP:

C:\Documents and Settings\<YourUsername>\My Documents\SQL Server Management Studio\Backup Files

2. View Recently Executed SQL Queries via T-SQL

If you executed the script before losing it, the code might still be in the SQL Server Plan Cache. Run the following query to retrieve recently executed scripts:

USE <Database_Name>;
GO

SELECT 
    execquery.last_execution_time AS [Date_Time],
    execsql.text AS [Script_Content]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC;
        

Note: Replace <Database_Name> with the database you were targeting.

3. How to Enable AutoRecover in SSMS

To prevent future data loss, ensure AutoRecover is properly configured. This feature automatically saves your scripts at set intervals.

1. Open SSMS.
2. Go to Tools > Options.
3. Navigate to Environment > AutoRecover.
4. Set "Save AutoRecover information every" to 5 minutes (or your preferred interval).

4. What Happens on Restart?

When SSMS restarts after an unexpected shutdown, it will typically present a "Recover Unsaved Queries" dialog. Always review this list before closing the window to ensure your work is restored.

Conclusion

By keeping AutoRecover enabled and knowing how to query the sys.dm_exec_query_stats DMV, you can significantly reduce the risk of losing critical T-SQL scripts. Always remember to save your work frequently (Ctrl+S) as a best practice!

Found this SQL tip helpful? Share it with your team or subscribe for more SQL Server administration guides!

How to Search SQL Server Error Logs Using T-SQL and Temp Tables

Summary: Learn how to efficiently query the SQL Server Error Log using sp_readerrorlog and temporary tables to filter out noise and identify critical system errors.

Read Specific SQL Server Errors Using a Temp Table

Ever need to quickly check recent errors in your SQL Server logs without scrolling through thousands of lines in the Log File Viewer? This handy T-SQL snippet allows you to query the error log and gather information about various processes and messages. It's a great way to get a snapshot of what's been happening in your environment.

Querying SQL Server Error Logs

The following SQL code creates a temporary table and populates it by reading the last seven SQL Server error logs (from 0 to 6). It then selects all entries where the process is not 'logon', helping you filter out routine login messages and focus on actual errors or system events.

-- Create a temp table to hold log data
CREATE TABLE #t (dt DATETIME, process VARCHAR(200), msg VARCHAR(1000));

-- Populate the table by reading current and archived logs
INSERT INTO #t EXEC sp_readerrorlog 0, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 1, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 2, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 3, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 4, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 5, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 6, 1, 'error';

-- Filter results to ignore routine logon messages
SELECT * 
FROM #t 
WHERE process <> 'logon'
ORDER BY dt DESC;

-- Cleanup
DROP TABLE #t;
        

---

Found this T-SQL tip helpful? Share it with your fellow DBAs or subscribe for more SQL Server troubleshooting guides!

Monitor Active SQL Server Connections and Sessions using T-SQL

Summary: Quickly audit your SQL Server environment with this T-SQL script that groups active connections by IP address, application name, and login to identify resource usage and connection leaks.

Monitoring Active SQL Server Connections and Sessions

Managing server load requires a clear understanding of who is connected to your database and what applications they are using. Here's a handy SQL query to get an overview of your active SQL Server connections and sessions. It helps you quickly see the source IP, the client hostname, and the total connection count per user. This is an essential tool for monitoring server activity or troubleshooting connection leaks.

SELECT 
    ec.client_net_address AS [IP Address],
    es.[program_name] AS [Application],
    es.[host_name] AS [Client Host],
    es.login_name AS [Login],
    COUNT(ec.session_id) AS [Connection Count]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) 
    ON es.session_id = ec.session_id
GROUP BY 
    ec.client_net_address,
    es.[program_name],
    es.[host_name],
    es.login_name
ORDER BY 
    ec.client_net_address,
    es.[program_name]
OPTION (RECOMPILE);
        

How the Query Works

This script performs an inner join between two critical Dynamic Management Views (DMVs):

• sys.dm_exec_connections: Provides information about the physical transport layer and the client IP addresses.
• sys.dm_exec_sessions: Contains high-level metadata about each session, such as the authenticated user and the application name.

By grouping the data, you can pinpoint exactly which application or user is consuming the most connection slots. The OPTION (RECOMPILE) hint ensures that SQL Server generates a fresh execution plan every time you run the script, which is ideal for querying volatile system views that change every second.

Conclusion

Regularly auditing your connections helps identify unauthorized access and application-level bugs where connections aren't being closed properly. For a deeper dive into SQL Server management, ensure your applications are utilizing connection pooling correctly to avoid hitting session limits.

Found this SQL monitoring script useful? Share it with your DBA team or subscribe for more T-SQL performance tuning guides!

How to Check TempDB Version Store Usage per Database in SQL Server

Summary: Use the sys.dm_tran_version_store_space_usage DMV to monitor how much TempDB space is consumed by version store records for Snapshot Isolation and RCSI.

Monitor Version Store Space Usage in TempDB

Looking to understand how much space the version store is using in your SQL Server tempdb? The sys.dm_tran_version_store_space_usage dynamic management view (DMV) is your primary tool for troubleshooting 1458 errors or general TempDB growth. It provides a summarized view of the total space consumed by version store records for each database on your instance.

This view is highly efficient and inexpensive to run because it does not traverse individual version store records. Instead, it provides an aggregated count of the pages used, making it an ideal choice for real-time monitoring and performance dashboards.

Retrieve Version Store Space Usage

Run the following T-SQL script to identify which database is responsible for the largest amount of version store data in TempDB:

SELECT 
    DB_NAME(database_id) AS [Database Name],
    reserved_page_count AS [Reserved Page Count],
    reserved_space_kb / 1024 AS [Version Store Space (MB)]
FROM sys.dm_tran_version_store_space_usage WITH (NOLOCK)
ORDER BY [Version Store Space (MB)] DESC
OPTION (RECOMPILE);
        

This query returns a clear breakdown of space usage, allowing you to quickly pinpoint which database has long-running transactions or high-volume updates under Snapshot Isolation or Read Committed Snapshot Isolation (RCSI).

Why Version Store Usage Matters

The version store is used to support row versioning features. If a transaction remains open for a long time, SQL Server cannot clean up the version store in tempdb, which can lead to disk space exhaustion. Monitoring this DMV helps DBAs proactively manage server health.

---

Related Reading:

• For deep technical details, visit the official Microsoft Documentation.

Found this T-SQL monitoring tip helpful? Share it with your team or subscribe for more SQL Server performance tuning guides!

How to Get SQL Server Hardware Information using sys.dm_os_sys_info

Summary: Use this T-SQL script to extract critical hardware metadata from SQL Server, including CPU core counts, physical memory, NUMA configuration, and virtualization type.

Retrieve SQL Server Hardware and System Information

This query provides valuable hardware information about your database server, which is essential for licensing audits, performance tuning, and capacity planning.

Pro Tip: A 'HYPERVISOR' value for 'virtual_machine_type_desc' doesn't automatically confirm SQL Server is running inside a virtual machine. This merely indicates that a hypervisor is present on the host machine (which could also mean the host has Hyper-V enabled).

Hardware Configuration Script

SELECT 
    cpu_count AS [Logical CPU Count], 
    scheduler_count, 
    (socket_count * cores_per_socket) AS [Physical Core Count], 
    socket_count AS [Socket Count], 
    cores_per_socket, 
    numa_node_count,
    physical_memory_kb / 1024 AS [Physical Memory (MB)], 
    max_workers_count AS [Max Workers Count], 
    affinity_type_desc AS [Affinity Type], 
    sqlserver_start_time AS [SQL Server Start Time],
    DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [Up Time (hrs)],
    virtual_machine_type_desc AS [Virtual Machine Type], 
    softnuma_configuration_desc AS [Soft-NUMA Config], -- SQL Server 2016+
    sql_memory_model_desc, -- SQL Server 2016 SP1+
    container_type_desc -- SQL Server 2019+
FROM sys.dm_os_sys_info WITH (NOLOCK) 
OPTION (RECOMPILE);
        

Understanding the Results

By querying sys.dm_os_sys_info, you get a snapshot of the resources SQL Server "sees." This is particularly useful for identifying:

• Memory Model: Whether SQL Server is using conventional memory or "Locked Pages in Memory."
• Core Density: The ratio of physical cores to sockets.
• Soft-NUMA: Whether SQL Server is automatically managing NUMA nodes to improve performance on high-core-count machines.

---

Further Reading:

• Soft-NUMA (SQL Server) Official Guide
• sys.dm_os_sys_info Documentation

Need to tune your SQL Server performance? Check out our other scripts for monitoring CPU and memory usage!

How to Identify System Manufacturer and Model via SQL Server Error Log

Summary: Use the xp_readerrorlog extended stored procedure to quickly retrieve hardware manufacturer and model details directly from the SQL Server startup sequences.

Identify System Manufacturer and Model from the SQL Server Error Log

Did you know you can quickly pinpoint your SQL Server's system manufacturer and model number directly from its error log? This is a highly efficient way to gather hardware details or verify server specifications without needing administrative access to the underlying Windows OS or BIOS.

Using a Simple SQL Query

When SQL Server starts, it logs several environmental details. You can use the following command to filter the current log for these specific entries:

-- Search the current error log for system hardware info
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
        

This command searches the current SQL Server error log (Log 0) for entries containing the word "Manufacturer." The output typically returns a line detailing the system's make (e.g., Dell, HP, VMware) and the specific model number.

Important Considerations

• Log Recycling: This query only yields results if the error log hasn't been recycled (restarted) since the SQL Server instance last started. If the log has cycled, you may need to change the first parameter from 0 to 1, 2, etc., to search archived logs.
• Virtualization: This method is excellent for confirming if your SQL Server is virtualized. Virtual environments like VMware or Azure will clearly state "VMware, Inc." or "Microsoft Corporation" with "Virtual Machine" in the model details.

---

Need to audit your hardware? Use this script as a quick win for your next server inventory report!

How to Check Database Access Permissions in SQL Server using HAS_DBACCESS

Summary: Quickly identify which databases on a SQL Server instance you have permission to connect to using the HAS_DBACCESS function.

Check Which Databases You Can Access in SQL Server

Ever wonder which databases you actually have permission to enter on a shared instance? Instead of clicking through every database in Object Explorer and waiting for "Access Denied" errors, you can run a simple script to audit your permissions at a glance.

Using the HAS_DBACCESS Function

The HAS_DBACCESS function returns a 1 if the user has access to the database, a 0 if the user does not have access, and NULL if the database name is invalid.

-- List all databases and check current user access
SELECT 
    name AS [Database Name], 
    CASE 
        WHEN HAS_DBACCESS(name) = 1 THEN 'Yes' 
        ELSE 'No' 
    END AS [Has Access]
FROM sys.databases
ORDER BY name;
        

Why Use This?

This statement is particularly handy for a quick security check. It helps you verify:

• If your login has been correctly mapped to a specific database.
• Which databases are visible but restricted to your current credentials.
• Connectivity status across a large instance with dozens of databases.

Working on security auditing? Combine this with our other scripts for a full view of your SQL Server environment!

Retrieve SQL Server Hardware and Registry Details via T-SQL

Summary: Learn how to use xp_instance_regread to query the Windows Registry for hardware details, including VMware PVSCSI parameters, BIOS release dates, and CPU specifications.

Querying Hardware and Registry Details from SQL Server

Ever wonder about the nitty-gritty details of your SQL Server's underlying hardware? While DMVs provide great data, sometimes peeking into the Windows Registry via T-SQL can give you specific environmental insights. Here is how to pull essential hardware information using xp_instance_regread.

1. VMware Storage Driver Parameters (PVSCSI)

For those running SQL Server on VMware, tuning your storage driver settings is critical for high-throughput I/O. You can check the current parameters for your PVSCSI adapter directly:

-- Check PVSCSI driver parameters
EXEC sys.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'SYSTEM\CurrentControlSet\services\pvscsi\Parameters\Device', 
    N'DriverParameter';
        

Performance Note: For intensive workloads, VMware often recommends setting RequestRingPages=32 and MaxQueueDepth=254. For more details, see the official VMware KB article.

---

2. Discovering BIOS Release Date

Identifying the BIOS release date is a quick way to determine if a host needs a firmware update to patch stability or security issues. Run this command to pull it from the registry:

-- Retrieve BIOS release date
EXEC sys.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'HARDWARE\DESCRIPTION\System\BIOS', 
    N'BiosReleaseDate';
        

---

3. Identifying Processor Details

Understanding exactly which CPU is powering your instance is fundamental for SQL Server licensing and performance tuning. This command grabs the full processor string:

-- Get CPU Model and Speed
EXEC sys.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 
    N'ProcessorNameString';
        

For deep dives into processor selection and its impact on SQL performance, I highly recommend checking out Glenn Berry's blog on Processor Selection.

Found this SQL monitoring script useful? Share it with your DBA team or subscribe for more T-SQL performance tuning guides!

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!

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 VLF Monitoring: How to Identify and Manage Virtual Log Files

Summary: Learn how to monitor Virtual Log Files (VLFs) in SQL Server using sys.dm_db_log_info. Discover why high VLF counts slow down database recovery and how to query for bottlenecks.

Monitoring Virtual Log Files (VLFs) for SQL Server Performance

Understanding and monitoring Virtual Log Files (VLFs) is a critical task for any Database Administrator. While the physical transaction log appears as a single file, SQL Server internally segments it into these smaller units.

---

What are Virtual Log Files (VLFs)?

VLFs are internal divisions within the transaction log. SQL Server manages the truncation and reuse of the log at the VLF level. You can explore the detailed physical architecture in the Official Documentation.

Why VLF Count Matters

An excessive number of VLFs (often caused by frequent, small log file growth increments) can lead to significant performance issues:

• Slower Database Recovery: SQL Server must initialize every VLF during startup or crash recovery.
• Extended Restore Times: High VLF counts add overhead to backup and restore operations.
• Log Cleansing Latency: It can impact features like Transactional Replication or Change Data Capture (CDC).

---

Querying VLF Information

To identify databases with a high VLF count across your entire instance, use the following T-SQL script. This leverages CROSS APPLY to aggregate data for every database.

-- Identify databases with high VLF counts
SELECT [name] AS [Database Name]
 ,[VLF Count]
FROM sys.databases AS db WITH (NOLOCK)
CROSS APPLY (
 SELECT file_id
 ,COUNT(*) AS [VLF Count]
 FROM sys.dm_db_log_info(db.database_id)
 GROUP BY file_id
 ) AS li
ORDER BY [VLF Count] DESC
OPTION (RECOMPILE);
        

To inspect the detailed properties (status, size, and offset) of VLFs for your **current database**, run this simplified query:

-- Detailed VLF info for current database
SELECT * FROM sys.dm_db_log_info(DB_ID());
        

---

Technical Reference:

For more in-depth information about the metadata returned by this function, refer to: sys.dm_db_log_info (Transact-SQL) .

Pro Tip: If your VLF count is in the thousands, consider shrinking the log file and manually growing it in larger chunks (e.g., 8GB or 16GB) to create a healthier, more manageable VLF structure.