For completeness, here is the T-SQL of the SQL Server Agent Job that I created from Michael Green's answer:
print 'checking for the evil query'
declare @query nvarchar(max);
declare @spid smallint;
declare @spid_str nvarchar(6);
declare @sql nvarchar(1000)
@query = s.text,
@spid = r.session_id
from sys.dm_exec_requests as r
cross apply sys....
Given the evil query is long-running you don't have to stress out about trapping it synchronously with its execution. It would be good if the approach was relatively lightweight and easy to remove.
I'd go with an agent job, executed on a schedule likely to overlap with the evil query. Once every few minutes should suffice given the evil query runs for thirty ...
Here is how to do it without having to use a scalar UDF. This avoids the pitfalls that Jason Long mentions in his answer (which was very well written, btw). The tradeoff here is that the SQL is not as readable.
Here are the pros and cons of my method.
no new UDFs created
simplifies index creation
simpler index declaration SQL
able to create index ...
These users form a graph. They are linked by edges to common attributes (email, device etc.). The question boils down to finding disjoint subgraphs within the data, and assigning each a label.
There's an answer on SO which addresses this. The web will have many more if you care to search.
SQL Server 2019 introduced SHORTEST_PATH() which would simplify the ...
I fully agree with Grant's comments. You have some requirement to "audit" something, so you need to meet up to those requirements. Just saying "audit" isn't enough, you have to tell them to be more specific.
If they don't care enough to have a discussion, then you can argue that you already have an auditing solution - SQL Server logs ...
Default trace is just a collection of behavioral information about the system. It's not the same as auditing. They do different things and support different purposes. One doesn't supplant the other.
Further, default trace is actually inferior to another thing running on all systems by default, the system_health Extended Events session. In terms of ...
This will be somewhat an opinion answer, so likely to be downvoted.
First up, in-place upgrades are fraught with issues. So many things can go wrong along the way and you have no fallback position except a restore. This means you must first ensure that at every step you have a restore option, for the databases and the OS. However, I recognize that not ...
I am trying to set up multi-factor authentication in SSMS using an on-premesis SQL Server (Standard Edition).
"Active Directory - Universal with MFA Support" is an Azure Active Directory authentication method. SQL Server (on premises) doesn't support Azure Active Directory Authentication; it only supports SQL Auth and Windows Auth (NTLM and ...
SQL Buffer pool is consuming more memory in sql server 2017.
That is by design.
The default memory management behavior of the SQL Server Database
Engine is to acquire as much memory as it needs without creating a
memory shortage on the system. The SQL Server Database Engine does
this by using the Memory Notification APIs in Microsoft Windows.
I just tried using the XML index creation commands in-lined with a table creation, and it didn't work.
I stumbled upon this article that makes it look like trying to create non-relational indexes (XML, columnstore, or spatial) doesn't work with in-lining the index creation statement in the table definition.
If you create a FUNCTION and query the sysjobhistory table in MSDB you can get a simple response on the most recent job execution.
Create the function:
CREATE FUNCTION [dbo].[fnGetLatestAgentJobExitCode]
SELECT TOP (1)
WHEN 0 THEN '...