Programming

Who Deleted Rows from SQL Server

Posted on

Want to know who deleted rows from your SQL database? The script below joins up delete transactions with users in order to find out who just deleted something. Because this comes from the transaction log, you’ll only find stuff that is still available in there. This means it will be affected by retention/recovery modes. If […]

Process

Passing Audits with Azure DevOps

Posted on

Azure DevOps (previous Visual Studio Team Services, Visual Studio Online, and Team Foundation Server) has many great features. We talk about the features all the time; source control, continuous integration, release management, task boards, reports! One of my favourite features is how it helps us to pass audits. There are two ways to pass an […]

Programming

SQL Server: Filter and Sort Records from sp_who2

Posted on

Sometimes you need to filter and sort the records you get from sp_who2. You can’t do this directly, but you can use a temporary table to do it. — Create an intermediate table to put all the results CREATE TABLE #spootoo ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME […]

Process

Estimates, #NoEstimates, No Estimates

Posted on

Photo by CEphoto, Uwe Aranas I have said a thing or two about estimates in the past; and it generally leads to no good. What I do sometimes need to do is clarify my perspective, because people don’t get it. As always, definitions are important – so I’m to clarify what I think of when […]

Programming

SQL Availability Groups: Find the Primary Server

Posted on

Here is a quick SQL script that will get back information on SQL availability groups, including the role of each server. This can help you find the primary server in an availability group. IF SERVERPROPERTY (‘IsHadrEnabled’) = 1 BEGIN SELECT AvailabilityGroup.name AS AvailabilityGroupName, ReplicaClusterStates.replica_server_name as ServerName, ReplicaStates.role_desc as ReplicaRole, Listeners.dns_name as DnsName FROM sys.availability_groups_cluster AS […]