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.

Get tempdb version store space usage by database

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 is your go-to. It provides a table showing the total tempdb space consumed by version store records for each database.

This view is super efficient and inexpensive to run because it doesn't dig into individual version store records. Instead, it gives you an aggregated view of the version store space used in tempdb on a per-database basis. That makes it perfect for quick checks and monitoring!

Retrieve Version Store Space Usage

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

This query will give you a clear breakdown of the version store space by database, ordered from largest to smallest.

For more SQL Server insights, check out this article on getting your TempDB files count.

You can also find more detailed documentation on sys.dm_tran_version_store_space_usage on the Microsoft Docs website.

Get a count of SQL connections by IP address

Here's a handy SQL query to get an overview of your active SQL Server connections and sessions. It helps you quickly see who is connected, from where, and how many connections they have open. This can be super useful for monitoring activity or troubleshooting connection issues.

SELECT ec.client_net_address
 ,es.[program_name]
 ,es.[host_name]
 ,es.login_name
 ,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);

This query joins two dynamic management views (DMVs):

• sys.dm_exec_connections: Provides information about the physical connections to the SQL Server.
• sys.dm_exec_sessions: Gives details about each active session on the SQL Server.

By grouping and counting, we can easily see the number of connections per unique combination of client IP address, program name, host name, and login name. The OPTION (RECOMPILE) ensures the query plan is recompiled each time it runs, which can be beneficial for DMVs that frequently change data.

Hardware information from SQL Server

This query provides valuable basic hardware information about your database server.

It's important to note that 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 your host machine.

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 [SQL Server Up Time (hrs)],
       virtual_machine_type_desc AS [Virtual Machine Type], 
       softnuma_configuration_desc AS [Soft NUMA Configuration], --SQL Server 2016
       sql_memory_model_desc, --Added in SQL Server 2016 SP1
       container_type_desc -- New in SQL Server 2019
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

For more in-depth information, you can explore the following resources:

• Soft-NUMA (SQL Server)
• sys.dm_os_sys_info