How to find unsaved file location for SQL query in management studio

Losing unsaved work can be frustrating, especially in SQL Server Management Studio (SSMS). Here's how to recover your work.

Default Location for Unsaved SQL Files

SSMS may store unsaved queries in temporary locations:

• Windows XP:
  C:\Documents and Settings\<YourUsername>\My Documents\SQL Server Management Studio\Backup Files
• Windows Vista / 7 / 10 / 11:

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

Replace <YourUsername> with your actual Windows username.

View Recently Executed SQL Queries

Run this SQL to view recent activity:

USE <Database Name>;
SELECT 
    execquery.last_execution_time AS [Date Time],
    execsql.text AS [Script]
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;

⚠️ Replace <Database Name> with the name of your working database.

Enable AutoRecover in SSMS

1. Open SSMS.
2. Go to Tools > Options.
3. Navigate to Environment > AutoRecover.

Adjust settings like time interval and number of backups to keep.

What Happens on Restart?

SSMS prompts to recover unsaved queries after a crash:

Conclusion

Enable AutoRecover and know where unsaved files go to prevent data loss in SSMS.

Read specific errors using temp table

 Read specific errors using a temp table

Ever need to quickly check recent errors in your SQL Server logs? This handy T-SQL snippet allows you to query the SQL Server 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 SQL Server 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 important events.

create table #t (dt datetime, process varchar(200), msg varchar(1000))
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'
select * from #t where process <> 'logon'

---

Further Reading and Related Topics

For more insights into SQL Server, error handling, and performance monitoring, check out these excellent resources:

• Manufacturer information inside SQL Server - Learn how to dig into hardware details within your SQL Server instance.
• How to use Extended Events to catch all errors in SQL Server - A deeper dive into using Extended Events for comprehensive error capturing.
• SQL Socket & Core information - Understand how SQL Server utilizes your system's sockets and cores.
• Get IO warning from log - Identify potential I/O bottlenecks or issues from your SQL Server logs.
• Get TempDB files count - A quick tip for checking the number of TempDB files.
• How to find SQL Server Cluster information - Discover details about your SQL Server cluster setup.

Query Plan read using T-SQL

How to Analyze SQL Server Execution Plans from a Saved XML File

If you have an execution plan saved as an XML file, here's how you can read and analyze it in SQL Server.

📄 Step 1: Load the Execution Plan File

Use the OPENROWSET function to load your .xml file into a variable.

DECLARE @plancontents VARCHAR(MAX), @xml XML;
SET @plancontents = (
    SELECT * 
    FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData
);

🔍 Step 2: Retrieve Recent Queries Matching a Specific Pattern

You can extract the top 100 recent SQL queries that match a specific text pattern, along with their execution times and plan handles.

SELECT TOP 100 
    execquery.last_execution_time AS [Date Time],
    execsql.TEXT AS [Script],
    execquery.plan_handle
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
WHERE execsql.TEXT LIKE '%WITH Temp_Accounts (%'
ORDER BY execquery.last_execution_time DESC;

🧠 Step 3: Analyze Execution Plan Nodes

Once you have the execution plan XML, you can use XQuery to dig into specific attributes of each node, such as cost estimates and operation types.

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@AvgRowSize', 'nvarchar(max)') AS AvgRowSize,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@Parallel', 'nvarchar(max)') AS Parallel,
    c.value('.[1]/@EstimateRebinds', 'nvarchar(max)') AS EstimateRebinds,
    c.value('.[1]/@EstimateRewinds', 'nvarchar(max)') AS EstimateRewinds,
    c.value('.[1]/@NodeId', 'nvarchar(max)') AS NodeId,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') AS EstimatedTotalSubtreeCost,
    c.value('.[1]/@EstimateRows', 'nvarchar(max)') AS EstimateRows,
    c.value('.[1]/@EstimateIO', 'nvarchar(max)') AS EstimateIO,
    c.value('.[1]/@EstimateCPU', 'nvarchar(max)') AS EstimateCPU,
    c.query('.') AS ExecPlanNode
FROM @xml.nodes('//child::RelOp') AS T(c)
ORDER BY EstimatedTotalSubtreeCost DESC;

🔗 Useful References

• Escape XML Special Characters in SQL Server
• Working with XML and NULL Columns in SQL Server
• Querying XML Data Types in SQL Server
• Tracking CPU Utilization Trends in SQL Server

💡 Final Thoughts

By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance and execution behavior directly from saved plan files. This method is particularly useful when troubleshooting or optimizing complex queries.