3

Scenario:

We write online banking software and for the time being (due to legacy design decisions) are using Quartz 2.2 right now as a queue system to perform a handful of operatoins long term. [Let's sidebar the discussion on if it's the right tool, it's worked well for many years until we updated to 2.2]

Part of that quartz.net has the following tables (related) for schema: https://gist.github.com/jcolebrand/8695603

So the process is we're inserting records in this table, to the tune of say 80k records. (I have three replications of quartz.net serving three different configurations, one has 80k records, one 50k, one 280k, so it varies). We insert them from some other table that is the table of record, so rebuilding this table isn't a terrible loss. The issue is described below.

I have a tool which can bulk schedule these tasks (most of which are one-time and will happen anywhere from two weeks to twenty years in the future, again, this is about the db and not the architecture choices) and I can debug step through, see the rows get inserted into the table with no concern. I can monitor the table and see that they get inserted. I then come back 30 minutes later (well, this part varies. It's not deterministic on when they disappear) and check and the records are now missing.

Out of 80k on the one instance, I'm missing approximately 2700 records. And when I run my tool, I see them all synced up, and after some matter of time, they have evaporated again.

Here's things I've tried:

  • checking the all transactions report in SSMS
  • checking the all blocking transactions report in SSMS
  • leaving my scheduling application open for long duration (in case, by some freak magic, there was an open, uncommitted transaction)
  • restarting the Quartz.net windows service application maintaining the database
  • inserting records with the quartz.net windows service disabled

Things I haven't tried:

  • restarting SQL Server
  • my queries don't use "with(nolock)" [I've heard that's bad juju for day-to-day]

Things I think I should do and don't know how:

  • run SQL Profiler against the instance and monitor for "delete" statements
  • magic to determine if there is an uncommitted transaction
  • sacrifice unicorns
  • telephone a friend
  • daily double

@@version:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

EDIT 2014-01-30

It is imperative for internal reasons that I can demonstrate precisely why the records are disappearing, including but not limited to:

  • deletion by a user
  • uncommitted transaction
  • deletion by Quartz itself
  • some other unexplained phenomena
  • butterfly effect
  • trigger effect
  • unicorns

Where possible, I need the specific statement involved in the delete.

TL;DR

So, basically, the TL;DR is: records are inserted. For some duration of time up to at least 30 minutes record definitively show in queries against the table. After some duration, records no longer show in queries against the table.

What gives? What am I overlooking? How would you profile this IN PRODUCTION to see what's happening here?

  • Please remember: I don't want to profile any more than I absolutely have to since this is production. – jcolebrand Jan 29 '14 at 20:12
  • 2
    Is your primary key really a combined 920 byte string (200x2 + 200x2 + 120x1)? Ouch. You should tell the 3rd party vendor that if they really have intentions of using the fully defined size of those columns, an insert could fail (by exceeding 900 bytes). – Aaron Bertrand Jan 29 '14 at 20:18
  • 3rd party products are so much fun. We're actively writing a new process, but that's still 2 months from approval for production. This impacts things TODAY. C'est la vie. – jcolebrand Jan 29 '14 at 20:23
  • 1
    I wonder if the fn_dblog() function can show you anything! Just curious... Very interesting problem! – DenisT Jan 29 '14 at 20:41
  • 3
3

I would debug this using a server-side trace. Essentially, the reason Profiler is problematic in production is because it is a client application. If you attempt to trace to much and SQL Server is trying to manage that communication between the server and the client, it can bog things down. So instead, you can script out the trace as a T-SQL script and run it on the server itself. It will capture the information to a trace file, then you can take that trace file elsewhere and review it.

To script the trace, open Profiler and select your events. Since you are concerned that statements may not be committed/completed, I would recommend

  • RPC: Starting/Completed
  • SP: Starting/Completed
  • SQL: BatchStarting/BatchCompleted

Included relevant columns. Filter as necessary (such as database name, maybe duration)

Then click 'Run', but immediately stop. At this point, go to your toolbar and select: File->Export->Script Trace Definition->For SQL Server... This will ask you to save a script somewhere. Once you've saved it, open it.

Before you run it, specify a file name and path (replace InsertFileNameHere). Then execute to start the trace. The trace will continue to run until you stop it. To stop it, make sure you know the trace ID, which is returned when you run the script. If you can't remember it, look at sys.traces. Once you have the trace ID, you can stop it by using sp_settracestatus to set the status to 0 to stop it. Once it is set to 0, you can set it to 2 to delete it.

After stopping it, you should have a trace file to review and identify what's going on with your processes. The trace file is viewable through the Profiler application.

  • Is it possible to record those traces right back into SQL Server itself into a table instead of a file? – jcolebrand Jan 29 '14 at 20:44
  • Yes, it is. When creating the trace (exactly at the beginning) you have to choose where to save the data: into a file or a table or both. – Marian Jan 29 '14 at 20:48
  • 1
    @Marian - That only applies to Profiler not server side tracing. When Profiler does it the data gets sent to the client then back again to the server to insert! – Martin Smith Jan 29 '14 at 21:17
  • Agreed with @Martin - this is mundo expensive. – Aaron Bertrand Jan 29 '14 at 21:24
  • Marking this as accepted only because it showed me specifically how the deletes were being accomplished, but Aaron's answer was implemented first and definitively helped. – jcolebrand Jan 30 '14 at 17:20
3

If you don't expect any rows to be deleted from this table, then the simplest, least impact way to catch anyone running deletes would be a delete trigger. You can use an INSTEAD OF trigger if you want to log and prevent, or an after trigger if you just want to log. First, a logging table:

CREATE TABLE dbo.CatchTheDoNoGooders
(
  [JOB_NAME] [nvarchar](200),
  [JOB_GROUP] [nvarchar](200),
  [SCHED_NAME] [varchar](120),
  UserName SYSNAME,
  HostName SYSNAME,
  Program NVARCHAR(4000),
  IP VARCHAR(15),
  EventDate DATETIME
);

Now, an INSTEAD OF INSERT trigger, could log the DELETEs but not carry through:

CREATE TRIGGER dbo.InsteadDelete_QRTZ_JOB_DETAILS
  ON dbo.QRTZ_JOB_DETAILS
  INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ip VARCHAR(15), @hn SYSNAME, @prog NVARCHAR(4000);

  SELECT TOP (1) @ip = client_net_address 
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID;

  SELECT TOP (1) @hn = [host_name], @prog = LEFT([program_name],4000)
    FROM sys.dm_exec_sessions
    WHERE session_id = @@SPID;

  INSERT dbo.CatchTheDoNoGooders
  SELECT JOB_NAME, JOB_GROUP, SCHED_NAME,
    SUSER_SNAME(), @hn, @prog, @ip, CURRENT_TIMESTAMP
    FROM deleted;
END
GO

Change INSTEAD OF to FOR and now you have an AFTER trigger that will still allow the delete to happen, but will log them all anyway. This can be better if you do have some legal deletes going on but want to determine who is deleting the wrong things.

The beauty is that in both cases this only has any impact whatsoever if and when a delete is actually issued against the table.

  • So the problem is not that I think they're getting deleted, fair point and I forgot to mention that outright, but I think that the problem is they aren't getting committed and I can't figure out why they wouldn't be. So confused. – jcolebrand Jan 29 '14 at 20:23
  • @jcolebrand if you're not using NOLOCK then it couldn't be that you read uncommitted rows. Are you using any variation of SNAPSHOT? – Aaron Bertrand Jan 29 '14 at 20:28
  • Not that I know of, but it's entirely possible someone threw something in on the configuration and didn't tell me. But not in my queries. My queries are as blocking as you can get (that I know of) – jcolebrand Jan 29 '14 at 20:29
  • Also: TIL so freaking much about SS I hadn't planned on learning today :D :D I love answers like this – jcolebrand Jan 29 '14 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.