How to execute long-execution time queries in SQL Management Studio

Question

How to execute long-execution time queries in SQL Management Studio

Andrew Tabar 271

I have a few queries that I need to run in SQL Server Management Studio that will take a VERY long time to complete, however, the SQL server is in an enclave with VERY aggressive timeouts for RDP sessions. This means that the RDP session times out/logs off the user before the SQL query can complete. Is there a way to start a query in SSMS and have it run in the background or otherwise run it without having to worry about the RDP session being disconnected?

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Joel Esteban Santana Herasme 85

You can configure your queries as SQL Server Agent Jobs, which allows them to run in the background without being tied to your RDP session. Schedule tasks using SQL Server Agent

Andrew Tabar 271 Reputation points

2024-12-17T18:38:24.7866667+00:00
Exactly what I needed. I have a few VERY VERY large tables that need to be truncated and the truncate queries were taking quite a long time to complete. I wrapped the truncate table query in a wrapper that logged how many records were in the table and the start/stop time. It logs to the SQL Server Log using RAISERROR (that command was very confusing because I kept wanting to type it as RAISEERROR). Note that using RAISERROR as the last command means the SQL Agent job will error at the end, so if this has a real error/failure, you'll need to look in the SQL Agent logs for what specifically went wrong.

declare @mycount int; declare @errmsg varchar(100); declare @startime varchar(100); declare @endtime varchar(100); set @startime = RTRIM(CAST(getdate() AS NVARCHAR(100))) set @mycount = (select count(*) from verybigtable) truncate table verybigtable set @endtime = RTRIM(CAST(getdate() AS NVARCHAR(100))) set @errmsg = CONCAT('The count in verybigtable:' ,@mycount, ' Start Time:',@startime, ' End Time:',@endtime); print @errmsg raiserror (@errmsg,16,1) with log;
Andrew Tabar 271 Reputation points

2024-12-17T18:40:50.85+00:00

This worked perfectly. I had some very large tables that needed to be truncated. I wrote some wrapper code that logs the count of records in the table and the start/stop time for the job and logs it to the SQL log using RAISERROR (using RAISERROR was a bit confusing because I kept wanting to type RAISEERROR)

declare @mycount int;

declare @errmsg varchar(100);

declare @startime varchar(100);

declare @endtime varchar(100);

set @startime = RTRIM(CAST(getdate() AS NVARCHAR(100)))

set @mycount = (select count(*) from verybigtable)

truncate table verybigtable

set @endtime = RTRIM(CAST(getdate() AS NVARCHAR(100)))

set @errmsg = CONCAT('The count in verybigtable:' ,@mycount, ' Start Time:',@startime, ' End Time:',@endtime);

print @errmsg

raiserror (@errmsg,16,1) with log;
Andrew Tabar 271 Reputation points

2024-12-26T18:31:48.3766667+00:00

If you don't like raiserror to write to the log, use EXEC xp_logevent, I figured this out after I made the post, this means the job will complete with success instead of an error and will still write something in the SQL Logs for you to see

xp_logevent 60000,@message,informational;

Share via

How to execute long-execution time queries in SQL Management Studio

0 additional answers

Your answer