The behaviour did change between SQL Server 2008 R2 and SQL Server 2012. The 2008 R2 implementation was inconsistent with the documented 'relaxed FIFO' semantics:
Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce unnecessary deadlocks and blocking.
New lock requests where the requestor does not yet own a lock on the resource become blocked if the requested mode is incompatible with the union of granted requests and the modes of pending requests.
A conversion request becomes blocked only if the requested mode is incompatible with the union of all granted modes, excluding the mode in which the conversion request itself was originally granted.
In 2008 R2, a new Sch-S lock request was granted despite it being incompatible with the union of granted and waiting requests, which might lead to lock starvation. In 2012, the Sch-S lock request is blocked.
The reproduction script below uses regular tables rather than a Service Broker queue:
-- Session 1
CREATE TABLE dbo.LockTest (col1 integer NULL);
INSERT dbo.LockTest (col1) VALUES (1);
BEGIN TRANSACTION;
-- Will hold row-X, Pag-IX, and Tab-IX
INSERT dbo.LockTest (col1) VALUES (2);
-- Session 2
-- Blocked waiting on Sch-M
TRUNCATE TABLE dbo.LockTest;
-- Session 3
-- Takes Sch-S only
-- Not blocked in 2008 R2
SELECT * FROM dbo.LockTest AS LT WITH (READUNCOMMITTED);
In summary, 2008 R2 did not behave as designed. The problem was fixed in SQL Server 2012.
I would suggest you to run a server side trace - for your entire business life cycle. There might be cases where some reports are ran end of month or end of quarter.
So you will need below event in a server side trace 11,13,16,19,33,37,61,162 with these columns 1,6,8,10,11,12,14,27,35,40.
You can filter out stuff that you dont need using sp_trace_setevent (note that it is announced deprecated).
Alternatively you can use Extended events - Tracking SQL Server Database Usage.
Note: Currently in my environment, I am using Server side trace and its a very low impact. Also, I have a job that dumps the profiler info into a table for analysis.
You can have below table structure for loading trace data :
CREATE TABLE [dbo].[login_trace](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](300) NULL,
[HostName] [varchar](300) NULL,
[ApplicationName] [varchar](300) NULL,
[DatabaseName] [varchar](200) NULL,
[LoginName] [varchar](100) NULL,
[StartTime] [datetime] NULL
) ON [PRIMARY]
You can adjust it as per your requirement.
Best Answer
With SQLAudit you cannot track HostName or IP address.
However, there's an alternative method for auditing, based on streaming extended events. I blogged about it earlier this year.
Basically, you treat locks acquired as indicators of the operations being performed on the tables (shared locks and schema stability locks for reads and exclusive locks for writes) and you capture these events with an extended events session. You will need to hook a script (or an executable) to that session using the streaming API and you can implement your own logic to filter, categorize and persist the events you're interested in.
Another possibility is capturing the "Audit Schema Object Access Event" events with a trace and streaming them to your own custom application. Here's a tutorial. In Extended Events the same event is not available, as it is reserved for the SQLAudit implementation.